Subversion Repositories phpLibraryV2

Rev

Rev 10 | Rev 15 | Go to most recent revision | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 10 Rev 13
Line 1... Line 1...
1 
<?php
 1 
<?php
2 
 
 2 
 
- 
 
 3 
   /*
- 
 
 4 
    * Class DBQuery
- 
 
 5 
    *
- 
 
 6 
    * PHP Class as a wrapper around the PDO class. Allows you to make
- 
 
 7 
    * the connection and run queries and/or DDL's if needed with the
- 
 
 8 
    * most common parameters (ie, not as flexible as PDO itself).
3 
   //require_once "DBDatabase.class.php";
 9 
    * Enhancements include:
- 
 
 10 
    *
- 
 
 11 
    * Logs all queries with an optional username except select statements
- 
 
 12 
    * ie, anything that will change the system ( insert, delete, update,
- 
 
 13 
    * create, drop, alter, etc... ). Statement are logged with a date/time
- 
 
 14 
    * stamp, the user who performed the function, and the query executed
4 
   global $DEBUG;
 15 
    *
- 
 
 16 
    * Errors are simply trapped and the message is stored in the public
- 
 
 17 
    * member $error. Functions which find an error return boolean false
5
18 
    *
6 
   define( 'HTML_QUOTE_CHAR', '"' );
 19 
    * Public Functions are:
- 
 
 20 
    *    __construct   -- creates connection
7 
   define( 'CONSTANT_NO_VALUE_DROPDOWN', '--------' );
 21 
    *    doSQL         -- executes an SQL query or DDL
- 
 
 22 
    *    errors        -- returns all errors not yet cleared
- 
 
 23 
    *                     can also directly access via public member errors
- 
 
 24 
    *
- 
 
 25 
    *    public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {
- 
 
 26 
 
- 
 
 27 
 
- 
 
 28 
    * Author: R. W. Rodolico (rodo@dailydata.net)
- 
 
 29 
    * Date: 2018-04-30
- 
 
 30 
    *
- 
 
 31 
    */
8 
 
 32 
 
