Subversion Repositories php_library

<?php

   require "DBDatabase.class.php";

   class DBQuery {

      public $query;          // the query to be processed

      public $rowsAffected;   // the number of rows 1) affectd by an insert, update or delete or 2) returned by a select

      public $lastInsertKey;  // the value of the last row inserted (mysql only)

      public $returnData;     // an array that contains the data retrieved by a query (select only)

      public $columnMetaData; // an array that contains the meta data from the query

      public $useAssociativeArray;   // if true, $returnData is an array of associative array, with the column names being the key fields

      public $showErrors;     // if true, will return errors to STDOUT

      public $error;          // set to last error returned

      public $logFile;        // if set to a fully qualified file, will write queries to that file

      public $auditTable;     // if set to a table name, all queries are written to it

      public function __construct( $query ) {

         $this->query = $query;

         $this->rowsAffected = 0;

         $this->lastInsertKey = '';

         $this->returnData = array();

         $this->columnMetaData = array();

         $this->useAssociativeArray = true;

         $this->showErrors = true;

         $this->error = '';

      }

      /* 

         function actually executes the query, populating the members with the results

         returns true if no errors, false if errors (see $this->error for code)

      */

      function run {

         // print "<pre>In Query database\n---------------------\n$query\n---------------------\n</pre>";

         if ($logFile) printLog( $query );

         if ($auditTable) audit( $query );

         $result = @mysql_query($this->query);

         if( mysql_errno() ) {

            $this->error = 'MYSQL ERROR #'.mysql_errno().' : <small>' . mysql_error(). "</small><br><VAR>$query</VAR>";

            if ( $this->showErrors ) echo($this->error);

            return false;

         } // if

         if( preg_match ( '/^\s*select/i', $this->query ) ) { // this is a select statement

            $this->rowsAffected = @mysql_num_rows($result);

            $this->columnMetaData = array();

            while ($i++ < mysql_num_fields( $result )) {

               $meta = mysql_fetch_field ( $result );

               //objectDebugScreen($meta);

               $this->columnMetaData[] = array('name' => $meta->name, 'length' => $meta->max_length, 'numeric' => $meta->numeric, 'type' => $meta->type );

            }

            if( ! $this->rowsAffected ) return;

            $returnData = array();

            for( $i = 0; $i < $count; $i++ ) {

               $returnData[] = $useAssociativeArray ? mysql_fetch_assoc($result) : mysql_fetch_row($result);

            }

            mysql_free_result($result);

         } else { // not a select statement

            $this->rowsAffected = mysql_affected_rows();

            $this->lastInsertKey = mysql_insert_id();

         }

         return true;

      } // function run

      // function returns the first column of the first row of data returned from query

      // or null no value returned

      function getOneDBValue( $sql ) {

         $save = $this->useAssociativeArray;

         $useAssociativeArray = false;

         $this->run();

         $useAssociativeArray = $save;

         return $this->rowsAffected ? $this->returnData[0][0] : null;

      }

   function countNumberOfRows ( $sql ) {

         $save = $this->useAssociativeArray;

         $useAssociativeArray = false;

         $this->run();

         $useAssociativeArray = $save;

         return $this->rowsAffected;

   }

      function makeWhereClause ($conditions, $joinedBy = 'and') {

         $joinedBy = " $joinedBy "; // make sure there are spaces around it

         $whereClause = ' where ' . implode ( $joinedBy, $conditions );

         return $whereClause;

      }

      /* 

         values is an associative array of name/value pairs

         function will replace all items of the form <$name> with its values (the less than and greater than symbols around the key)

      */

      function insertValuesIntoQuery( $values ) {

         foreach ( $values as $name => $value ) {

            $this->query = search_replace_string($this->query, "<$name>", $value );

         }

      }

      /*

         Function takes an SQL statement and converts it to an HTML table.

         Return Value: HTML table representation of the query

         Parameters:

            $sql    A valid SQL query to run

            $format An optional array of format strings (suitable for printf) for each column (empty strings ignored)

            $makeTableDef  If True, the resulting HTML has the <table></table> tags; otherwise starts with header

            $append   Arbitrary string that is appended as a single column to each row

            NOTE ON $append

               $append may optionally contain variables of the form %colname%, which will be replaced

               with values from the current row. $colname is taken from the META data from the query, thus

               the query select foo from bar would have a meta of header of foo for column 0.

               $append is searched for strings of form %foo% in that case, and the current value of column

               foo replaces the tag %foo%.

               %foo% is ignored if foo is not a valid column name in a query.

               thus, a query such as select zip,city,state from zip_codes and a string of Hello %city% I'm glad

               you are in %State% will not replace the second as State is not a column of this query (it is case

               sensitive). Also, beware of items like select zip,concat(state,', ', city) from zip_codes. It is

               much better to rewrite that as select zip,concat(state,', ', city) mytown from zip_codes.

               If you don't know what all that means, get a book on SQL

      */

      function queryToTable ( $format = '', $makeTableDef = true, $append='' ) {

         //print "\n\n$this->query\n\n";

         $tdTextDefinition = '<td valign=' . HTML_QUOTE_CHAR . 'top' . HTML_QUOTE_CHAR . '>';

         $tdNumberDefinition = '<td valign=' . HTML_QUOTE_CHAR .'top' . HTML_QUOTE_CHAR . 'align=' . HTML_QUOTE_CHAR . 'right' . HTML_QUOTE_CHAR . '>';

         $html = '';

         $save = $this->useAssociativeArray;

         $useAssociativeArray = false; // temporarily turn off associative arrays

         $this->run();

         if ( $this->rowsAffected ) { // we returned at least one row

            $meta = $result['meta'];

            // Build the searchFor array for $append

            $searchFor = array();

            foreach ( $meta as $field ) {

               $searchFor[] = '%' . $field['name'] . '%';

            }

            $info = $result['data'];

            unset ($result);

            /* special condition where only one row is returned. In that case, $info is not an array

            of associations, but a simple association. In this case, we need to convert it

            */

            if (count($info[0]) == 1) { // convert from association to single row array of associations

               $temp = array();

               foreach ($info as $column => $value) {

                  $temp[0][$column] = $value;

               } // foreach

               $info = $temp;

            }

            if (count($format) > 0 ) { // we have some formats, so let's do it the hard, slow way

               for ( $row = 0; $row < count($info); $row++) {

                  $rows[$row] = '';

                  for ( $column = 0; $column < count($info[$row]); $column++  ) {

                     $rows[$row] .= strlen($format[$column]) && isset($info[$row][$column])> 0 ?

                                    ( $tdNumberDefinition . sprintf($format[$column],$info[$row][$column]) . '</td>')

                                    : ($tdTextDefinition . $info[$row][$column] . '</td>');

                  } // for $column

                  /*

                     let's append some stuff to the row if it exists.

                     We will take the array of SearchFor containing the column names

                     and the array of values from this row, then look for matches and replace them

                     with the correct values. Note, the column names in $append should have percent

                     signs appended and prepended, thus a column name of joe would be %joe% in %append

                  */

                  if (strlen($append) > 0) { // let's append some stuff to the row

                     $rows[$row] .= $tdTextDefinition . str_replace ( $searchFor, $info[$row], $append ) . '</td>';

                  }

               } // for $row

            } else { // no formatting, so we just slam the stuff together

               for ( $row = 0; $row < count($info); $row++) {

                  $currentValue = $tdTextDefinition . implode( '</td>' . $tdTextDefinition, $info[$row]) . '</td>';

                  if (strlen($append) > 0) { // see explaination in if part of this structure

                     $currentValue  .= $tdTextDefinition . str_replace ( $searchFor, $info[$row], $append ) . '</td>';

                  }

                  $rows[] = $currentValue;

               }

            }

            // ok, let's get the field headers from the table

            $html .= '<tr>';

            foreach ( $meta as $field ) {

               $html .= '<th>' . $field['name'] . '</th>';

            }

            $html .= '</tr>';

            // we now have all the info, so let's make it into a table

            $html .= '<tr>' . implode('</tr><tr>', $rows ) . '</tr>';

            if ( $makeTableDef ) { // they want the <table> def, so let's give it to them

               $html = '<table border=' . HTML_QUOTE_CHAR . '1' . HTML_QUOTE_CHAR . '>' . $html . '</table>';

            } // if ( $makeTableDef

         } // if ( $info = queryDatabase($this->query,0) )

         // print '<pre>';  print_r ($info); print '</pre>';

         $useAssociativeArray = $save;

         return $html;

      }

      /*

         function executes a query, then returns an array containing only the values of the first

         field in the return as the keys to an associative array. NOTE: any duplicates will be

         discarded. This is useful when you simply want to know if a value exists, ie if you

         are building an html select from a query, and a separate query needs to hold the

         values that are currently selected

      */

      function valuesToKeys () {

         $returnValue = array();

         $save = $this->useAssociativeArray;

         $useAssociativeArray = false; // temporarily turn off associative arrays

         $this->run();

         if ( $this->rowsAffected ) { // we returned at least one row

            foreach ( $info as $key => $value ) { // only thing I know to do is iterate

               $returnValue[$this->returnData[0]] = true;

            }

         } // if

         $useAssociativeArray = $save;

         return $returnValue;

      }

      /* this function will take the result of an SQL query that returns at least two columns.

         The first column becomes the keys in an associative array, and the second column

         becomes the value.

         Note, because it is an associative array, duplicates of the first column will only contain

         the last value.

      */

      function ColumnColumnArray () {

         $returnValue = array();

         $save = $this->useAssociativeArray;

         $useAssociativeArray = false; // temporarily turn off associative arrays

         $this->run();

         if ( $this->rowsAffected ) { // we returned at least one row

            foreach ( $info as $key => $value ) { // only thing I know to do is iterate

               $returnValue[$this->returnData[0]] = $this->returnData[1];

            }

         } // if

         $useAssociativeArray = $save;

         return $returnValue;

      }

      /* the first returned column becomes the value, the second becomes the display element

         if $selectedFieldsQuery is begins with 'select', items matching that query will be selected

         if  $selectedFieldsQuery is any other value, it is assumed to be the value of the first column

      */

      function htmlSelect ( $selectedFieldsQuery = '' ) {

         $html = '';

         $selectedFields = array();

         if strlen( $selectedFieldsQuery ) {

            if (  preg_match ( '/^\s*select/i', $selectedFieldsQuery ) ) { // They passed in a query

               if (strlen($selectedFieldsQuery) ) {

                  $subSet = new DBQuery($selectedFieldsQuery);

                  $subSet->run();

                  $selectedFields = $subSet->sqlValuesToKeys();

               }

            } else { // assume the passed in a value

               $selectedFields[$selectedFieldsQuery] = 1;

            }

         }

         $save = $this->useAssociativeArray;

         $useAssociativeArray = false; // temporarily turn off associative arrays

         $this->run();

         if ( $this->rowsAffected ) { // we returned at least one row

            foreach ($this->returnData as $rowNumber => $value )

               $html .= '<option value="' . $value[0] . '"';

               if ( $selectedFields[$value[0]] ) {

                  $html .= ' selected';

               }

               $html .= '>' . $value[1] . '</option>';

            }

         }

         $useAssociativeArray = $save;

         return $html;

      }

      /* function will take a query and turn it into a series of check boxes. It must contain

         two columns, with the first becoming the the name of the checkbox

         and the second becoming the displayed value. an optional third column will be used

         to match if the $checkedValuesQuery is used.

         if $checkedValuesQuery is not empty, it will be run to see what boxes need to be

         checked by being compared to the third column of the $sql query.

         $htmlBefore will be placed before each check box, and $htmlAfter will be placed after

         each checkbox.

         if $tableColumns is set to a number, the checkboxes will be embedded in a group of

         <tr></tr>, each containing table columns of $tableColumns width. In this case, $htmlBefore

         will have <td> prepended and $htmlAfter will have </td> appended, meaning any passed

         values will be INSIDE of the td. NOTE: the <table></table> tags are NOT put in.

         NOTE: currently, using the table stuff will leave a dangling row with 0 elements if

         the number of elements equal the number of columns.

      */

      function htmlCheckBoxes ( $checkedValuesQuery = '', $htmlBefore = '', $htmlAfter = '', $table_columns='' ) {

         $html = '';

         if ($table_columns) {

            $htmlBefore = '<td>' . $htmlBefore;

            $htmlAfter .= '</td>';

            $html .= '<tr>';

         }

         $numColumns = 0;

         $checkBoxes = queryDatabaseExtended( $sql,0 );

         $selectedFields = array();

         if (strlen($checkedValuesQuery) ) {

            $subSet = new DBQuery($selectedFieldsQuery);

            $subSet->run();

            $selectedFields = $subSet->sqlValuesToKeys();

         }

         $save = $this->useAssociativeArray;

         $useAssociativeArray = false; // temporarily turn off associative arrays

         foreach ($this->returnData as $row => $values) {

            if ($table_columns && ++$numColumns == $table_columns) {

               $html .= '</tr><tr>';

               $numColumns = 0;

            }

            //objectDebugScreen($row);

            $html .= $htmlBefore . '<input type="checkbox" name="' . $values[0] . '"';

            if ( $selectedFields[$values[2]] ) {

               $html .= ' checked';

            }

            $html .= '>' . $values[1] . $htmlAfter;

            //<INPUT type="checkbox" checked name="temp">

         } // foreach

         $html .= '</tr>';

         $useAssociativeArray = $save;

         return $html;

      }

   /*   

      function CSV ( $sql ) {

         $rows = array();

         $CSV = '';

         $info = array();

         if ( $result = queryDatabaseExtended($sql,0) ) {

            $meta = $result['meta'];

            //objectDebugScreen($result);

            $info = $result['data'];

            unset ($result);

            $headers = array();

            foreach ( $meta as $field ) {

               $headers[] = $field['name'];

            } // foreach

            $rows[] = implode("\t", $headers);

            // special condition where only one row is returned. In that case, $info is not an array

            //  of associations, but a simple association. In this case, we need to convert it

            if (count($info[0]) == 1) { // convert from association to single row array of associations

               $temp = array();

               foreach ($info as $column => $value) {

                  $temp[0][$column] = $value;

               } // foreach

               $info = $temp;

            }

            for ( $row = 0; $row < count($info); $row++) {

               $rows[] = implode("\t", $info[$row]);

            }

            // we now have all the info, so let's make it into a table

            $CSV .= implode("\n", $rows );

         } // if ( $info = queryDatabase($sql,0) )

         // print '<pre>';  print_r ($info); print '</pre>';

         return $CSV;

      }

   */

   } // class DBQuery

?>