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