Subversion Repositories phpLibraryV2

Rev

Rev 46 | Details | Compare with Previous | Last modification | View Log | RSS feed

Rev Author Line No. Line
1 rodolico 1
<?php
2
 
13 rodolico 3
   /*
4
    * Class DBQuery
5
    * 
16 rodolico 6
    * PHP Class as a wrapper around the mysqli class. Allows you to make
13 rodolico 7
    * the connection and run queries and/or DDL's if needed with the
16 rodolico 8
    * most common parameters (ie, not as flexible as mysqli itself).
13 rodolico 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
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
18
    *
19
    * Public Functions are:
20
    *    __construct   -- creates connection
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' ) {
1 rodolico 26
 
4 rodolico 27
 
13 rodolico 28
    * Author: R. W. Rodolico (rodo@dailydata.net)
29
    * Date: 2018-04-30
30
    * 
31
    */
32
 
33
   class DBQuery extends mysqli {
34
 
35
      /* everything is in the one $parameters array, which can then
36
       * be set/saved/loaded as needed
37
       */
6 rodolico 38
      protected $parameters = array(
13 rodolico 39
             // query(s) to be run
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' => '',
45
             // the number of rows 1) affectd by an insert, update or delete or 2) returned by a select
46
             'rowsAffected' => '',
47
             // the value of the last row inserted
48
             'lastInsertKey' => '',
49
             // an array that contains the data retrieved by a query (select only)
29 rodolico 50
             'returnData' => array(),
13 rodolico 51
             // an array that contains the meta data from the query for each column
29 rodolico 52
             'columnMetaData' => array(),
13 rodolico 53
             // number of columns (fields) returned by query (select only)
54
             'numfields' => 0,
55
             // human readable form of fetchType
36 rodolico 56
             'returnType' => 'both',
13 rodolico 57
             // array of any errors which occurred
58
             'error' => array(),
59
             // if set to a table name, all modifying queries are written to it
60
             // must contain, at a minimum, columns whenrun timestamp, whoran varchar and query text
61
             // can be created with $this->buildAuditTable
62
             'auditTable' => '_activity_log'
63
           );
64
 
37 rodolico 65
      public function __construct( $server, $username, $password, $database ) {
66
         parent::__construct(  $server, $username, $password, $database );
67
      }
4 rodolico 68
 
13 rodolico 69
      /*
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 ) {
74
         $return = array();
75
         for ( $i = 0; $i < count( $error ); $i++ ) {
76
            $return[] = implode( "\n", array( 'Error ' . $error[$i]['id'] . ' - ' . $error[$i]['message'], 'while executing query', $error[$i]['query'] ) );
6 rodolico 77
         }
13 rodolico 78
         return implode( "\n\n", $return ) . "\n";
79
      } // error2String
6 rodolico 80
 
13 rodolico 81
      // simply returns parameters for saving and reloading later
6 rodolico 82
      public function save() {
83
         return $this->parameters;
84
      } // function save
13 rodolico 85
 
86
      // loads parameters from $this->save(), or hand built
6 rodolico 87
      public function load( $parameters = array() ) {
88
         $this->parameters = $parameters;
89
      } // function load
90
 
13 rodolico 91
      // sets a key/value pair in $this->parameters
92
      // returns $value
1 rodolico 93
      public function __set( $name, $value ) {
6 rodolico 94
         $this->parameters[$name] = $value;
4 rodolico 95
         return $value;
1 rodolico 96
      }
13 rodolico 97
 
98
      // gets the current value of $this->parameters[$name]
1 rodolico 99
      public function __get( $name ) {
6 rodolico 100
         return isset( $this->parameters[$name] ) ? $this->parameters[$name] : null;
1 rodolico 101
      }
13 rodolico 102
 
103
      // returns true if $parameters[$name] is set
1 rodolico 104
      public function __isset( $name ) {
6 rodolico 105
         return isset( $this->parameters[$name] );
1 rodolico 106
      }
13 rodolico 107
 
108
 
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
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;
133
         if ( ! $createStatement ) { // they did not set createStatement, so use our default
15 rodolico 134
            $auditTable = $this->parameters['auditTable'];
13 rodolico 135
            $createStatement = "
29 rodolico 136
               create table if not exists $auditTable (
13 rodolico 137
                  _activity_log_id int unsigned not null auto_increment,
138
                  timestamp  timestamp,
139
                  user       varchar(64),
140
                  query      text,
141
                  primary key(_activity_log_id)
142
               ) comment 'tracks queries which modify data'";
143
         } // if
144
         if ( parent::query( $createStatement ) === false ) {
145
            // on error, die
146
            print "Can not create audit table with query<br />\n$createStatement<br />\n";
147
            die ( printf("Errormessage: %d - %s\n", $this->errno, $this->error ) );
148
         } // if error, die
149
      } // buildAuditTable
150
 
151
      /*
152
       * log queries to a table, file, or nothing
153
       * log contains date/time, username and query
154
       * to turn off logginging:
155
       * unset( $parameters['auditTable'] )
156
       */
157
      private function logIt( $username, $query, $recursion = false ) {
158
         if ( ! isset( $this->parameters['auditTable'] ) )
159
            return; 
160
         $username = $this->real_escape_string( $username );
161
         $query = $this->real_escape_string( $query );
15 rodolico 162
         $logEntry = "insert into " . $this->parameters['auditTable'] . " (user, query) values ( '$username', '$query')";
13 rodolico 163
         //print "Loggging\n$logEntry\n";
164
         if ( parent::query( $logEntry ) !== false ) { // good
165
            return;
166
         } else { // we had an error
167
            if ( ! $recursion && $this->errno == 1146 ) { // table doesn't exist, so let's create it
15 rodolico 168
               $result = parent::query( "show tables like '" . $this->parameters['auditTable'] . "'" );
13 rodolico 169
               if ( $result->num_rows == 0 ) {
170
                  $this->buildAuditTable( );
171
                  return $this->logIt( $username, $query, true );
172
               }
6 rodolico 173
            } else {
13 rodolico 174
               print "Trying to log transaction with query<br />\n$logEntry<br />\n";
175
               die ( printf("Errormessage: %d - %s\n", $this->errno, $this->error ) );
176
            } // if..else
1 rodolico 177
         } // if
13 rodolico 178
      } // function logIt
179
 
180
 
181
      /*
182
       * doSQL
183
       * Parameters: $query - string or array of strings to be executed
184
       *             $parameters - hash used to pass additional parameters, to include
185
       *                           $parameters['username'] = 'fred'; // username for logging
36 rodolico 186
       *                           $parameters['returnType'] = 'hash'; or array or both
13 rodolico 187
       * 
188
       * executes one or more queries
189
       *
190
       * If the query is one of select, show, describe or explain, it must
191
       * be a single string. It will return the data results in a hash
192
       * containing
39 rodolico 193
       *    'returnData'  - an array of array/hash/both depending on what you asked for
13 rodolico 194
       *    'count' - number of results (ie, count(data)
195
       *    'meta'  - metadata for each column returned
196
       *    'numfields' - number of columns in result (ie, count(meta))
197
       *    'errors' - normally empty array of errors
198
       * 
199
       * if the query modifies data (ie, NOT above), query may be an array
200
       * which will be surrounded by a transaction and rolled back if
201
       * anything causes an error.
202
       *
203
       * These will return a hash containing
204
       *    'count' l- number of rows affected by last statement
205
       *    'last_insert_id' - last insert id created by BLOCK of queries
206
       *    'errors' - normally empty array of errors which occurred (caused a rollback)
207
       * 
208
       */
15 rodolico 209
      public function doSQL( $query = null, $parameters = array() ) {
13 rodolico 210
         $errors = array();
15 rodolico 211
         if ( isset( $query ) ) {
212
            $this->parameters['query'] = $query;
213
         }
42 rodolico 214
         if ( isset( $parameters['returnType'] ) ) {
215
            $this->parameters['returnType'] = $parameters['returnType'];
216
         }
13 rodolico 217
         // if it is a "selectstatement" it doesn't modify data
218
         // if query is an array, assume it modifies something
219
         // if it is a single statement, look for the regex
15 rodolico 220
         $selectStatement = is_array( $this->parameters['query'] ) ?
13 rodolico 221
            false :
29 rodolico 222
            ( preg_match( '/^\s*(select)|(show)|(describe)|(explain)/xsi', $this->parameters['query'] ) === 1 );
13 rodolico 223
 
224
         // different actions based on whether it modifies data or not
225
         if ( $selectStatement ) { // if a select, simply return the rows
226
            // dataset is small enough, we just read it into memory all at one time.
42 rodolico 227
            // set MYSQLI_USE_RESULT for unbuffered results
15 rodolico 228
            if ( $sth = parent::query( $this->parameters['query'], MYSQLI_USE_RESULT  ) ) {
13 rodolico 229
               if ( $sth === false ) {
230
                  $errors[] = array( 'id' => $this->errno, 'message' => $this->error );
231
               } else {
42 rodolico 232
                  // query can return either an array of array, array of hash, or both. Default is both
47 rodolico 233
                  //$returnType = MYSQLI_BOTH;
13 rodolico 234
                  $this->parameters['columnMetaData'] = $sth->fetch_fields(); // get metadata
47 rodolico 235
                  // in newer PHP, this is an array of objects, so we turn it into an array of array
236
                  foreach ( $this->parameters['columnMetaData'] as $index => $obj ) {
237
                     if ( is_object($obj) ) {
238
                        $this->parameters['columnMetaData'][$index] = (array) $obj;
239
                     }
240
                  }
13 rodolico 241
                  $this->parameters['returnData'] = array(); // we'll put all the results in an array
42 rodolico 242
                  // $fetchtype returns either an array of array, array of hash, or both. Default is both
243
                  switch ( $this->parameters['returnType'] ) {
244
                     case 'array': 
245
                        $returnType = MYSQLI_NUM;
246
                        break;
247
                     case 'associative':
248
                        $returnType = MYSQLI_ASSOC;
249
                        break;
250
                     default: $returnType = MYSQLI_BOTH;
13 rodolico 251
                  }
252
                  // slurp all the stuff in
42 rodolico 253
                  while ( $values =  $sth->fetch_array( $returnType ) ) {
38 rodolico 254
                     $this->parameters['returnData'][] = $values;
13 rodolico 255
                  }
256
                  $sth->free(); // cleanup memory, don't need two copies
257
               } // if we had no errors
1 rodolico 258
            }
13 rodolico 259
            $this->parameters[ 'rowsAffected' ] = count( $this->parameters[ 'returnData' ] );
260
            $this->parameters[ 'numfields' ] = count( $this->parameters['columnMetaData'] );
261
            $this->parameters[ 'lastInsertKey' ] = 0;
262
            $this->parameters[ 'error' ] = $errors;
263
         } else {
264
            if ( ! is_array( $this->parameters['query'] ) ) { // not an array, so make it one
265
               $temp = $this->parameters['query'];
266
               $this->parameters['query'] = array( $temp );
1 rodolico 267
            }
13 rodolico 268
            // do it in a transaction so we can back out on failure
269
            $this->autocommit(false);
270
            $allOk = true;
271
            for ( $i = 0; $i < count( $this->parameters['query'] ); $i++ ) {
15 rodolico 272
               // debugging
273
               //print "$i\t" . $this->parameters['query'][$i] ."\n"; continue;
274
               // debugging
13 rodolico 275
               $this->logIt( isset( $parameters['username'] ) ? $parameters['username'] : 'unknown', $this->parameters['query'][$i] );
276
               if ( parent::query( $this->parameters['query'][$i] ) === false ) { // we had an erorr
277
                  // record it
278
                  $errors[] = array( 'id' => $this->errno, 'message' => $this->error, 'query' => $this->parameters['query'][$i] );
279
                  $allOk = false;
280
                  // and bail
281
                  break;
282
               }
283
            }
284
            // if we made it through ok, commit, otherwise rollback
285
            $allOk ? $this->commit() : $this->rollback();
286
            // reset autocommit to true
287
            $this->autocommit(true);
288
            $this->parameters['query'] = $query;
289
            $this->parameters[ 'rowsAffected' ] = $this->affected_rows;
290
            $this->parameters[ 'lastInsertKey' ] = $this->insert_id;
291
            $this->parameters[ 'error' ] = $errors;
292
         } // if select .. else
293
         return $this->parameters;
294
      }  // function doSQL
17 rodolico 295
 
296
      public function run () {
297
         return $this->doSQL( );
298
      }
34 rodolico 299
 
300
      /**
44 rodolico 301
       * Simple function returns last inserted id on success, false on 
40 rodolico 302
       * failure
303
       * 
304
       * @params string $query An insert query to execute
305
       * @return integer The insert id from this insert, if applicable
306
       */
307
 
308
      public function insert( $query ) {
309
         return $this->query( $query ) ? $this->insert_id : false;
310
      }
311
 
44 rodolico 312
       /**
313
        * A query with at least two columns in the result is converted to a 
314
        * key => value array. The first column is the key, the second is the value
315
        * All other columns are ignored
316
        */
317
 
318
       public function queryToKeyedArray ( $query ) {
319
          $return = array();
320
         $result = $this->doSQL( $query, array( 'returnType' => 'array' ) );
321
         foreach ( $result['returnData'] as $row ) {
322
            $return[$row[0]] = $row[1];
323
         }
324
         return $return;
325
      }
326
 
40 rodolico 327
      /**
34 rodolico 328
       * returns an array of the first column for each row returned from query
329
       * 
330
       * The query is run, then for each row returns, the first column
331
       * is added to $return (an array). $return is then returned.
332
       * 
333
       * Used to do things like get an array of keyfields, or something
334
       * else.
335
       * 
336
       * @param string $query Query to run
337
       * @returns string[] Array of values
338
       */
339
      public function columnToArray( $query ) {
340
         $return = array();
341
         $result = $this->doSQL( $query );
342
         foreach ( $result['returnData'] as $row ) {
343
            $return[] = $row['id'];
344
         }
345
         return $return;
346
      }
1 rodolico 347
 
348
      /*
349
       * function will return one and only one row, NOT as an array of array
350
       * but as a single row array
351
       * if more than one row is returned by query, error is set and function
352
       * returns false.
353
       * Otherwise, function returns true
354
       */
355
      public function getOneRow( $sql = null ) {
356
         if ( isset( $sql ) )
6 rodolico 357
            $this->parameters[ 'query' ] = $sql;
36 rodolico 358
         $save = $this->parameters[ 'returnType' ];
359
         //print "<pre>" . print_r( $this->parameters['query'], true) . '</pre>';
43 rodolico 360
         $this->parameters['returnType'] = 'associative';
1 rodolico 361
         $this->run();
43 rodolico 362
         $this->parameters[ 'returnType' ] = $save;
363
         if ( $this->parameters[ 'rowsAffected' ] == 0 ) {
364
            return array();
365
         } elseif ( $this->parameters[ 'rowsAffected' ] == 1 ) {
6 rodolico 366
            $this->parameters[ 'returnData' ] = $this->parameters[ 'returnData' ][0];
35 rodolico 367
            return $this->parameters[ 'returnData' ];
37 rodolico 368
         } else {
43 rodolico 369
            $this->parameters['error'] = "$this->parameters[query] did not return a unique row";
370
            print "<pre>In Class DBQuery->getOneRow, no unique row in\n" . print_r($this->parameters[query], true) . "\n</pre>"; die;
37 rodolico 371
         }
1 rodolico 372
         return false;
373
      } // getOneRow
374
 
375
 
376
      // function returns the first column of the first row of data returned from query
377
      // or null no value returned
6 rodolico 378
      public function getOneDBValue( $sql = null ) {
34 rodolico 379
         //print '<pre>' . $sql . '</pre>';
1 rodolico 380
         if ( isset( $sql ) )
6 rodolico 381
            $this->parameters[ 'query' ] = $sql;
36 rodolico 382
         $save = $this->parameters['returnType'];
383
         $this->parameters['returnType'] = 'array';
1 rodolico 384
         $this->run();
36 rodolico 385
         $this->parameters['returnType'] = $save;
386
         //print "<pre>" . print_r($this->parameters,true ) . "</pre>";
6 rodolico 387
         return $this->parameters[ 'rowsAffected' ] ? $this->parameters[ 'returnData' ][0][0] : null;
1 rodolico 388
      }
6 rodolico 389
 
4 rodolico 390
      /*
391
       * function will attempt to make a constant ($value) safe for SQL depending on the type.
392
       * 
393
       * if $value is empty, $default is returned, as will happen if any of the
394
       * conversions (date, datetime, etc...) fail.
395
       * 
396
       * First, it will pass it through get_magic_quotes_gpc, 
397
       * then will run through mysql_real_escape_string
398
       * 
399
       * For strings, will encapsulate in quotes
400
       * Dates will attempt a conversion, then change to YYYY-MM-DD and encapsulate in quotes
401
       * DateTime will perform the same, but change to YYYY-MM-DD HH:MM:SS
402
       * Integer and Floats are passed through builtins intval and floatval
403
       * Boolean only checks the first character, a '0', 'f' and 'n' denoting false
404
       *    all else denoting true. The result is converted based on the variable
405
       *    $falsetrue, with the first char denoting false and the second denoting true
406
       */
407
      public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' ) {
408
         if (strlen($value) == 0) // simply set any empty values to null
409
            return $default;
410
         // print "Processing $value as $type with default $default<br>\n";
411
         switch ( strtolower( $type ) ) {
412
            case 'string' :
413
            case 's' : 
414
                     if ( get_magic_quotes_gpc() ) 
415
                        $value = stripslashes($value);
13 rodolico 416
                     $value = $this->real_escape_string( $value );
4 rodolico 417
                     $value = strlen( $value ) > 0 ? "'$value'" : $default;
418
                     break;
419
            case 'date' :
420
            case 'd' :
421
                     if ( $value != 'null' ) {
422
                        $result = strtotime( $value );
423
                        $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d', $result) . "'";
424
                     }
425
                     break;
426
            case 'datetime':
427
            case 'timestamp':
428
            case 'dt': 
429
                     if ( $value != 'null' ) {
430
                        $result = strtotime( $value );
431
                        $value = ( $result === false ) ? $default : "'" . Date( 'Y-m-d H:i:s', $result) . "'";
432
                     }
433
                     break;
434
            case 'integer':
435
            case 'i' :  
436
                     $value = intval( $value );
437
                     break;
438
            case 'float':
439
            case 'f' :  
440
                     $value = floatval( $value );
441
                     break;
442
            case 'bool':
443
            case 'boolean':
444
            case 'b' :  // note, because of the way strpos works, you can not
445
                        // simply set $value based on the output; you MUST do
446
                        // as below; specifically check for false, then set the result
447
                        $value =  strpos( '0fn', strtolower(substr( $value, 0, 1 )) ) === false ? 0 : 1;
448
                        $value = substr( $falsetrue, $value, 0, 1 );
449
                        break;
450
         } // switch
451
         return $value;
452
      }