9 
   class DBQuery {
 33 
   class DBQuery extends mysqli {
10 
 
 34 
 
11 
      // following are used for the class with no instantiation
 35 
      /* everything is in the one $parameters array, which can then
12 
      protected static $connected = false;
 36 
       * be set/saved/loaded as needed
13 
      protected static $connectionInfo = null; // connection information for database
 - 
 
14
37 
       */
15 
      protected $parameters = array(
 38 
      protected $parameters = array(
16 
                               // query to be run
 39 
             // query(s) to be run
17 
                               'query' => '',
 40 
             'query' => '',
- 
 
 41 
             // additional clause to be added to where on a statement
- 
 
 42 
             'whereClause' => '',
- 
 
 43 
             // additional order by clause to be added
- 
 
 44 
             'orderBy' => '',
18 
                               // the number of rows 1) affectd by an insert, update or delete or 2) returned by a select
 45 
             // the number of rows 1) affectd by an insert, update or delete or 2) returned by a select
19 
                               'rowsAffected' => '',
 46 
             'rowsAffected' => '',
20 
                               // the value of the last row inserted (mysql only)
 47 
             // the value of the last row inserted
21 
                               'lastInsertKey' => '',
 48 
             'lastInsertKey' => '',
22 
                               // an array that contains the data retrieved by a query (select only)
 49 
             // an array that contains the data retrieved by a query (select only)
23 
                               'returnData' => '',
 50 
             'returnData' => '',
24 
                               // an array that contains the meta data from the query
 51 
             // an array that contains the meta data from the query for each column
25 
                               'columnMetaData' => '',
 52 
             'columnMetaData' => '',
26 
                               // if true, $returnData is an array of associative array, with the column names being the key fields
 53 
             // number of columns (fields) returned by query (select only)
27 
                               'useAssociativeArray' => '',
 54 
             'numfields' => 0,
28 
                               // if true, will return errors to STDOUT
 55 
             // type of data returned, array of array, array of hash, or both
29 
                               'showErrors' => '',
 56 
             'fetchType' => MYSQLI_ASSOC,
30 
                               // set to last error returned
 57 
             // human readable form of fetchType
31 
                               'error' => '',
 58 
             'returntype' => 'associative',
32 
                               // Will be instantiation of a  Logging instance if set
 59 
             // array of any errors which occurred
33 
                               'log' => '',
 60 
             'error' => array(),
34 
                               // if set to a table name, all queries are written to it
 61 
             // if set to a table name, all modifying queries are written to it
35 
                               'auditTable' => '',
 62 
             // must contain, at a minimum, columns whenrun timestamp, whoran varchar and query text
36 
                               'orderBy' => '',
 63 
             // can be created with $this->buildAuditTable
37 
                             );
 64 
             'auditTable' => '_activity_log'
38
65 
           );
39 
 
 66 
 
40 
      public static function connect( $connectionInfo = null ) {
 - 
 
41 
         if ( isset ( $connectionInfo ) ) {
 - 
 
42 
            DBQuery::$connectionInfo = $connectionInfo;
 - 
 
43 
            if ( ! isset( DBQuery::$connectionInfo['host'] ) || DBQuery::$connectionInfo['host'] == '' )
 - 
 
44 
               DBQuery::$connectionInfo['host'] = '127.0.0.1';
 - 
 
45 
         } // if they passed in a connection string
 - 
 
46 
         if ( ! DBQuery::$connected ) {
 - 
 
47 
            try {
 - 
 
48 
               if ( mysql_connect( DBQuery::$connectionInfo['host'], DBQuery::$connectionInfo['username'], DBQuery::$connectionInfo['password'] ) === false )
 - 
 
49 
                  throw new Exception( mysql_error() );
 - 
 
50 
               if ( mysql_select_db( DBQuery::$connectionInfo['name'] ) === false )
 - 
 
51 
                  throw new Exception(mysql_error());
 - 
 
52 
               DBQuery::$connected = true;
 - 
 
53 
            } catch ( Exception $e ) {
 - 
 
54 
               die( $e->getMessage() );
 - 
 
55 
            }
 67
56 
         } // if we are not connected
 - 
 
57 
      } // static function connect
 - 
 
58 
 
 68 
 
- 
 
 69 
      /*
59 
      protected function logIt ( $level, $message, $file = null, $class = null, $function = null, $line = null ) {
 70 
       * static function which simply parses out an error and returns
- 
 
 71 
       * a string suitable for printing. NOTE: it includes line returns
- 
 
 72 
      */
- 
 
 73 
      public static function error2String( $error ) {
60 
         global $DEBUG;
 74 
         $return = array();
61 
         if ( isset( $DEBUG ) ) {
 75 
         for ( $i = 0; $i < count( $error ); $i++ ) {
62 
            $DEBUG->writeLog( $level, $message, $file, $class, $function, $line );
 76 
            $return[] = implode( "\n", array( 'Error ' . $error[$i]['id'] . ' - ' . $error[$i]['message'], 'while executing query', $error[$i]['query'] ) );
63 
         }
 77 
         }
- 
 
 78 
         return implode( "\n\n", $return ) . "\n";
64 
      } // logIt
 79 
      } // error2String
65
80
66 
      public function __construct( $query, $whereClause = null, $orderBy = null, $runImmediate = false ) {
 - 
 
67 
         $this->parameters['query'] = $query;
 - 
 
68 
         if ( isset( $whereClause ) ) {
 - 
 
69 
            $this->parameters[ 'query' ] .= $this->makeWhereClause( $whereClause );
 - 
 
70 
         } // whereclause
 - 
 
71 
         if ( isset( $orderBy ) ) {
 - 
 
72 
            $this->parameters[ 'orderBy' ] .= ' order by ' . implode( ',', $orderBy );
 - 
 
73 
         } // orderby
 - 
 
74 
         $this->parameters[ 'rowsAffected' ] = 0;
 - 
 
75 
         $this->parameters[ 'lastInsertKey' ] = '';
 - 
 
76 
         $this->parameters[ 'returnData' ] = array();
 - 
 
77 
         $this->parameters[ 'columnMetaData' ] = array();
 - 
 
78 
         $this->parameters[ 'useAssociativeArray' ] = true;
 81 
      // simply returns parameters for saving and reloading later
79 
         $this->parameters[ 'showErrors' ] = true;
 - 
 
80 
         $this->parameters[ 'error' ] = '';
 - 
 
81 
         if ( $runImmediate )
 - 
 
82 
            $this->run();
 - 
 
83 
         // if (isset( $AUDITTABLE ) ) $this->parameters[ 'auditTable' ) =  new Logging( $LOGFILE );
 - 
 
84 
      } // __construct
 - 
 
85
- 
 
86 
      public function save() {
 82 
      public function save() {
87 
         return $this->parameters;
 83 
         return $this->parameters;
88 
      } // function save
 84 
      } // function save
89
85 
 
- 
 
 86 
      // loads parameters from $this->save(), or hand built
90 
      public function load( $parameters = array() ) {
 87 
      public function load( $parameters = array() ) {
91 
         $this->parameters = $parameters;
 88 
         $this->parameters = $parameters;
92 
      } // function load
 89 
      } // function load
93 
 
 90 
 
- 
 
 91 
      // sets a key/value pair in $this->parameters
- 
 
 92 
      // returns $value
94 
      public function __set( $name, $value ) {
 93 
      public function __set( $name, $value ) {
95 
         $this->parameters[$name] = $value;
 94 
         $this->parameters[$name] = $value;
96 
         return $value;
 95 
         return $value;
97 
      }
 96 
      }
98
97 
 
- 
 
 98 
      // gets the current value of $this->parameters[$name]
99 
      public function __get( $name ) {
 99 
      public function __get( $name ) {
100 
         return isset( $this->parameters[$name] ) ? $this->parameters[$name] : null;
 100 
         return isset( $this->parameters[$name] ) ? $this->parameters[$name] : null;
101 
      }
 101 
      }
102
102 
 
- 
 
 103 
      // returns true if $parameters[$name] is set
103 
      public function __isset( $name ) {
 104 
      public function __isset( $name ) {
104 
         return isset( $this->parameters[$name] );
 105 
         return isset( $this->parameters[$name] );
105 
      }
 106 
      }
- 
 
 107 
 
- 
 
 108 
 
106
109 
      /*
- 
 
 110 
       * function: buildAuditTable
- 
 
 111 
       * parameters:
- 
 
 112 
       *       $tablename - name of table to be built
- 
 
 113 
       *       $createStatement - SQL DDL to build the table
- 
 
 114 
       *
- 
 
 115 
       * If $tablename is set, will use that, otherwise will use
- 
 
 116 
       * $parameters[auditTable]. In either case, $parameters[auditTable]
- 
 
 117 
       * is set to the value of the table used
107 
      /*
118 
       *
- 
 
 119 
       * if $createStatement is set, will be run WITHOUT MODIFICATION, and
- 
 
 120 
       * $parameters[auditTable] is not set to anything (unless $tablename
- 
 
 121 
       * is set)
- 
 
 122 
       *
- 
 
 123 
       * If $createStatement is not set, will use a default to build
- 
 
 124 
       * a table from $parameters[auditTable].
- 
 
 125 
       *
- 
 
 126 
       * Can definitely blow up if the table name is not set both places
- 
 
 127 
       * or if $createStatement is fubar
- 
 
 128 
       */
- 
 
 129 
 
- 
 
 130 
      public function buildAuditTable( $tablename = '', $createStatement = '' ) {
- 
 
 131 
         if ( $tablename ) // they sent us one, so set it
- 
 
 132 
            $this->parameters[ 'auditTable' ] = $tablename;
108 
         function actually executes the query, populating the members with the results
 133 
         if ( ! $createStatement ) { // they did not set createStatement, so use our default
- 
 
 134 
            $createStatement = "
- 
 
 135 
               create table $this->parameters['auditTable'] (
- 
 
 136 
                  _activity_log_id int unsigned not null auto_increment,
- 
 
 137 
                  timestamp  timestamp,
- 
 
 138 
                  user       varchar(64),
- 
 
 139 
                  query      text,
- 
 
 140 
                  primary key(_activity_log_id)
- 
 
 141 
               ) comment 'tracks queries which modify data'";
- 
 
 142 
         } // if
- 
 
 143 
         if ( parent::query( $createStatement ) === false ) {
- 
 
 144 
            // on error, die
- 
 
 145 
            print "Can not create audit table with query<br />\n$createStatement<br />\n";
109 
         returns true if no errors, false if errors (see $this->parameters[ 'error' ) for code)
 146 
            die ( printf("Errormessage: %d - %s\n", $this->errno, $this->error ) );
- 
 
 147 
         } // if error, die
- 
 
 148 
      } // buildAuditTable
- 
 
 149
- 
 
 150 
      /*
- 
 
 151 
       * log queries to a table, file, or nothing
- 
 
 152 
       * log contains date/time, username and query
- 
 
 153 
       * to turn off logginging:
- 
 
 154 
       * unset( $parameters['auditTable'] )
110 
      */
 155 
       */
- 
 
 156 
      private function logIt( $username, $query, $recursion = false ) {
- 
 
 157 
         if ( ! isset( $this->parameters['auditTable'] ) )
111 
      function run () {
 158 
            return;
- 
 
 159 
         $username = $this->real_escape_string( $username );
- 
 
 160 
         $query = $this->real_escape_string( $query );
- 
 
 161 
         $logEntry = "insert into $this->parameters['auditTable'] (user, query) values ( '$username', '$query')";
- 
 
 162 
         //print "Loggging\n$logEntry\n";
- 
 
 163 
         if ( parent::query( $logEntry ) !== false ) { // good
- 
 
 164 
            return;
112 
         if ( ! DBQuery::$connected ) {
 165 
         } else { // we had an error
- 
 
 166 
            if ( ! $recursion && $this->errno == 1146 ) { // table doesn't exist, so let's create it
- 
 
 167 
               $result = parent::query( "show tables like '$this->parameters['auditTable']'" );
113 
            if ( isset( DBQuery::$connectionInfo ) ) {
 168 
               if ( $result->num_rows == 0 ) {
114 
               DBQuery::connect();
 169 
                  $this->buildAuditTable( );
- 
 
 170 
                  return $this->logIt( $username, $query, true );
- 
 
 171 
               }
115 
            } else {
 172 
            } else {
116 
               throw new Exception( "No Connection Available" );
 173 
               print "Trying to log transaction with query<br />\n$logEntry<br />\n";
117 
            }
 - 
 
118 
         }
 - 
 
119 
         // print "<pre>In Query database\n---------------------\n$query\n---------------------\n</pre>";
 - 
 
120 
         $this->logIt( 2, $this->parameters['query'] );
 - 
 
121 
//         if ( isset( $this->parameters[ 'auditTable' )) ) audit( $query );
 - 
 
122 
         $result = @mysql_query($this->parameters[ 'query' ]);
 - 
 
123 
         if( mysql_errno() ) {
 - 
 
124 
            $this->parameters[ 'error' ] = 'MYSQL ERROR #'.mysql_errno().' : <small>' . mysql_error(). "</small><br><VAR>$this->parameters[query]</VAR>";
 - 
 
125 
            if ( $this->parameters[ 'showErrors' ] ) echo($this->parameters[ 'error' ]);
 174 
               die ( printf("Errormessage: %d - %s\n", $this->errno, $this->error ) );
126 
            return false;
 175 
            } // if..else
127 
         } // if
 176 
         } // if
- 
 
 177 
      } // function logIt
- 
 
 178 
 
- 
 
 179 
 
- 
 
 180 
      /*
- 
 
 181 
       * doSQL
- 
 
 182 
       * Parameters: $query - string or array of strings to be executed
- 
 
 183 
       *             $parameters - hash used to pass additional parameters, to include
- 
 
 184 
       *                           $parameters['username'] = 'fred'; // username for logging
- 
 
 185 
       *                           $parameters['returntype'] = 'hash'; or array or both
- 
 
 186 
       *
- 
 
 187 
       * executes one or more queries
- 
 
 188 
       *
- 
 
 189 
       * If the query is one of select, show, describe or explain, it must
- 
 
 190 
       * be a single string. It will return the data results in a hash
- 
 
 191 
       * containing
- 
 
 192 
       *    'data'  - an array of array/hash/both depending on what you asked for
- 
 
 193 
       *    'count' - number of results (ie, count(data)
- 
 
 194 
       *    'meta'  - metadata for each column returned
- 
 
 195 
       *    'numfields' - number of columns in result (ie, count(meta))
- 
 
 196 
       *    'errors' - normally empty array of errors
- 
 
 197 
       *
- 
 
 198 
       * if the query modifies data (ie, NOT above), query may be an array
- 
 
 199 
       * which will be surrounded by a transaction and rolled back if
- 
 
 200 
       * anything causes an error.
- 
 
 201 
       *
- 
 
 202 
       * These will return a hash containing
- 
 
 203 
       *    'count' l- number of rows affected by last statement
- 
 
 204 
       *    'last_insert_id' - last insert id created by BLOCK of queries
- 
 
 205 
       *    'errors' - normally empty array of errors which occurred (caused a rollback)
- 
 
 206 
       *
- 
 
 207 
       */
- 
 
 208 
      public function doSQL( $query, $parameters = array() ) {
- 
 
 209 
         $errors = array();
- 
 
 210 
         // if it is a "selectstatement" it doesn't modify data
- 
 
 211 
         // if query is an array, assume it modifies something
- 
 
 212 
         // if it is a single statement, look for the regex
- 
 
 213 
         $selectStatement = is_array( $query ) ?
- 
 
 214 
            false :
128 
         if( preg_match ( '/^\s*select/i', $this->parameters[ 'query' ] ) ) { // this is a select statement
 215 
            ( preg_match( '/^\s*(select)|(show)|(describe)|(explain)/i', $query ) === 1 );
- 
 
 216
- 
 
 217 
         // different actions based on whether it modifies data or not
- 
 
 218 
         if ( $selectStatement ) { // if a select, simply return the rows
- 
 
 219 
            // dataset is small enough, we just read it into memory all at one time.
- 
 
 220 
            // NOTE: fetch_all is nice, but tied to mysqlnd, which has reports of problems, so we do it the old way
- 
 
 221 
            if ( $sth = parent::query( $query, MYSQLI_USE_RESULT  ) ) {
- 
 
 222 
               if ( $sth === false ) {
- 
 
 223 
                  $errors[] = array( 'id' => $this->errno, 'message' => $this->error );
- 
 
 224 
               } else {
129 
            $this->parameters[ 'rowsAffected' ] = @mysql_num_rows($result);
 225 
                  $this->parameters['columnMetaData'] = $sth->fetch_fields(); // get metadata
- 
 
 226 
                  $this->parameters['returnData'] = array(); // we'll put all the results in an array
- 
 
 227 
                  // $fetchtype returns either an array of array, array of hash, or both. Default is array of hash
- 
 
 228 
                  if ( isset( $this->parameters['returntype'] ) )
- 
 
 229 
                     $this->parameters[ 'fetchType' ] = $this->parameters['returntype'] == 'array' ? MYSQLI_NUM : (
- 
 
 230 
                           ( $parameters['both'] == 'both' ) ? MYSQLI_BOTH : MYSQLI_ASSOC
- 
 
 231 
                           );
- 
 
 232 
                  } else { // default is associative array (hash)
130 
            $this->parameters[ 'columnMetaData' ] = array();
 233 
                     $this->parameters[ 'fetchType' ] =  MYSQLI_ASSOC;
131 
            for ($i = 0; $i < mysql_num_fields( $result ); $i++) {
 234 
                     $this->parameters['returntype'] = 'associative';
- 
 
 235 
                  }
132 
               $meta = mysql_fetch_field ( $result );
 236 
                  // slurp all the stuff in
133 
               $this->parameters[ 'columnMetaData' ][] = array('name' => $meta->name, 'length' => $meta->max_length, 'numeric' => $meta->numeric, 'type' => $meta->type );
 237 
                  while ( $values =  $sth->fetch_array( $this->parameters[ 'fetchType' ] ) ) {
- 
 
 238 
                     $this->parameters[ 'returnData' ][] = $values;
- 
 
 239 
                  }
- 
 
 240 
                  $sth->free(); // cleanup memory, don't need two copies
- 
 
 241 
               } // if we had no errors
134 
            }
 242 
            }
- 
 
 243 
            $this->parameters[ 'rowsAffected' ] = count( $this->parameters[ 'returnData' ] );
- 
 
 244 
            $this->parameters[ 'numfields' ] = count( $this->parameters['columnMetaData'] );
135 
            if( ! $this->parameters[ 'rowsAffected' ] ) return;
 245 
            $this->parameters[ 'lastInsertKey' ] = 0;
136 
            $this->parameters[ 'returnData' ] = array();
 246 
            $this->parameters[ 'error' ] = $errors;
- 
 
 247 
         } else {
- 
 
 248 
            if ( ! is_array( $this->parameters['query'] ) ) { // not an array, so make it one
137 
            for( $i = 0; $i < $this->parameters[ 'rowsAffected' ]; $i++ ) {
 249 
               $temp = $this->parameters['query'];
138 
               $this->parameters[ 'returnData' ][] = $this->parameters[ 'useAssociativeArray' ] ? mysql_fetch_assoc($result) : mysql_fetch_row($result);
 250 
               $this->parameters['query'] = array( $temp );
139 
            }
 251 
            }
- 
 
 252 
            // do it in a transaction so we can back out on failure
140 
            mysql_free_result($result);
 253 
            $this->autocommit(false);
- 
 
 254 
            $allOk = true;
- 
 
 255 
            for ( $i = 0; $i < count( $this->parameters['query'] ); $i++ ) {
- 
 
 256 
               $this->logIt( isset( $parameters['username'] ) ? $parameters['username'] : 'unknown', $this->parameters['query'][$i] );
- 
 
 257 
               if ( parent::query( $this->parameters['query'][$i] ) === false ) { // we had an erorr
- 
 
 258 
                  // record it
- 
 
 259 
                  $errors[] = array( 'id' => $this->errno, 'message' => $this->error, 'query' => $this->parameters['query'][$i] );
- 
 
 260 
                  $allOk = false;
- 
 
 261 
                  // and bail
- 
 
 262 
                  break;
- 
 
 263 
               }
- 
 
 264 
            }
- 
 
 265 
            // if we made it through ok, commit, otherwise rollback
- 
 
 266 
            $allOk ? $this->commit() : $this->rollback();
141 
         } else { // not a select statement
 267 
            // reset autocommit to true
- 
 
 268 
            $this->autocommit(true);
- 
 
 269 
            $this->parameters['query'] = $query;
142 
            $this->parameters[ 'rowsAffected' ] = mysql_affected_rows();
 270 
            $this->parameters[ 'rowsAffected' ] = $this->affected_rows;
143 
            $this->parameters[ 'lastInsertKey' ] = mysql_insert_id();
 271 
            $this->parameters[ 'lastInsertKey' ] = $this->insert_id;
- 
 
 272 
            $this->parameters[ 'error' ] = $errors;
144 
         }
 273 
         } // if select .. else
145 
         return true;
 274 
         return $this->parameters;
146 
      } // function run
 275 
      }  // function doSQL
