DBQuery

The DBQuery class extends mysqli, so it inherits all of those functions. It can be used as a drop in replacement for mysqli with a few enhancements

• when using class function doSQL
  • Accepts an array of queries to be executed
  • queries which modify data can be logged to a separate table in the database.
  • Error trapping done more conveniently, with lists of errors returned as part of the return value
  • queries which modify data are wrapped in a commit/rollback block, so any failure in any query results in the database being in the form it was before the block was executed
  • returns a hash containing all rows from a select, a count of the number of rows returned, any errors and meta data on the columns returned
  on data modifying queries, returns number of rows affected, last_insert_id and errors
• Additional convenience functions getOneRow and getOneDBValue
• Enhanced real_escape_string (function makeSafeSQLConstant)
  • correctly delimits fields based on data type (default string)
  • interprets strings which are date, date/time, integer, float and boolean values

public function doSQL( $query, $parameters )

• $query
  string containing a single select, show, describe or explain statement, or a single string/array of strings containing insert, delete, update or DDL statements.
• $parameters
  hash of key/value pairs which is merged into class member $this->parameters. Convenient way to modify the way a query is executed. Also used to pass 'username' for audit function

Non Data Modifying queries

doSQL runs one query. A single string starting with one of the keywords select, show, describe or explain is run as is, with results stored in the $parameters hash which is returned by the function. Hash key/value pairs are as follows:

• 'returnData'
  Array (one row per return row from query). Each row contains either
  • associative array, where key is field name and value is the value returned by the query
  • indexed array, where $parameters[0][0] is the first column of the first row returned
  • mixed array, each column is duplicated, combining associative and indexed
• 'columnMetaData'
  metadata for each column returned by the query. See mysqli documentation for structure
• 'returntype'
  'fetchType'
  type of result requested. 'returntype' is the human readable associative, array or both, and 'fetchType' is the actual mysqli constant associated with it. Default is 'associative' and MYSQLI_ASSOC
• 'rowsAffected'
  number of rows returned by query
• 'numfields'
  number of columns in results
• 'error'
  (possibly empty) indexed array of errors encountered.

Following code illustrates how to use doSQL to run a simple query.

            $db = new DBQuery( '127.0.0.1', 'camp', 'camp', 'camp' );
            if ($db->connect_error) {
                die('Connect Error (' . $db->connect_errno . ') '  . $db->connect_error);
            }
            $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'" );
            if ( $return['error'] ) {
               die ( DBQuery::error2String( $result['error'] ) );
            } else {
               for ( $i = 0; $i < $result['rowsAffected']; $i++ ) {
                  foreach ( $result['returnData'][$i] as $columnName => $value ) {
                     print "Row $i, Column $columnName == $value\n";
                  }
               }
            }
      

Data Modifying queries

If the query is an array, or if it does not start with one of select, show, describe or explain, it is treated as a group of statements which will modify data. At this point, the query is treated differently.

• if it is a single string, it is converted to an array with one value
• mysqli::autocommit is turned off
• For each query in the array
  • the function logIt is called, which records the query being executed
  • Query is executed
  • If an error occurs, no more statements are executed, the error array is updated with the mysql error
• If no errors occured, a commit is executed. Otherwise, rollback is called

When complete, the results are stored in the parameters also.

• 'query'
  contains the array of queries which were suposed to be executed
• 'rowsAffected'
  contains the number of rows modified by the last query executed
• 'lastInsertKey'
  contains the last used auto-increment value
• 'error'
  contains a (possibly empty) array of errors encountered

Following code illustrates how to use doSQL to create a table and populate it

         $db = new DBQuery( '127.0.0.1', 'camp', 'camp', 'camp' );

         if ($db->connect_error) {
             die('Connect Error (' . $db->connect_errno . ') '  . $db->connect_error);
         }
         $result = $db->doSQL(
                     array(
                           'drop table if exists temp',
                           'create table temp ( col1 int unsigned )',
                           "insert into temp values ('mike')"
                           )
               );
         if ( $result['error'] ) {
            print_r ( $result );
            die ( DBQuery::error2String( $result['error'] ) );
         }         
      

private function logIt( $username, $query, $recursion = false )

this function stores all queries which modify data. The first two parameters are the user who is logged in, and the query being executed. The third parameter, $recursion, is only used internally do detect the function calling itself.

logIt populates the table defined in $this->parameters['auditTable'] which defined as _activity_log by default. If the table does not exist, it is created with the following SQL.

         create table _activity_log (
                  _activity_log_id int unsigned not null auto_increment,
                  timestamp  timestamp,
                  user       varchar(64),
                  query      text,
                  primary key(_activity_log_id)
               ) comment 'tracks queries which modify data         
      

The name of the table, and its structure can be overridden by directly calling public function buildAuditTable( $tablename = '', $createStatement = ''). If $tablename or $createStatement is not passed in, the defaults are used. The audit table must have the fields in question, however.

Update Statements

queries which begin with the keyword update are treated slightly different. The query is parsed for the table name and the where clause, and a new query is created which retrieves the values in the table before the update command is executed. All rows returned are then stored in the query column of the audit table before the command is executed. Note: this function only works for single table updates with clearly defined where clauses, ie update tablename set column=value, set column = value where condition [order by] ... [limit] ...

public static function makeSafeSQLConstant ( $value, $type='S', $default='null', $falsetrue='10' )

This is an extension of mysqli::real_escape_string which is aware of the data type you are expecting to pass in. The default is string, in which case it simply strips slashes, does the standard real_escape_string, and encloses the result in single quotes.

In all cases, if $value is empty, returns keyword null. This can be overridden by setting the $default parameter (warning, you are responsible for delimiting and escaping this)

If a data type other than string is set, the following actions are taken

• $type == 'date' or 'd'
  attempts to convert the string to a date using strtotime, and returns it in the format YYYY-MM-DD enclosed in single quotes. Any time portion is discarded
• $type == 'datetime', 'timestamp' or 'dt'
  Attempts to conver the string to date and time using strtotime, returning it in format YYYY-MM-DD HH:MM:SS, enclosed in single quotes.
• $type == 'integer', 'i', 'float', 'f'
  uses intval and floatval respectively to convert the string to a number. Returns the result as an unenclosed string.
• $type == 'bool', 'boolean' or 'b'
  Looks for the character 0, 'f' or 'n'. If one of those is the first character in the string, returns 0, else returns 1. Note this can be overridden by setting the paramters $falsetrue when calling the function.

A note on the date and datetime. This uses strtotime, which is a very powerful function, accepting strings like 'tomorrow at 5pm' and, in most cases, correctly calculating the result. However, the functionality of this is at times erratic.

public function getOneRow( $sql = null )

executes a query, returning only the first row as an associative array

public function getOneDBValue( $sql = null )

executes query, returning only the first column of the first row found.

Convenience functions

• public static function error2String( $error )
  when passed an array of errors, returns a string of newline terminated, human readable text. One line per array entry
• load( $parameters ) and save()
  public functions which simply retrieve or set $this->parameters
• __set( $name, $value) and __get( $name )
  public functions which retrieve or set individual members of the $this->parameters array