tbl_select.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Handles table search tab
   5  *
   6  * display table search form, create SQL query from form data
   7  * and include sql.php to execute it
   8  *
   9  * @todo display search form again if no results from previous search
  10  * @package phpMyAdmin
  11  */
  12
  13 /**
  14  * Gets some core libraries
  15  */
  16 require_once './libraries/common.inc.php';
  17 require_once './libraries/mysql_charsets.lib.php';
  18 require_once './libraries/tbl_select.lib.php';
  19
  20 $GLOBALS['js_include'][] = 'makegrid.js';
  21 $GLOBALS['js_include'][] = 'sql.js';
  22 $GLOBALS['js_include'][] = 'tbl_select.js';
  23 $GLOBALS['js_include'][] = 'tbl_change.js';
  24 $GLOBALS['js_include'][] = 'jquery/jquery-ui-1.8.custom.js';
  25 $GLOBALS['js_include'][] = 'jquery/timepicker.js';
  26
  27 $titles['Browse'] = PMA_tbl_setTitle($GLOBALS['cfg']['PropertiesIconic'], $pmaThemeImage);
  28
  29 /**
  30  * Not selection yet required -> displays the selection form
  31  */
  32 if (! isset($param) || $param[0] == '') {
  33     // Gets some core libraries
  34     require_once './libraries/tbl_common.php';
  35     //$err_url   = 'tbl_select.php' . $err_url;
  36     $url_query .= '&amp;goto=tbl_select.php&amp;back=tbl_select.php';
  37
  38     /**
  39      * Gets tables informations
  40      */
  41     require_once './libraries/tbl_info.inc.php';
  42
  43     /**
  44      * Displays top menu links
  45      */
  46     require_once './libraries/tbl_links.inc.php';
  47
  48     if (! isset($goto)) {
  49         $goto = $GLOBALS['cfg']['DefaultTabTable'];
  50     }
  51     // Defines the url to return to in case of error in the next sql statement
  52     $err_url   = $goto . '?' . PMA_generate_common_url($db, $table);
  53
  54     // Gets the list and number of fields
  55
  56     list($fields_list, $fields_type, $fields_collation, $fields_null) = PMA_tbl_getFields($table,$db);
  57     $fields_cnt = count($fields_list);
  58
  59     // retrieve keys into foreign fields, if any
  60     // check also foreigners even if relwork is FALSE (to get
  61     // foreign keys from innodb)
  62     $foreigners = PMA_getForeigners($db, $table);
  63     ?>
  64
  65 <fieldset id="fieldset_subtab">
  66 <?php
  67 $url_params = array();
  68 $url_params['db']    = $db;
  69 $url_params['table'] = $table;
  70
  71 echo PMA_generate_html_tabs(PMA_tbl_getSubTabs(), $url_params);
  72
  73 ?>
  74
  75         <form method="post" action="tbl_select.php" name="insertForm" id="tbl_search_form" <?php echo ($GLOBALS['cfg']['AjaxEnable'] ? ' class="ajax"' : ''); ?>>
  76 <?php echo PMA_generate_common_hidden_inputs($db, $table); ?>
  77 <input type="hidden" name="goto" value="<?php echo $goto; ?>" />
  78 <input type="hidden" name="back" value="tbl_select.php" />
  79
  80 <fieldset id="fieldset_table_search">
  81
  82 <fieldset id="fieldset_table_qbe">
  83     <legend><?php echo __('Do a "query by example" (wildcard: "%")') ?></legend>
  84     <table class="data">
  85     <?php echo PMA_tbl_setTableHeader(); ?>
  86     <tbody>
  87     <?php
  88     $odd_row = true;
  89
  90     for ($i = 0; $i < $fields_cnt; $i++) {
  91         ?>
  92         <tr class="noclick <?php echo $odd_row ? 'odd' : 'even'; $odd_row = ! $odd_row; ?>">
  93             <th><?php echo htmlspecialchars($fields_list[$i]); ?></th>
  94             <td><?php echo $fields_type[$i]; ?></td>
  95             <td><?php echo $fields_collation[$i]; ?></td>
  96             <td><select name="func[]">
  97         <?php
  98         if (strncasecmp($fields_type[$i], 'enum', 4) == 0) {
  99             foreach ($GLOBALS['cfg']['EnumOperators'] as $fc) {
 100                 echo "\n" . '                        '
 101                    . '<option value="' . htmlspecialchars($fc) . '">'
 102                    . htmlspecialchars($fc) . '</option>';
 103             }
 104         } elseif (preg_match('@char|blob|text|set@i', $fields_type[$i])) {
 105             foreach ($GLOBALS['cfg']['TextOperators'] as $fc) {
 106             echo "\n" . '                        '
 107                . '<option value="' . htmlspecialchars($fc) . '">'
 108                . htmlspecialchars($fc) . '</option>';
 109             }
 110         } else {
 111             foreach ($GLOBALS['cfg']['NumOperators'] as $fc) {
 112                 echo "\n" . '                        '
 113                    . '<option value="' .  htmlspecialchars($fc) . '">'
 114                    . htmlspecialchars($fc) . '</option>';
 115             }
 116         } // end if... else...
 117         if ($fields_null[$i]) {
 118             foreach ($GLOBALS['cfg']['NullOperators'] as $fc) {
 119                 echo "\n" . '                        '
 120                    . '<option value="' .  htmlspecialchars($fc) . '">'
 121                    . htmlspecialchars($fc) . '</option>';
 122             }
 123         }
 124         ?>
 125
 126                 </select>
 127             </td>
 128             <td>
 129         <?php
 130         $field = $fields_list[$i];
 131
 132         $foreignData = PMA_getForeignData($foreigners, $field, false, '', '');
 133
 134         echo PMA_getForeignFields_Values($foreigners, $foreignData, $field, $fields_type, $i, $db, $table, $titles,$GLOBALS['cfg']['ForeignKeyMaxLimit'], '' );
 135
 136         ?>
 137             <input type="hidden" name="names[<?php echo $i; ?>]"
 138                 value="<?php echo htmlspecialchars($fields_list[$i]); ?>" />
 139             <input type="hidden" name="types[<?php echo $i; ?>]"
 140                 value="<?php echo $fields_type[$i]; ?>" />
 141             <input type="hidden" name="collations[<?php echo $i; ?>]"
 142                 value="<?php echo $fields_collation[$i]; ?>" />
 143         </td>
 144     </tr>
 145         <?php
 146     } // end for
 147     ?>
 148     </tbody>
 149     </table>
 150 </fieldset>
 151 <?php
 152     PMA_generate_slider_effect('searchoptions', __('Options'));
 153 ?>
 154 <fieldset id="fieldset_select_fields">
 155     <legend><?php echo __('Select columns (at least one):'); ?></legend>
 156     <select name="param[]" size="<?php echo min($fields_cnt, 10); ?>"
 157         multiple="multiple">
 158     <?php
 159     // Displays the list of the fields
 160     foreach ($fields_list as $each_field) {
 161         echo '        '
 162             .'<option value="' . htmlspecialchars($each_field) . '"'
 163             .' selected="selected">' . htmlspecialchars($each_field)
 164             .'</option>' . "\n";
 165     }
 166     ?>
 167     </select>
 168     <input type="checkbox" name="distinct" value="DISTINCT" id="oDistinct" />
 169     <label for="oDistinct">DISTINCT</label>
 170 </fieldset>
 171
 172 <fieldset id="fieldset_search_conditions">
 173     <legend><?php echo '<em>' . __('Or') . '</em> ' . __('Add search conditions (body of the "where" clause):'); ?></legend>
 174 <?php echo PMA_showMySQLDocu('SQL-Syntax', 'Functions'); ?>
 175
 176 <input type="text" name="where" class="textfield" size="64" />
 177 </fieldset>
 178
 179 <fieldset id="fieldset_limit_rows">
 180     <legend><?php echo __('Number of rows per page'); ?></legend>
 181     <input type="text" size="4" name="session_max_rows"
 182         value="<?php echo $GLOBALS['cfg']['MaxRows']; ?>" class="textfield" />
 183 </fieldset>
 184
 185 <fieldset id="fieldset_display_order">
 186     <legend><?php echo __('Display order:'); ?></legend>
 187     <select name="orderField">
 188         <option value="--nil--"></option>
 189     <?php
 190     foreach ($fields_list as $each_field) {
 191         echo '        '
 192             .'<option value="' . htmlspecialchars($each_field) . '">'
 193             .htmlspecialchars($each_field) . '</option>' . "\n";
 194     } // end for
 195     ?>
 196     </select>
 197 <?php
 198     $choices = array(
 199         'ASC'  => __('Ascending'),
 200         'DESC' => __('Descending')
 201     );
 202     PMA_display_html_radio('order', $choices, 'ASC', false, true, "formelement");
 203     unset($choices);
 204 ?>
 205 </fieldset>
 206 <br style="clear: both;"/>
 207 </div>
 208 </fieldset>
 209 <fieldset class="tblFooters">
 210     <input type="hidden" name="max_number_of_fields"
 211         value="<?php echo $fields_cnt; ?>" />
 212     <input type="submit" name="submit" value="<?php echo __('Go'); ?>" />
 213 </fieldset>
 214 </form>
 215 <div id="sqlqueryresults"></div>
 216     <?php
 217     require './libraries/footer.inc.php';
 218 ?>
 219
 220 </fieldset>
 221
 222 <?php
 223 }
 224
 225
 226
 227 /**
 228  * Selection criteria have been submitted -> do the work
 229  */
 230 else {
 231     echo "ZZ";
 232     // Builds the query
 233
 234     $sql_query = 'SELECT ' . (isset($distinct) ? 'DISTINCT ' : '');
 235
 236     // if all fields were selected to display, we do a SELECT *
 237     // (more efficient and this helps prevent a problem in IE
 238     // if one of the rows is edited and we come back to the Select results)
 239
 240     if (count($param) == $max_number_of_fields) {
 241         $sql_query .= '* ';
 242     } else {
 243         $param = PMA_backquote($param);
 244         $sql_query .= implode(', ', $param);
 245     } // end if
 246
 247     // avoid a loop, for example when $cfg['DefaultTabTable'] is set
 248     // to 'tbl_select.php'
 249     unset($param);
 250
 251     $sql_query .= ' FROM ' . PMA_backquote($table);
 252
 253     // The where clause
 254     if (trim($where) != '') {
 255         $sql_query .= ' WHERE ' . $where;
 256     } else {
 257         $w = $charsets = array();
 258         $cnt_func = count($func);
 259         reset($func);
 260         while (list($i, $func_type) = each($func)) {
 261
 262             list($charsets[$i]) = explode('_', $collations[$i]);
 263             $unaryFlag =  (isset($GLOBALS['cfg']['UnaryOperators'][$func_type]) && $GLOBALS['cfg']['UnaryOperators'][$func_type] == 1) ? true : false;
 264             $whereClause = PMA_tbl_search_getWhereClause($fields[$i],$names[$i], $types[$i], $collations[$i], $func_type, $unaryFlag);
 265             if($whereClause)
 266                 $w[] = $whereClause;
 267
 268         } // end for
 269         //print_r($w);
 270         if ($w) {
 271             $sql_query .= ' WHERE ' . implode(' AND ', $w);
 272         }
 273     } // end if
 274
 275     if ($orderField != '--nil--') {
 276         $sql_query .= ' ORDER BY ' . PMA_backquote($orderField) . ' ' . $order;
 277     } // end if
 278     require './sql.php';
 279 }
 280
 281 ?>