147
- 
 
148 
 
 - 
 
149 
 
 276 
 
150 
      /*
 277 
      /*
151 
       * function will return one and only one row, NOT as an array of array
 278 
       * function will return one and only one row, NOT as an array of array
152 
       * but as a single row array
 279 
       * but as a single row array
153 
       * if more than one row is returned by query, error is set and function
 280 
       * if more than one row is returned by query, error is set and function
Line 179... Line 306...
179 
         $useAssociativeArray = false;
 306 
         $useAssociativeArray = false;
180 
         $this->run();
 307 
         $this->run();
181 
         $useAssociativeArray = $save;
 308 
         $useAssociativeArray = $save;
182 
         return $this->parameters[ 'rowsAffected' ] ? $this->parameters[ 'returnData' ][0][0] : null;
 309 
         return $this->parameters[ 'rowsAffected' ] ? $this->parameters[ 'returnData' ][0][0] : null;
183 
      }
 310 
      }
184
- 
 
185 
      public function countNumberOfRows ( $sql = null ) {
 - 
 
186 
         if ( isset( $sql ) )
 - 
 
187 
            $this->parameters[ 'query' ] = $sql;
 - 
 
188 
         $save = $this->parameters[ 'useAssociativeArray' ];
 - 
 
189 
         $this->parameters['useAssociativeArray'] = false;
 - 
 
190 
         $this->run();
 - 
 
191 
         $this->parameters['useAssociativeArray'] = $save;
 - 
 
192 
         return $this->parameters[ 'rowsAffected' ];
 - 
 
193 
      }
 - 
 
194
- 
 
195 
      public function makeWhereClause ($conditions, $joinedBy = 'and') {
 - 
 
196 
         $joinedBy = " $joinedBy "; // make sure there are spaces around it
 - 
 
197 
         $whereClause = ' where ' . implode ( $joinedBy, $conditions );
 - 
 
198 
         return $whereClause;
 - 
 
199 
      }
 - 
 
200
- 
 
201 
      /*
- 
 
202 
         values is an associative array of name/value pairs
 - 
 
203 
         function will replace all items of the form <$name> with its values (the less than and greater than symbols around the key)
 - 
 
204 
      */
 - 
 