1 rodolico 453
 
29 rodolico 454
      /*
455
         '[^']*(?!\\)'(*SKIP)(*F)       # Make sure we're not matching inside of quotes
456
         |(?m-s:\s*(?:\-{2}|\#)[^\n]*$) # Single line comment
457
         |(?:
458
           \/\*.*?\*\/                  # Multi-line comment
459
           (?(?=(?m-s:\h+$))         # Get trailing whitespace if any exists and only if it's the rest of the line
460
             \h+
461
           )
462
         )
463
      */
464
      /**
465
       * Functions strips SQL queries from a file. Above is a commented copy of the regex used
466
       * 
467
       * @param string $query An arbitrary sized SQL script
468
       * @returns string $query, with all SQL comments removed
469
       */
4 rodolico 470
 
29 rodolico 471
      public function strip_sqlcomment ($query = '') {
472
         $regex = '\'[^\']*(?!\\\)\'(*SKIP)(*F)|(?m-s:\\s*(?:\\-{2}|\\#)[^\\n]*$)|(?:\\/\\*.*?\\*\\/(?(?=(?m-s:\h+$))\\h+))';
473
         //print "<pre>$regex</pre>" ; die;
474
          return (($query == '') ?  '' : preg_replace( "/$regex/xs", '', $query ));
475
      }
