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