205 
      protected function insertValuesIntoQuery( $values ) {
 - 
 
206 
         foreach ( $values as $name => $value ) {
 - 
 
207 
            $this->parameters[ 'query' ] = search_replace_string($this->parameters[ 'query' ], "<$name>", $value );
 - 
 
208 
         }
 - 
 
209 
      } // insertValuesIntoQuery
 - 
 
210
- 
 
211 
 
 311 
 
212 
      /*
 312 
      /*
213 
       * function will attempt to make a constant ($value) safe for SQL depending on the type.
 313 
       * function will attempt to make a constant ($value) safe for SQL depending on the type.
214 
       *
314 
       *
215 
       * if $value is empty, $default is returned, as will happen if any of the
 315 
       * if $value is empty, $default is returned, as will happen if any of the
Line 233... Line 333...
233 
         switch ( strtolower( $type ) ) {
 333 
         switch ( strtolower( $type ) ) {
234 
            case 'string' :
 334 
            case 'string' :
235 
            case 's' :
335 
            case 's' :
236 
                     if ( get_magic_quotes_gpc() )
336 
                     if ( get_magic_quotes_gpc() )
237 
                        $value = stripslashes($value);
 337 
                        $value = stripslashes($value);
238 
                     $value = mysql_real_escape_string( $value );
 338 
                     $value = $this->real_escape_string( $value );
239 
                     $value = strlen( $value ) > 0 ? "'$value'" : $default;
 339 
                     $value = strlen( $value ) > 0 ? "'$value'" : $default;
240 
                     break;
 340 
                     break;
241 
            case 'date' :
 341 
            case 'date' :
242 
            case 'd' :
 342 
            case 'd' :
243 
                     if ( $value != 'null' ) {
 343 
                     if ( $value != 'null' ) {
Line 274... Line 374...
274 
      }
 374 
      }
275 
 
 375 
 
276 
 
 376 
 
277 
   } // class DBQuery
 377 
   } // class DBQuery
278 
 
 378 
 
- 
 
 379 
   $db = new DBQuery( '127.0.0.1', 'camp', 'camp', 'camp' );
- 
 
 380 
 
- 
 
 381 
   if ($db->connect_error) {
- 
 
 382 
       die('Connect Error (' . $db->connect_errno . ') '  . $db->connect_error);
- 
 
 383 
   }
- 
 
 384 
   $result = $db->doSQL(
- 
 
 385 
               array(
- 
 
 386 
                     'drop table if exists temp',
- 
 
 387 
                     'create table temp ( col1 int unsigned )',
- 
 
 388 
                     "insert into temp values ('mike')"
- 
 
 389 
                     )
- 
 
 390 
         );
- 
 
 391 
   if ( $result['errors'] ) {
- 
 
 392 
      print_r ( $result );
- 
 
 393 
      die ( DBQuery::error2String( $result['errors'] ) );
- 
 
 394 
   } else {
- 
 
 395 
      print "running select\n";
- 
 
 396 
      $result = $db->doSQL( 'select * from temp' );
- 
 
 397 
      print_r( $result );
- 
 
 398 
   }
- 
 
 399 
//   $return = $db->doSQL( "select device.device_id 'id',device.name 'name', device_type.name 'type' from device join device_type using (device_type_id) where device.removed_date is null and device_type.show_as_system = 'Y'" );
- 
 
 400 
//   print_r( $return );
- 
 
 401 
//   print_r( $db );
279 
?>
 402 
?>
- 
 
 403
- 
 
 404
- 
 
 405
- 
 
 406
- 
 
 407
- 
 
 408
- 
 
 409
- 
 
 410
- 
 
 411
- 
 
 412