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
  19 $GLOBALS['js_include'][] = 'makegrid.js';
  20 $GLOBALS['js_include'][] = 'sql.js';
  21 $GLOBALS['js_include'][] = 'tbl_select.js';
  22 $GLOBALS['js_include'][] = 'tbl_change.js';
  23 $GLOBALS['js_include'][] = 'jquery/jquery-ui-1.8.custom.js';
  24 $GLOBALS['js_include'][] = 'jquery/timepicker.js';
  25 if ($GLOBALS['cfg']['PropertiesIconic'] == true) {
  26     $titles['Browse'] =
  27         '<img class="icon" width="16" height="16" src="' . $pmaThemeImage
  28         .'b_browse.png" alt="' . __('Browse foreign values') . '" title="'
  29         . __('Browse foreign values') . '" />';
  30
  31     if ($GLOBALS['cfg']['PropertiesIconic'] === 'both') {
  32         $titles['Browse'] .= __('Browse foreign values');
  33     }
  34 } else {
  35     $titles['Browse'] = __('Browse foreign values');
  36 }
  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     $result     = PMA_DBI_query('SHOW FULL FIELDS FROM ' . PMA_backquote($table) . ' FROM ' . PMA_backquote($db) . ';', null, PMA_DBI_QUERY_STORE);
  66     $fields_cnt = PMA_DBI_num_rows($result);
  67     $fields_list = $fields_null = $fields_type = $fields_collation = array();
  68     $geom_column_present = false;
  69     while ($row = PMA_DBI_fetch_assoc($result)) {
  70         $fields_list[] = $row['Field'];
  71         $type          = $row['Type'];
  72         // check whether table contains geometric columns
  73         if (in_array($type, $geom_types)) {
  74             $geom_column_present = true;
  75         }
  76         // reformat mysql query output
  77         if (strncasecmp($type, 'set', 3) == 0
  78             || strncasecmp($type, 'enum', 4) == 0) {
  79             $type = str_replace(',', ', ', $type);
  80         } else {
  81
  82             // strip the "BINARY" attribute, except if we find "BINARY(" because
  83             // this would be a BINARY or VARBINARY field type
  84             if (!preg_match('@BINARY[\(]@i', $type)) {
  85                 $type = preg_replace('@BINARY@i', '', $type);
  86             }
  87             $type = preg_replace('@ZEROFILL@i', '', $type);
  88             $type = preg_replace('@UNSIGNED@i', '', $type);
  89
  90             $type = strtolower($type);
  91         }
  92         if (empty($type)) {
  93             $type = '&nbsp;';
  94         }
  95         $fields_null[] = $row['Null'];
  96         $fields_type[] = $type;
  97         $fields_collation[] = !empty($row['Collation']) && $row['Collation'] != 'NULL'
  98                           ? $row['Collation']
  99                           : '';
 100     } // end while
 101     PMA_DBI_free_result($result);
 102     unset($result, $type);
 103
 104     // retrieve keys into foreign fields, if any
 105     // check also foreigners even if relwork is FALSE (to get
 106     // foreign keys from innodb)
 107     $foreigners = PMA_getForeigners($db, $table);
 108     ?>
 109         <form method="post" action="tbl_select.php" name="insertForm" id="tbl_search_form" <?php echo ($GLOBALS['cfg']['AjaxEnable'] ? ' class="ajax"' : ''); ?>>
 110 <?php echo PMA_generate_common_hidden_inputs($db, $table); ?>
 111 <input type="hidden" name="goto" value="<?php echo $goto; ?>" />
 112 <input type="hidden" name="back" value="tbl_select.php" />
 113
 114 <fieldset id="fieldset_table_search">
 115
 116 <fieldset id="fieldset_table_qbe">
 117     <legend><?php echo __('Do a "query by example" (wildcard: "%")') ?></legend>
 118     <table class="data">
 119     <thead>
 120     <tr><?php
 121         // Display the Function column only if there is alteast one geomety colum
 122         if ($geom_column_present) {
 123             echo('<th>'); echo __('Function');  echo('</th>');
 124         }
 125         ?>
 126         <th><?php echo __('Column'); ?></th>
 127         <th><?php echo __('Type'); ?></th>
 128         <th><?php echo __('Collation'); ?></th>
 129         <th><?php echo __('Operator'); ?></th>
 130         <th><?php echo __('Value'); ?></th>
 131     </tr>
 132     </thead>
 133     <tbody>
 134     <?php
 135     $odd_row = true;
 136
 137     for ($i = 0; $i < $fields_cnt; $i++) {
 138         ?>
 139         <tr class="noclick <?php echo $odd_row ? 'odd' : 'even'; $odd_row = ! $odd_row; ?>">
 140             <?php
 141             // if 'Function' column is present
 142             if ($geom_column_present) {
 143                 echo('<td>');
 144                 // if a geometry column
 145                 if (in_array($fields_type[$i], $geom_types)) {
 146                     echo('<select name="geom_func['. $i .']">');
 147                         // get the relevant list of functions
 148                         $funcs = PMA_getGISFunctions($fields_type[$i], false, true);
 149                         foreach ($funcs as $func_name => $func) {
 150                             $name =  isset($func['display']) ? $func['display'] : $func_name;
 151                             echo('<option value="' . htmlspecialchars($name) . '">'
 152                                 . htmlspecialchars($name) . '</option>');
 153                         }
 154                     echo('</select>');
 155                 } else {
 156                     echo('&nbsp;');
 157                 }
 158                 echo('</td>');
 159             }
 160             ?>
 161             <th><?php echo htmlspecialchars($fields_list[$i]); ?></th>
 162             <td><?php echo $fields_type[$i]; ?></td>
 163             <td><?php echo $fields_collation[$i]; ?></td>
 164             <td><select name="func[]">
 165         <?php
 166         // determine valid operators
 167         if (strncasecmp($fields_type[$i], 'enum', 4) == 0) {
 168             // enum operators
 169             $operators = array(
 170                 '=',
 171                 '!=',
 172             );
 173         } elseif (preg_match('@char|blob|text|set@i', $fields_type[$i])) {
 174             // text operators
 175             $operators = array(
 176                'LIKE',
 177                'LIKE %...%',
 178                'NOT LIKE',
 179                '=',
 180                '!=',
 181                'REGEXP',
 182                'REGEXP ^...$',
 183                'NOT REGEXP',
 184                "= ''",
 185                "!= ''",
 186                'IN (...)',
 187                'NOT IN (...)',
 188                'BETWEEN',
 189                'NOT BETWEEN',
 190             );
 191         } else {
 192             // numeric operators
 193             $operators = array(
 194                '=',
 195                '>',
 196                '>=',
 197                '<',
 198                '<=',
 199                '!=',
 200                'LIKE',
 201                'NOT LIKE',
 202                'IN (...)',
 203                'NOT IN (...)',
 204                'BETWEEN',
 205                'NOT BETWEEN',
 206             );
 207         } // end if... else...
 208
 209         // if field can be NULL, add IS NULL and IS NOT NULL
 210         if ($fields_null[$i]) {
 211             $operators[] = 'IS NULL';
 212             $operators[] = 'IS NOT NULL';
 213         }
 214         foreach ($operators as $op) {
 215             echo "\n" . '                        '
 216                . '<option value="' .  htmlspecialchars($op) . '">' . htmlspecialchars($op) . '</option>';
 217         }
 218         ?>
 219
 220                 </select>
 221             </td>
 222             <td>
 223         <?php
 224         $field = $fields_list[$i];
 225
 226         $foreignData = PMA_getForeignData($foreigners, $field, false, '', '');
 227
 228         if ($foreigners && isset($foreigners[$field]) && is_array($foreignData['disp_row'])) {
 229             // f o r e i g n    k e y s
 230             echo '            <select name="fields[' . $i . ']">' . "\n";
 231             // go back to first row
 232
 233             // here, the 4th parameter is empty because there is no current
 234             // value of data for the dropdown (the search page initial values
 235             // are displayed empty)
 236             echo PMA_foreignDropdown($foreignData['disp_row'],
 237                 $foreignData['foreign_field'],
 238                 $foreignData['foreign_display'],
 239                 '', $GLOBALS['cfg']['ForeignKeyMaxLimit']);
 240             echo '            </select>' . "\n";
 241         } elseif ($foreignData['foreign_link'] == true) {
 242             ?>
 243             <input type="text" name="fields[<?php echo $i; ?>]"
 244                 id="field_<?php echo md5($field); ?>[<?php echo $i; ?>]"
 245                 class="textfield" />
 246             <script type="text/javascript">
 247             // <![CDATA[
 248                 document.writeln('<a target="_blank" onclick="window.open(this.href, \'foreigners\', \'width=640,height=240,scrollbars=yes\'); return false" href="browse_foreigners.php?<?php echo PMA_generate_common_url($db, $table); ?>&amp;field=<?php echo urlencode($field); ?>&amp;fieldkey=<?php echo $i; ?>"><?php echo str_replace("'", "\'", $titles['Browse']); ?></a>');
 249             // ]]>
 250             </script>
 251             <?php
 252         } elseif (strncasecmp($fields_type[$i], 'enum', 4) == 0) {
 253             // e n u m s
 254             $enum_value=explode(', ', str_replace("'", '', substr($fields_type[$i], 5, -1)));
 255             $cnt_enum_value = count($enum_value);
 256             echo '            <select name="fields[' . $i . '][]"'
 257                 .' multiple="multiple" size="' . min(3, $cnt_enum_value) . '">' . "\n";
 258             for ($j = 0; $j < $cnt_enum_value; $j++) {
 259                 echo '                <option value="' . $enum_value[$j] . '">'
 260                     . $enum_value[$j] . '</option>';
 261             } // end for
 262             echo '            </select>' . "\n";
 263         } else {
 264             // o t h e r   c a s e s
 265             $the_class = 'textfield';
 266             $type = $fields_type[$i];
 267             if ($type == 'date') {
 268                 $the_class .= ' datefield';
 269             } elseif ($type == 'datetime' || substr($type, 0, 9) == 'timestamp') {
 270                 $the_class .= ' datetimefield';
 271             }
 272             echo '            <input type="text" name="fields[' . $i . ']"'
 273                 .' size="40" class="' . $the_class . '" id="field_' . $i . '" />' .  "\n";
 274         };
 275         ?>
 276             <input type="hidden" name="names[<?php echo $i; ?>]"
 277                 value="<?php echo htmlspecialchars($fields_list[$i]); ?>" />
 278             <input type="hidden" name="types[<?php echo $i; ?>]"
 279                 value="<?php echo $fields_type[$i]; ?>" />
 280             <input type="hidden" name="collations[<?php echo $i; ?>]"
 281                 value="<?php echo $fields_collation[$i]; ?>" />
 282         </td>
 283     </tr>
 284         <?php
 285     } // end for
 286     ?>
 287     </tbody>
 288     </table>
 289 </fieldset>
 290 <?php
 291     PMA_generate_slider_effect('searchoptions', __('Options'));
 292 ?>
 293 <fieldset id="fieldset_select_fields">
 294     <legend><?php echo __('Select columns (at least one):'); ?></legend>
 295     <select name="param[]" size="<?php echo min($fields_cnt, 10); ?>"
 296         multiple="multiple">
 297     <?php
 298     // Displays the list of the fields
 299     foreach ($fields_list as $each_field) {
 300         echo '        '
 301             .'<option value="' . htmlspecialchars($each_field) . '"'
 302             .' selected="selected">' . htmlspecialchars($each_field)
 303             .'</option>' . "\n";
 304     }
 305     ?>
 306     </select>
 307     <input type="checkbox" name="distinct" value="DISTINCT" id="oDistinct" />
 308     <label for="oDistinct">DISTINCT</label>
 309 </fieldset>
 310
 311 <fieldset id="fieldset_search_conditions">
 312     <legend><?php echo '<em>' . __('Or') . '</em> ' . __('Add search conditions (body of the "where" clause):'); ?></legend>
 313 <?php echo PMA_showMySQLDocu('SQL-Syntax', 'Functions'); ?>
 314
 315 <input type="text" name="where" class="textfield" size="64" />
 316 </fieldset>
 317
 318 <fieldset id="fieldset_limit_rows">
 319     <legend><?php echo __('Number of rows per page'); ?></legend>
 320     <input type="text" size="4" name="session_max_rows"
 321         value="<?php echo $GLOBALS['cfg']['MaxRows']; ?>" class="textfield" />
 322 </fieldset>
 323
 324 <fieldset id="fieldset_display_order">
 325     <legend><?php echo __('Display order:'); ?></legend>
 326     <select name="orderField">
 327         <option value="--nil--"></option>
 328     <?php
 329     foreach ($fields_list as $each_field) {
 330         echo '        '
 331             .'<option value="' . htmlspecialchars($each_field) . '">'
 332             .htmlspecialchars($each_field) . '</option>' . "\n";
 333     } // end for
 334     ?>
 335     </select>
 336 <?php
 337     $choices = array(
 338         'ASC'  => __('Ascending'),
 339         'DESC' => __('Descending')
 340     );
 341     PMA_display_html_radio('order', $choices, 'ASC', false, true, "formelement");
 342     unset($choices);
 343 ?>
 344 </fieldset>
 345 <br style="clear: both;"/>
 346 </div>
 347 </fieldset>
 348 <fieldset class="tblFooters">
 349     <input type="hidden" name="max_number_of_fields"
 350         value="<?php echo $fields_cnt; ?>" />
 351     <input type="submit" name="submit" value="<?php echo __('Go'); ?>" />
 352 </fieldset>
 353 </form>
 354 <div id="sqlqueryresults"></div>
 355     <?php
 356     require './libraries/footer.inc.php';
 357 }
 358
 359
 360 /**
 361  * Selection criteria have been submitted -> do the work
 362  */
 363 else {
 364     // Builds the query
 365
 366     $sql_query = 'SELECT ' . (isset($distinct) ? 'DISTINCT ' : '');
 367
 368     // if all fields were selected to display, we do a SELECT *
 369     // (more efficient and this helps prevent a problem in IE
 370     // if one of the rows is edited and we come back to the Select results)
 371
 372     if (count($param) == $max_number_of_fields) {
 373         $sql_query .= '* ';
 374     } else {
 375         $param = PMA_backquote($param);
 376         $sql_query .= implode(', ', $param);
 377     } // end if
 378
 379     // avoid a loop, for example when $cfg['DefaultTabTable'] is set
 380     // to 'tbl_select.php'
 381     unset($param);
 382
 383     $sql_query .= ' FROM ' . PMA_backquote($table);
 384
 385     // The where clause
 386     if (trim($where) != '') {
 387         $sql_query .= ' WHERE ' . $where;
 388     } else {
 389         $w = $charsets = array();
 390         $unary_operators = array(
 391            'IS NULL' => 1,
 392            'IS NOT NULL' => 1,
 393            "= ''" => 1,
 394            "!= ''" => 1
 395         );
 396         $geom_unary_operators = array(
 397             'IsEmpty' => 1,
 398             'IsSimple' => 1,
 399             'IsRing' => 1,
 400             'IsClosed' => 1,
 401         );
 402         $cnt_func = count($func);
 403         reset($func);
 404         while (list($i, $func_type) = each($func)) {
 405             // If geometry function is set apply it to the field name
 406             if (isset($geom_func[$i]) && trim($geom_func[$i]) != '') {
 407                 $backquoted_name = $geom_func[$i] . '(' . PMA_backquote($names[$i]) . ')';
 408
 409                 // New output type is the output type of the function being applied
 410                 $geom_funcs = PMA_getGISFunctions($types[$i], false, false);
 411                 $types[$i] = $geom_funcs[$geom_func[$i]]['type'];
 412
 413                 // If the intended where clause is something like 'IsEmpty(`spatial_col_name`)'
 414                 if (isset($geom_unary_operators[$geom_func[$i]]) && trim($fields[$i]) == '') {
 415                     $w[] = $backquoted_name;
 416                     continue;
 417                 }
 418             } else {
 419                 $backquoted_name = PMA_backquote($names[$i]);
 420             }
 421
 422             list($charsets[$i]) = explode('_', $collations[$i]);
 423             if (isset($unary_operators[$func_type])) {
 424                 $fields[$i] = '';
 425                 $w[] = $backquoted_name . ' ' . $func_type;
 426
 427             } elseif (in_array($types[$i], $geom_types)) {
 428                 $w[] = $backquoted_name . ' ' . $func_type . " GeomFromText('" . $fields[$i] . "')";
 429             } elseif (strncasecmp($types[$i], 'enum', 4) == 0) {
 430                 if (!empty($fields[$i])) {
 431                     if (! is_array($fields[$i])) {
 432                         $fields[$i] = explode(',', $fields[$i]);
 433                     }
 434                     $enum_selected_count = count($fields[$i]);
 435                     if ($func_type == '=' && $enum_selected_count > 1) {
 436                         $func_type    = $func[$i] = 'IN';
 437                         $parens_open  = '(';
 438                         $parens_close = ')';
 439
 440                     } elseif ($func_type == '!=' && $enum_selected_count > 1) {
 441                         $func_type    = $func[$i] = 'NOT IN';
 442                         $parens_open  = '(';
 443                         $parens_close = ')';
 444
 445                     } else {
 446                         $parens_open  = '';
 447                         $parens_close = '';
 448                     }
 449                     $enum_where = '\'' . PMA_sqlAddSlashes($fields[$i][0]) . '\'';
 450                     for ($e = 1; $e < $enum_selected_count; $e++) {
 451                         $enum_where .= ', \'' . PMA_sqlAddSlashes($fields[$i][$e]) . '\'';
 452                     }
 453
 454                     $w[] = $backquoted_name . ' ' . $func_type . ' ' . $parens_open . $enum_where . $parens_close;
 455                 }
 456
 457             } elseif ($fields[$i] != '') {
 458                 // For these types we quote the value. Even if it's another type (like INT),
 459                 // for a LIKE we always quote the value. MySQL converts strings to numbers
 460                 // and numbers to strings as necessary during the comparison
 461                 if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types[$i]) || strpos(' ' . $func_type, 'LIKE')) {
 462                     $quot = '\'';
 463                 } else {
 464                     $quot = '';
 465                 }
 466
 467                 // LIKE %...%
 468                 if ($func_type == 'LIKE %...%') {
 469                     $func_type = 'LIKE';
 470                     $fields[$i] = '%' . $fields[$i] . '%';
 471                 }
 472                 if ($func_type == 'REGEXP ^...$') {
 473                     $func_type = 'REGEXP';
 474                     $fields[$i] = '^' . $fields[$i] . '$';
 475                 }
 476
 477                 if ($func_type == 'IN (...)' || $func_type == 'NOT IN (...)' || $func_type == 'BETWEEN' || $func_type == 'NOT BETWEEN') {
 478                     $func_type = str_replace(' (...)', '', $func_type);
 479
 480                     // quote values one by one
 481                     $values = explode(',', $fields[$i]);
 482                     foreach ($values as &$value) {
 483                         $value = $quot . PMA_sqlAddSlashes(trim($value)) . $quot;
 484                     }
 485
 486                     if ($func_type == 'BETWEEN' || $func_type == 'NOT BETWEEN') {
 487                         $w[] = $backquoted_name . ' ' . $func_type . ' ' . (isset($values[0]) ? $values[0] : '')  . ' AND ' . (isset($values[1]) ? $values[1] : '');
 488                     } else {
 489                         $w[] = $backquoted_name . ' ' . $func_type . ' (' . implode(',', $values) . ')';
 490                     }
 491                 }
 492                 else {
 493                     $w[] = $backquoted_name . ' ' . $func_type . ' ' . $quot . PMA_sqlAddSlashes($fields[$i]) . $quot;;
 494                 }
 495
 496             } // end if
 497         } // end for
 498
 499         if ($w) {
 500             $sql_query .= ' WHERE ' . implode(' AND ', $w);
 501         }
 502     } // end if
 503
 504     if ($orderField != '--nil--') {
 505         $sql_query .= ' ORDER BY ' . PMA_backquote($orderField) . ' ' . $order;
 506     } // end if
 507
 508     require './sql.php';
 509 }
 510
 511 ?>