476
 
477
      /**
478
       * runs an SQL script with multiple statements in it
479
       * 
37 rodolico 480
       * If $sql is an array, it is assumed each row is a separate query
481
       * 
482
       * If $sql is a string, will separate it into different lines and
483
       * execute them
484
       * 
29 rodolico 485
       * NOTE: it does this by breaking it based on the semicolon, so
486
       * in some weird situations, it will break at the wrong place.
487
       * 
488
       */
489
      public function runSQLScript( $sql ) {
37 rodolico 490
         if ( is_array( $sql ) ) {
491
            $queries = $sql;
492
         } else {
493
            $sql = $this->strip_sqlcomment( $sql );
494
            $queries = explode( ";", $sql );
495
         }
29 rodolico 496
         //print "<pre>" . print_r( $queries, true ) . '</pre>'; die;
497
         foreach ( $queries as $query ) {
498
            $this->doSQL( trim( implode( ' ', explode("\n",$query ) ) ) );
499
         }
37 rodolico 500
      } // runSQLScript
501
 
502
      /**
503
       * Overrides real_escape_string to change behaviour slightly
504
       * 
505
       * Will check if string is pure numeric and, if it is, will return
506
       * as it is. Otherwise, will call real_escape_string, then wrap
507
       * result in single quotes
508
       */
