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