Subversion Repositories php_library

Rev

Rev 40 | Blame | Compare with Previous | Last modification | View Log | Download | RSS feed

<?php

   require_once "DBDatabase.class.php";
   require_once "Logging.class.php"; // For logging

   define( HTML_QUOTE_CHAR, '"' );
   define( CONSTANT_NO_VALUE_DROPDOWN, '--------' );

   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();
            for ($i = 0; $i < mysql_num_fields( $result ); $i++) {
               $meta = mysql_fetch_field ( $result );
               $this->columnMetaData[] = array('name' => $meta->name, 'length' => $meta->max_length, 'numeric' => $meta->numeric, 'type' => $meta->type );
            }
            if( ! $this->rowsAffected ) return;
            $this->returnData = array();
            for( $i = 0; $i < $this->rowsAffected; $i++ ) {
               $this->returnData[] = $this->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 );
         }
      }

   } // class DBQuery

   
   /* 
      This class simply extends DBQuery to allow output to be converted to HTML
   */
   
   class DBQueryHTML extends DBQuery {
   
      /*
         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
            // Build the searchFor array for $append
            $searchFor = array();
            foreach ( $this->columnMetaData as $field ) {
               $searchFor[] = '%' . $field['name'] . '%';
            }
            $this->returnData = $result['data'];
            unset ($result);
            /* special condition where only one row is returned. In that case, $this->returnData is not an array
            of associations, but a simple association. In this case, we need to convert it
            */
            if (count($this->returnData[0]) == 1) { // convert from association to single row array of associations
               $temp = array();
               foreach ($this->returnData as $column => $value) {
                  $temp[0][$column] = $value;
               } // foreach
               $this->returnData = $temp;
            }
            if (count($format) > 0 ) { // we have some formats, so let's do it the hard, slow way
               for ( $row = 0; $row < count($this->returnData); $row++) {
                  $rows[$row] = '';
                  for ( $column = 0; $column < count($this->returnData[$row]); $column++  ) {
                     $rows[$row] .= strlen($format[$column]) && isset($this->returnData[$row][$column])> 0 ?
                                    ( $tdNumberDefinition . sprintf($format[$column],$this->returnData[$row][$column]) . '</td>')
                                    : ($tdTextDefinition . $this->returnData[$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, $this->returnData[$row], $append ) . '</td>';
                  }
               } // for $row
            } else { // no formatting, so we just slam the stuff together
               for ( $row = 0; $row < count($this->returnData); $row++) {
                  $currentValue = $tdTextDefinition . implode( '</td>' . $tdTextDefinition, $this->returnData[$row]) . '</td>';
                  if (strlen($append) > 0) { // see explaination in if part of this structure
                     $currentValue  .= $tdTextDefinition . str_replace ( $searchFor, $this->returnData[$row], $append ) . '</td>';
                  }
                  $rows[] = $currentValue;
               }
            }
            // ok, let's get the field headers from the table
            $html .= '<tr>';
            foreach ( $this->columnMetaData 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 DBQueryHTML

?>

Generated by GNU Enscript 1.6.5.90.