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