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