Subversion Repositories phpLibraryV2

<?php

   /* 

      This class simply extends DBQuery to allow output to be converted to HTML

   */

   require_once( 'DBQuery.class.php' );

   class DBQueryHTML extends DBQuery {

      protected $htmlQuoteChar = '"';

      /*

         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 = array(), $makeTableDef = true, $append='', $headers = '' ) {

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

         $tdTextDefinition = '<td valign=' . $this->htmlQuoteChar . 'top' . $this->htmlQuoteChar . '>';

         $tdNumberDefinition = '<td valign=' . $this->htmlQuoteChar .'top' . $this->htmlQuoteChar . 'align=' . $this->htmlQuoteChar . 'right' . $this->htmlQuoteChar . '>';

         $html = '';

         $rows = array();

         $save = $this->returnType;

         $this->returnType = MYSQLI_ASSOC; // temporarily turn off associative arrays

         $columnNames = array();

         $this->run();

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

            //print "<pre>Found " . $this->rowsAffected . "\n</pre>";

            // get the column names; we'll use them later

            foreach ( $this->columnMetaData as $field ) {

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

            }

            // Build the searchFor array for $append

            $searchFor = $columnNames;

            array_walk( $searchFor, function ( &$value, $key ) { $value = '%' . $value . '%'; } );

            // print '<pre>SearchFor ' . print_r( $searchFor, true ) . '</pre>'; die;

            //print "<pre>" . print_r($this->columnMetaData,true) . '</pre>'; die;

            /* 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++) {

                  $temp = array();

                  foreach ( $columnNames as $col ) {

                     $temp[] = $this->returnData[$row][$col];

                  }

                  $currentValue = $tdTextDefinition . implode( '</td>' . $tdTextDefinition, $temp) . '</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>';

            if ( $headers ) { // user has passed in some header fields for us to use

               $html .= '<th>' . implode( '</th><th>',  $headers ) . '</th>';

            } else { // no headers, so we just get them from the returned column names

               foreach ( $this->columnMetaData as $field ) {

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

               }

            } // if headers .. else

            $html .= '</tr>';

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

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

         } else {

            $html = '<tr><td>No rows returned by the query</td></tr>';

         }

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

            $html = '<table border=' . $this->htmlQuoteChar . '1' . $this->htmlQuoteChar . '>' . $html . '</table>';

         } // if ( $makeTableDef

         $this->returnType = $save;

         //return '<pre>' . print_r ($this,true) .  '</pre>';

         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 ( $query = null ) {

         if ( isset( $query) ) {

            $this->parameters['query'] = $query;

         }

         $returnValue = array();

         $save = $this->useAssociativeArray;

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

         $this->run();

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

            //print '<pre>' . print_r($this->parameters, true) . '</pre>'; die;

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

               $returnValue[$value[0]] = $value[1];

            }

         } // if

         $useAssociativeArray = $save;

         return $returnValue;

      }

      /**

       * Builds an HTML select out of a query, or data

       * 

       * $parameters may contain the following keys

       * 

       * Must have one of the following. If both are present, 'query' is

       * ignored

       * 'query' - An SQL query to run. Should return 2 columns; first is value, second id display

       * 'data' - An array of key/value pair. Key is used for value, value is display

       * 

       * 'name' - HTML name for the select. If this is not set, only the options will be returned

       * 'class' - used for class='whatever' in select

       * 'selected' - key in 'data' or result of 'query' which will be selected

       * 'label' - used as <label>. If this is not set, no <label> is generated

       * 'nullok' - adds a entry at top with value of -1 and display of three dashes (---)

       * 

       * 

       * @parameters string $parameters an array of data to decide what/how to process

       * @returns string an HTML select entity

       */

      function htmlSelect ( $parameters ) {

         //print "<pre>" . print_r( $parameters, true ) . "</pre>"; die;

         if ( empty( $parameters['data'] ) ) {

            // print "<pre>No data, running query\n" . $parameters['query'] . "</pre>"; die;

            if ( empty( $parameters['query'] ) )

               return '';

            $save = $this->useAssociativeArray;

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

            //print "<pre>Running Query\n</pre>";

            $data = $this->doSQL( $parameters['query'] );

            //print "<pre>" . print_r( $data, true ) . "</pre>"; die;

            if ( $data['rowsAffected'] ) { // we returned at least one row

               $data = $data['returnData'];

               foreach ($data as $rowNumber => $value ) {

                  $parameters['data'][$value[0]] = $value[1];

               }

            }

         }

         //return "<pre>Data is \n" . print_r( $parameters['data'], true ) . "</pre>";

         $selectedKey = isset( $parameters['selected'] ) ? $parameters['selected'] : '';

         $options = array();

         if ( isset( $parameters['nullok'] ) ) {

            $options[] = '<option value="-1">---</option>';

         }

         foreach ( $parameters['data'] as $key => $value ) {

            $options[] = sprintf( 

               '<option value="%s"%s>%s</option>',

               $key,

               $key == $selectedKey ? ' selected' : '',

               $value

               );

         } // foreach

         $return = implode( "\n", $options );

         if ( isset( $parameters['name'] ) ) {

            $return = "<select name='$parameters[name]'>\n$return\n</select>";

            if ( isset( $parameters['label'] ) ) {

               $return = "<label>$parameters[label]\n$return\n</label>\n";

            }

         }

         return $return;

      }

      /**

       * Create an HTML checkbox block from a query, or array of data passed in

       * 

       * if $parameters['data'] is set, no further query is used. $parameters['data']

       * should contain an array of data. Each row in that array should be another 

       * array with keys 

       *    'id' -- 'id' and 'name' of the checkbox

       *    'name' -- display name of the checkbox

       *    'checked' -- boolean (or 0/1) saying whether this has a check

       * 

       * of $parameters['data'] is not set and $parameters['query'] is set

       * the query will be run. It MUST contain the column names listed above.

       * 

       * If $parameters['template'] is set, that template will be used. If it

       * is not set, a template will be defaulted to.

       * 

       * If $paramters['arrayName'] is set, the form of the template will be

       * name='arrayname[name]'

       * 

       * Processing will then continue

      */

      function htmlCheckBoxes ( $parameters ) {

         if ( empty( $parameters['template'] ) ) { // they did not send us a template

            if ( empty( $parameters['arrayName'] ) ) {

               $parameters['template'] = "<li><input type='checkbox' id='~~id~~' name='~~id~~' ~~checked~~>\n<label for='~~id~~'>~~name~~</label></li>\n";

            } else {

               $parameters['template'] = "<li><input type='checkbox' id='$parameters[arrayName]~~id~~' name='$parameters[arrayName][~~id~~]' ~~checked~~>\n<label for='~~id~~'>~~name~~</label></li>\n";

            }

         }

         //print "<pre>" . print_r($parameters, true) . "</pre>"; die;

         if ( empty( $parameters['data'] ) ) {

            if ( empty( $parameters['query'] ) )

               return '';

            $save = $this->useAssociativeArray;

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

            $this->doSQL( $parameters['query'] );

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

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

                  $parameters['data'][] = $value;

               }

            }

         }

         //print "<pre>" . print_r($parameters, true) . "</pre>"; die;

         foreach ( $parameters['data'] as $key => $value ) {

            $replacement = array(

               'id' => $value['id'], 

               'name' => $value['name'], 

               'checked' => $value['checked'] ? 'checked' : ''

               );

            $html[] = $this->templateReplace($parameters['template'],$replacement);

         }

         return '<ul>' . implode( '', $html ) . '</ul>';

      }

      /**

       * Replaces instances of replacement strings in string

       * 

       * Designed to allow the caller to build a string with things to be

       * replaced, for example

       * <input type='text' name='~~name~~'>

       * All instances of ~~name~~ will be replaced 

       */

      public function templateReplace ( $template, $replacmentStrings, $delimiter = '~~' ) {

         //print "<pre>Replacement Strings\n" . print_r($replacmentStrings, true) . "</pre>";

         //print "<pre>Template\n" . print_r($template, true) . "</pre>"; die;

         foreach ( $replacmentStrings as $key => $replace ) {

            if ( is_array( $replace ) )

               continue;

            $search = '/' . $delimiter . $key . $delimiter . '/';

            //print "<pre>" . print_r($search, true) . "</pre>"; die;

            $template = preg_replace( $search, $replace, $template );

         }

         return $template;

      }

   /*   

      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

?>