509
       public function my_escape_string( $string, $additionalEscapes = array() ) {
510
          if ( is_numeric( $string ) )
511
             return $string;
512
          $string = $this->real_escape_string( $string );
513
          if ( count( $additionalEscapes ) ) 
514
             $string = addcslashes( $string, implode( '', $additionalEscapes ) );
515
          return "'$string'";
516
       }
517
 
518
      /**
519
       * Builds a query of form update $tablename set $fields where $where
520
       * 
521
       * Creates a query that will update table $tablename. It assumes
522
       * $fields is an array where the indexes are fieldnames and the values
523
       * are the new values for the field. Will escape the values.
524
       * 
525
       * Appends $where, again where index is a field name
526
       */
527
      public function updateQuery( $tablename, $where, $fields ) {
528
         /*
529
         print "<pre>updateQuery\n\ntable\n$tablename\n</pre>";
530
         print "<pre>\nwhere\n" . print_r( $where, true ) . "\n</pre>";
531
         print "<pre>fields\n" . print_r( $fields, true ) . "\n</pre>"; die;
532
         */
533
         $sql = '';
534
         $updateFields = array();
535
         foreach ( $fields as $fieldname => $value ) {
536
            $updateFields[] = sprintf( "%s = %s", $fieldname, $this->my_escape_string( $value ) );
537
         }
538
         $sql = "update $tablename set " . implode( ", ", $updateFields );
539
         $updateFields = array();
540
         foreach ( $where as $fieldname => $value ) {
541
            $updateFields[] = sprintf( "%s = %s", $fieldname, $this->my_escape_string( $value ) );
542
         }
543
         if ( count( $updateFields ) ) {
544
            $sql .= ' where ' . implode( ' and ', $updateFields );
545
         }
546
         return $sql;
547
      }
