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