Subversion Repositories phpLibraryV2

Rev

Rev 45 | Show entire file | Ignore whitespace | Details | Blame | Last modification | View Log | RSS feed

Rev 45 Rev 47
Line 5... Line 5...
5 
   */
 5 
   */
6
6
7 
   require_once( 'DBQuery.class.php' );
 7 
   require_once( 'DBQuery.class.php' );
8
8
9 
   class DBQueryHTML extends DBQuery {
 9 
   class DBQueryHTML extends DBQuery {
- 
 
 10
- 
 
 11 
      protected $htmlQuoteChar = '"';
10
12
11 
      /*
 13 
      /*
12 
         Function takes an SQL statement and converts it to an HTML table.
 14 
         Function takes an SQL statement and converts it to an HTML table.
13 
         Return Value: HTML table representation of the query
 15 
         Return Value: HTML table representation of the query
14 
         Parameters:
 16 
         Parameters:
Line 29... Line 31...
29 
               sensitive). Also, beware of items like select zip,concat(state,', ', city) from zip_codes. It is
 31 
               sensitive). Also, beware of items like select zip,concat(state,', ', city) from zip_codes. It is
30 
               much better to rewrite that as select zip,concat(state,', ', city) mytown from zip_codes.
 32 
               much better to rewrite that as select zip,concat(state,', ', city) mytown from zip_codes.
31
33
32 
               If you don't know what all that means, get a book on SQL
 34 
               If you don't know what all that means, get a book on SQL
33 
      */
 35 
      */
34 
      function queryToTable ( $format = '', $makeTableDef = true, $append='', $headers = '' ) {
 36 
      function queryToTable ( $format = array(), $makeTableDef = true, $append='', $headers = '' ) {
35 
         print "\n\n<pre>$this->query</pre>\n\n";
 37 
         // print "\n\n<pre>$this->query</pre>\n\n";
36 
         $tdTextDefinition = '<td valign=' . HTML_QUOTE_CHAR . 'top' . HTML_QUOTE_CHAR . '>';
 38 
         $tdTextDefinition = '<td valign=' . $this->htmlQuoteChar . 'top' . $this->htmlQuoteChar . '>';
37 
         $tdNumberDefinition = '<td valign=' . HTML_QUOTE_CHAR .'top' . HTML_QUOTE_CHAR . 'align=' . HTML_QUOTE_CHAR . 'right' . HTML_QUOTE_CHAR . '>';
 39 
         $tdNumberDefinition = '<td valign=' . $this->htmlQuoteChar .'top' . $this->htmlQuoteChar . 'align=' . $this->htmlQuoteChar . 'right' . $this->htmlQuoteChar . '>';
38 
         $html = '';
 40 
         $html = '';
39 
         $rows = array();
 41 
         $rows = array();
40 
         $save = $this->useAssociativeArray;
 42 
         $save = $this->returnType;
41 
         $this->useAssociativeArray = false; // temporarily turn off associative arrays
 43 
         $this->returnType = MYSQLI_ASSOC; // temporarily turn off associative arrays
- 
 
 44 
         $columnNames = array();
42 
         $this->run();
 45 
         $this->run();
43 
         if ( $this->rowsAffected ) { // we returned at least one row
 46 
         if ( $this->rowsAffected ) { // we returned at least one row
- 
 
 47
44 
            // Build the searchFor array for $append
 48 
            //print "<pre>Found " . $this->rowsAffected . "\n</pre>";
45 
            $searchFor = array();
 49 
            // get the column names; we'll use them later
46 
            foreach ( $this->columnMetaData as $field ) {
 50 
            foreach ( $this->columnMetaData as $field ) {
47 
               $searchFor[] = '%' . $field['name'] . '%';
 51 
               $columnNames[] = $field['name'];
48 
            }
 52 
            }
- 
 
 53 
 
- 
 
 54 
            // Build the searchFor array for $append
- 
 
 55 
            $searchFor = $columnNames;
- 
 
 56 
            array_walk( $searchFor, function ( &$value, $key ) { $value = '%' . $value . '%'; } );
- 
 
 57 
            // print '<pre>SearchFor ' . print_r( $searchFor, true ) . '</pre>'; die;
- 
 
 58 
            //print "<pre>" . print_r($this->columnMetaData,true) . '</pre>'; die;
49 
            /* special condition where only one row is returned. In that case, $this->returnData is not an array
 59 
            /* special condition where only one row is returned. In that case, $this->returnData is not an array
50 
            of associations, but a simple association. In this case, we need to convert it
 60 
            of associations, but a simple association. In this case, we need to convert it
51 
            */
 61 
            */
52 
            if (count($this->returnData[0]) == 1) { // convert from association to single row array of associations
 62 
            if (count($this->returnData[0]) == 1) { // convert from association to single row array of associations
53 
               $temp = array();
 63 
               $temp = array();
Line 75... Line 85...
75 
                     $rows[$row] .= $tdTextDefinition . str_replace ( $searchFor, $this->returnData[$row], $append ) . '</td>';
 85 
                     $rows[$row] .= $tdTextDefinition . str_replace ( $searchFor, $this->returnData[$row], $append ) . '</td>';
76 
                  }
 86 
                  }
77 
               } // for $row
 87 
               } // for $row
78 
            } else { // no formatting, so we just slam the stuff together
 88 
            } else { // no formatting, so we just slam the stuff together
79 
               for ( $row = 0; $row < count($this->returnData); $row++) {
 89 
               for ( $row = 0; $row < count($this->returnData); $row++) {
- 
 
 90 
                  $temp = array();
- 
 
 91 
                  foreach ( $columnNames as $col ) {
- 
 
 92 
                     $temp[] = $this->returnData[$row][$col];
- 
 
 93 
                  }
80 
                  $currentValue = $tdTextDefinition . implode( '</td>' . $tdTextDefinition, $this->returnData[$row]) . '</td>';
 94 
                  $currentValue = $tdTextDefinition . implode( '</td>' . $tdTextDefinition, $temp) . '</td>';
81 
                  if (strlen($append) > 0) { // see explaination in if part of this structure
 95 
                  if (strlen($append) > 0) { // see explaination in if part of this structure
82 
                     $currentValue  .= $tdTextDefinition . str_replace ( $searchFor, $this->returnData[$row], $append ) . '</td>';
 96 
                     $currentValue  .= $tdTextDefinition . str_replace ( $searchFor, $this->returnData[$row], $append ) . '</td>';
83 
                  }
 97 
                  }
84 
                  $rows[] = $currentValue;
 98 
                  $rows[] = $currentValue;
85 
               }
 99 
               }
Line 88... Line 102...
88 
            $html .= '<tr>';
 102 
            $html .= '<tr>';
89 
            if ( $headers ) { // user has passed in some header fields for us to use
 103 
            if ( $headers ) { // user has passed in some header fields for us to use
90 
               $html .= '<th>' . implode( '</th><th>',  $headers ) . '</th>';
 104 
               $html .= '<th>' . implode( '</th><th>',  $headers ) . '</th>';
91 
            } else { // no headers, so we just get them from the returned column names
 105 
            } else { // no headers, so we just get them from the returned column names
92 
               foreach ( $this->columnMetaData as $field ) {
 106 
               foreach ( $this->columnMetaData as $field ) {
93 
                  $html .= '<th>' . $field['name'] . '</th>';
 107 
                  $html .= '<th>' . ((array)$field)['name'] . '</th>';
94 
               }
 108 
               }
95 
            } // if headers .. else
 109 
            } // if headers .. else
96 
            $html .= '</tr>';
 110 
            $html .= '</tr>';
97 
            // we now have all the info, so let's make it into a table
 111 
            // we now have all the info, so let's make it into a table
98 
            $html .= '<tr>' . implode('</tr><tr>', $rows ) . '</tr>';
 112 
            $html .= '<tr>' . implode('</tr><tr>', $rows ) . '</tr>';
99 
         } else {
 113 
         } else {
100 
            $html = '<tr><td>No rows returned by the query</td></tr>';
 114 
            $html = '<tr><td>No rows returned by the query</td></tr>';
101 
         }
 115 
         }
102 
         if ( $makeTableDef ) { // they want the <table> def, so let's give it to them
 116 
         if ( $makeTableDef ) { // they want the <table> def, so let's give it to them
103 
            $html = '<table border=' . HTML_QUOTE_CHAR . '1' . HTML_QUOTE_CHAR . '>' . $html . '</table>';
 117 
            $html = '<table border=' . $this->htmlQuoteChar . '1' . $this->htmlQuoteChar . '>' . $html . '</table>';
104 
         } // if ( $makeTableDef
 118 
         } // if ( $makeTableDef
105
119
106 
         $this->useAssociativeArray = $save;
 120 
         $this->returnType = $save;
107 
         //return '<pre>' . print_r ($this,true) .  '</pre>';
 121 
         //return '<pre>' . print_r ($this,true) .  '</pre>';
108 
         return $html;
 122 
         return $html;
109 
      }
 123 
      }