548
 
549
      /**
550
       * Creates an insert query from $fields
551
       * 
552
       */
553
       public function insertQuery( $tablename, $fields ) {
40 rodolico 554
          //print "<pre>insertQuery\n\ntable\n$tablename\n</pre>";
555
          //print "<pre>fields\n" . print_r( $fields, true ) . "\n</pre>"; die;
37 rodolico 556
          $query = "insert into $tablename (" . implode( ',',array_keys($fields) );
40 rodolico 557
          $query .= ") values (" . implode( ',', array_map( array($this, 'my_escape_string'), array_values( $fields ) ) );
37 rodolico 558
          return "$query)";
559
       } // insertQuery
44 rodolico 560
 
46 rodolico 561
      function insertValuesIntoQuery( $query, $values ) {
562
         foreach ( $values as $name => $value ) {
563
            $query = $this->search_replace_string($query, "<$name>", $value );
564
         }
565
         return $query;
566
      }
44 rodolico 567
 
46 rodolico 568
      function search_replace_string($string, $searchFor, $replaceWith ) {
569
         $string = str_replace ( $searchFor, $replaceWith, $string );
570
         return $string;
571
      }
572
 
573
 
37 rodolico 574
 
29 rodolico 575
 
1 rodolico 576
   } // class DBQuery
577
 
17 rodolico 578
/*
579
 *    $db = new DBQuery( '127.0.0.1', 'camp', 'camp', 'camp' );
13 rodolico 580
 
581
   if ($db->connect_error) {
582
       die('Connect Error (' . $db->connect_errno . ') '  . $db->connect_error);
583
   }
584
   $result = $db->doSQL(
585
               array(
586
                     'drop table if exists temp',
587
                     'create table temp ( col1 int unsigned )',
588
                     "insert into temp values ('mike')"
589
                     )
590
         );
15 rodolico 591
   if ( $result['error'] ) {
13 rodolico 592
      print_r ( $result );
15 rodolico 593
      die ( DBQuery::error2String( $result['error'] ) );
13 rodolico 594
   } else {
595
      print "running select\n";
596
      $result = $db->doSQL( 'select * from temp' );
597
      print_r( $result );
598
   }
599
//   $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'" );
600
//   print_r( $return );
601
//   print_r( $db );
17 rodolico 602
*/
1 rodolico 603
?>
13 rodolico 604
 
605
 
606
 
607
 
608
 
609
 
610
 
611
 
612
 
613