tbl_select.php3

   1 <?php
   2 /* $Id$ */
   3 // vim: expandtab sw=4 ts=4 sts=4:
   4
   5
   6 /**
   7  * Gets some core libraries
   8  */
   9 require('./libraries/grab_globals.lib.php3');
  10 require('./libraries/common.lib.php3');
  11 require('./libraries/relation.lib.php3'); // foreign keys
  12
  13 /**
  14  * Defines arrays of functions (should possibly be in config.inc.php3
  15  * so it can also be used in tbl_qbe.php3)
  16  *
  17  * LIKE works also on integers and dates so I added it in numfunctions
  18  */
  19 $numfunctions  = array('=', '>', '>=', '<', '<=', '!=', 'LIKE');
  20 $textfunctions = array('LIKE', '=', '!=');
  21
  22 /**
  23  * Not selection yet required -> displays the selection form
  24  */
  25 if (!isset($param) || $param[0] == '') {
  26     // Gets some core libraries
  27     include('./tbl_properties_common.php3');
  28     //$err_url   = 'tbl_select.php3' . $err_url;
  29     $url_query .= '&amp;goto=tbl_select.php3&amp;back=tbl_select.php3';
  30     include('./tbl_properties_table_info.php3');
  31
  32     if (!isset($goto)) {
  33         $goto = $cfg['DefaultTabTable'];
  34     }
  35     // Defines the url to return to in case of error in the next sql statement
  36     $err_url   = $goto . '?' . PMA_generate_common_url($db, $table);
  37
  38     // Gets the list and number of fields
  39     $local_query = 'SHOW FIELDS FROM ' . PMA_backquote($table) . ' FROM ' . PMA_backquote($db);
  40     $result      = @PMA_mysql_query($local_query);
  41     if (!$result) {
  42         PMA_mysqlDie('', $local_query, '', $err_url);
  43     }
  44     else {
  45         $fields_cnt        = mysql_num_rows($result);
  46         while ($row = PMA_mysql_fetch_array($result)) {
  47             $fields_list[] = $row['Field'];
  48             $type          = $row['Type'];
  49             // reformat mysql query output - staybyte - 9. June 2001
  50             $shorttype     = substr($type, 0, 3);
  51             if ($shorttype == 'set' || $shorttype == 'enu') {
  52                 $type      = eregi_replace(',', ', ', $type);
  53             } else {
  54                 $type          = eregi_replace('BINARY', '', $type);
  55                 $type          = eregi_replace('ZEROFILL', '', $type);
  56                 $type          = eregi_replace('UNSIGNED', '', $type);
  57             }
  58             if (empty($type)) {
  59                 $type      = '&nbsp;';
  60             }
  61             $fields_type[] = $type;
  62         } // end while
  63         mysql_free_result($result);
  64
  65         // <markus@noga.de>
  66         // retrieve keys into foreign fields, if any
  67         $cfgRelation = PMA_getRelationsParam();
  68         // check also foreigners even if relwork is FALSE (to get
  69         // foreign keys from innodb)
  70         //$foreigners  = ($cfgRelation['relwork'] ? PMA_getForeigners($db, $table) : FALSE);
  71         $foreigners  = PMA_getForeigners($db, $table);
  72         ?>
  73 <form method="post" action="tbl_select.php3">
  74     <?php echo PMA_generate_common_hidden_inputs($db, $table); ?>
  75     <input type="hidden" name="goto" value="<?php echo $goto; ?>" />
  76     <input type="hidden" name="back" value="tbl_select.php3" />
  77     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  78     <?php echo $strSelectFields; ?>&nbsp;:<br />
  79     &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
  80     <select name="param[]" size="<?php echo ($fields_cnt < 10) ? $fields_cnt : 10; ?>" multiple="multiple">
  81         <?php
  82         echo "\n";
  83         // Displays the list of the fields
  84         for ($i = 0 ; $i < $fields_cnt; $i++) {
  85             echo '        <option value="' . htmlspecialchars($fields_list[$i]) . '" selected="selected">' . htmlspecialchars($fields_list[$i]) . '</option>' . "\n";
  86         }
  87         ?>
  88     </select><br />
  89     <ul>
  90         <li>
  91             <div style="margin-bottom: 10px">
  92             <?php echo $strLimitNumRows . "\n"; ?>
  93             <input type="text" size="4" name="session_max_rows" value="<?php echo $cfg['MaxRows']; ?>" class="textfield" />
  94             </div>
  95         </li>
  96         <li>
  97             <?php echo $strAddSearchConditions; ?><br />
  98             <input type="text" name="where" class="textfield" />&nbsp;
  99             <?php echo PMA_showMySQLDocu('Reference', 'Functions') . "\n"; ?>
 100             <br /><br />
 101             <?php echo '<i>' . $strOr . '</i> ' . $strDoAQuery; ?><br />
 102             <table border="<?php echo $cfg['Border']; ?>">
 103             <tr>
 104                 <th><?php echo $strField; ?></th>
 105                 <th><?php echo $strType; ?></th>
 106                 <th><?php echo $strFunction; ?></th>
 107                 <th><?php echo $strValue; ?></th>
 108             </tr>
 109         <?php
 110         for ($i = 0; $i < $fields_cnt; $i++) {
 111             echo "\n";
 112             $bgcolor   = ($i % 2) ? $cfg['BgcolorOne'] : $cfg['BgcolorTwo'];
 113             ?>
 114             <tr>
 115                 <td bgcolor="<?php echo $bgcolor; ?>"><?php echo htmlspecialchars($fields_list[$i]); ?></td>
 116                 <td bgcolor="<?php echo $bgcolor; ?>"><?php echo $fields_type[$i]; ?></td>
 117                 <td bgcolor="<?php echo $bgcolor; ?>">
 118                     <select name="func[]">
 119             <?php
 120             reset($numfunctions);
 121             reset($textfunctions);
 122             if (eregi('char|blob|text|set|enum', $fields_type[$i])) {
 123                 while (list($k, $fc) = each($textfunctions)) {
 124                     echo "\n" . '                        '
 125                          . '<option value="' . htmlspecialchars($fc) . '">' . htmlspecialchars($fc) . '</option>';
 126                 } // end while
 127             } else {
 128                 while (list($k, $fc) = each($numfunctions)) {
 129                     echo "\n" . '                        '
 130                          . '<option value="' .  htmlspecialchars($fc) . '">' . htmlspecialchars($fc) . '</option>';
 131                 } // end while
 132             } // end if... else...
 133             echo "\n";
 134             ?>
 135                     </select>
 136                 </td>
 137                 <td bgcolor="<?php echo $bgcolor; ?>">
 138             <?php
 139             // <markus@noga.de>
 140             $field = $fields_list[$i];
 141
 142             include('./libraries/get_foreign.lib.php3');
 143
 144             echo "\n";
 145             // we got a bug report: in some cases, even if $disp is true,
 146             // there are no rows, so we add a fetch_array
 147             if ($foreigners && isset($foreigners[$field]) && isset($disp) && $disp && @PMA_mysql_fetch_array($disp)) {
 148                 // f o r e i g n    k e y s
 149                 echo '                    <select name="fields[]">' . "\n";
 150                 echo '                        <option value=""></option>' . "\n";
 151                 // go back to first row
 152                 mysql_data_seek($disp,0);
 153                 while ($relrow = @PMA_mysql_fetch_array($disp)) {
 154                     $key   = $relrow[$foreign_field];
 155                     $value = (($foreign_display != FALSE) ? '-' . htmlspecialchars($relrow[$foreign_display]) : '');
 156                     echo '                        <option value="' . htmlspecialchars($key) . '">'
 157                          . htmlspecialchars($key) . $value . '</option>' . "\n";
 158                 } // end while
 159                 echo '                    </select>' . "\n";
 160             } else if (substr($fields_type[$i], 0, 3)=='enu'){
 161                 // e n u m s
 162                 $enum_value=explode(", ",str_replace("'", "", substr($fields_type[$i], 5, -1)));
 163                 echo '                    <select name="fields[]">' . "\n";
 164                 echo '                        <option value=""></option>' . "\n";
 165                 for ($j=0; $j<count($enum_value);$j++){
 166                     echo '                        <option value="' . $enum_value[$j] . '">' . $enum_value[$j] . '</option>';
 167                 } // end for
 168                 echo '                    </select>' . "\n";
 169             } else {
 170                 // o t h e r   c a s e s
 171                 echo '                    <input type="text" name="fields[]" size="40" class="textfield" />' .  "\n";
 172             }
 173
 174             ?>
 175                     <input type="hidden" name="names[]" value="<?php echo htmlspecialchars($fields_list[$i]); ?>" />
 176                     <input type="hidden" name="types[]" value="<?php echo $fields_type[$i]; ?>" />
 177                 </td>
 178             </tr>
 179             <?php
 180         } // end for
 181         echo "\n";
 182         ?>
 183             </table><br />
 184         </li>
 185         <li>
 186             <?php echo $strDisplayOrder; ?><br />
 187             <select name="orderField" style="vertical-align: middle">
 188                 <option value="--nil--"></option>
 189         <?php
 190         echo "\n";
 191         for ($i = 0; $i < $fields_cnt; $i++) {
 192             echo '                ';
 193             echo '<option value="' . htmlspecialchars($fields_list[$i]) . '">' . htmlspecialchars($fields_list[$i]) . '</option>' . "\n";
 194         } // end for
 195         ?>
 196             </select>
 197             <input type="radio" name="order" value="ASC" checked="checked" />
 198             <?php echo $strAscending; ?>&nbsp;
 199             <input type="radio" name="order" value="DESC" />
 200             <?php echo $strDescending; ?><br /><br />
 201         </li>
 202     </ul>
 203
 204     &nbsp;&nbsp;&nbsp;&nbsp;
 205     <input type="hidden" name="max_number_of_fields" value="<?php echo $fields_cnt; ?>" />
 206     <input type="submit" name="submit" value="<?php echo $strGo; ?>" />
 207 </form>
 208         <?php
 209     } // end if
 210     echo "\n";
 211     include('./footer.inc.php3');
 212 }
 213
 214
 215 /**
 216  * Selection criteria have been submitted -> do the work
 217  */
 218 else {
 219     // Builds the query
 220
 221     $sql_query = 'SELECT ';
 222
 223     // if all fields were selected to display, we do a SELECT *
 224     // (more efficient and this helps prevent a problem in IE
 225     // if one of the rows is edited and we come back to the Select results)
 226
 227     if (count($param) == $max_number_of_fields) {
 228         $sql_query .= '* ';
 229     } else {
 230
 231         $sql_query .= PMA_backquote(urldecode($param[0]));
 232         $i         = 0;
 233         $c         = count($param);
 234         while ($i < $c) {
 235             if ($i > 0) {
 236                 $sql_query .= ',' . PMA_backquote(urldecode($param[$i]));
 237             }
 238             $i++;
 239         }
 240     } // end if
 241
 242     $sql_query .= ' FROM ' . PMA_backquote($table);
 243     // The where clause
 244     if ($where != '') {
 245         $sql_query .= ' WHERE ' . $where;
 246     }
 247     else {
 248         $sql_query .= ' WHERE 1';
 249         for ($i = 0; $i < count($fields); $i++) {
 250             if (!empty($fields) && $fields[$i] != '') {
 251                 if (eregi('char|blob|text|set|enum|date|time|year', $types[$i])) {
 252                     $quot     = '\'';
 253                 } else {
 254                     $quot     = '';
 255                 }
 256                 if (strtoupper($fields[$i]) == 'NULL' || strtoupper($fields[$i]) == 'NOT NULL') {
 257                     $quot     = '';
 258                     $func[$i] = 'IS';
 259                 }
 260                 $sql_query    .= ' AND ' . PMA_backquote(urldecode($names[$i])) . " $func[$i] $quot$fields[$i]$quot";
 261             } // end if
 262         } // end for
 263     } // end if
 264
 265     if ($orderField != '--nil--') {
 266         $sql_query .= ' ORDER BY ' . PMA_backquote(urldecode($orderField)) . ' ' . $order;
 267     } // end if
 268
 269     include('./sql.php3');
 270 }
 271
 272 ?>