110
124
111 
      /*
 125 
      /*
Line 136... Line 150...
136
150
137 
         Note, because it is an associative array, duplicates of the first column will only contain
 151 
         Note, because it is an associative array, duplicates of the first column will only contain
138 
         the last value.
 152 
         the last value.
139 
      */
 153 
      */
140
154
141 
      function ColumnColumnArray () {
 155 
      function ColumnColumnArray ( $query = null ) {
- 
 
 156 
         if ( isset( $query) ) {
- 
 
 157 
            $this->parameters['query'] = $query;
- 
 
 158 
         }
142 
         $returnValue = array();
 159 
         $returnValue = array();
143 
         $save = $this->useAssociativeArray;
 160 
         $save = $this->useAssociativeArray;
144 
         $useAssociativeArray = false; // temporarily turn off associative arrays
 161 
         $useAssociativeArray = false; // temporarily turn off associative arrays
145 
         $this->run();
 162 
         $this->run();
146 
         if ( $this->rowsAffected ) { // we returned at least one row
 163 
         if ( $this->rowsAffected ) { // we returned at least one row
- 
 
 164 
            //print '<pre>' . print_r($this->parameters, true) . '</pre>'; die;
147 
            foreach ( $info as $key => $value ) { // only thing I know to do is iterate
 165 
            foreach ( $this->returnData as $key => $value ) { // only thing I know to do is iterate
148 
               $returnValue[$this->returnData[0]] = $this->returnData[1];
 166 
               $returnValue[$value[0]] = $value[1];
149 
            }
 167 
            }
150 
         } // if
 168 
         } // if
151 
         $useAssociativeArray = $save;
 169 
         $useAssociativeArray = $save;
152 
         return $returnValue;
 170 
         return $returnValue;
153 
      }
 171 
      }