libraries/TableSearch.class.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Handles Table search and Zoom search
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 if (! defined('PHPMYADMIN')) {
   9     exit;
  10 }
  11
  12 /**
  13  * Class to handle normal-search
  14  * and zoom-search in a table
  15  *
  16  * @package PhpMyAdmin
  17  */
  18 class PMA_TableSearch
  19 {
  20     /**
  21      * Database name
  22      *
  23      * @access private
  24      * @var string
  25      */
  26     private $_db;
  27     /**
  28      * Table name
  29      *
  30      * @access private
  31      * @var string
  32      */
  33     private $_table;
  34     /**
  35      * Normal search or Zoom search
  36      *
  37      * @access private
  38      * @var string
  39      */
  40     private $_searchType;
  41     /**
  42      * Names of columns
  43      *
  44      * @access private
  45      * @var array
  46      */
  47     private $_columnNames;
  48     /**
  49      * Types of columns
  50      *
  51      * @access private
  52      * @var array
  53      */
  54     private $_columnTypes;
  55     /**
  56      * Collations of columns
  57      *
  58      * @access private
  59      * @var array
  60      */
  61     private $_columnCollations;
  62     /**
  63      * Null Flags of columns
  64      *
  65      * @access private
  66      * @var array
  67      */
  68     private $_columnNullFlags;
  69     /**
  70      * Whether a geometry column is present
  71      *
  72      * @access private
  73      * @var boolean
  74      */
  75     private $_geomColumnFlag;
  76     /**
  77      * Foreign Keys
  78      *
  79      * @access private
  80      * @var array
  81      */
  82     private $_foreigners;
  83
  84
  85     /**
  86      * Public Constructor
  87      *
  88      * @param string $db         Database name
  89      * @param string $table      Table name
  90      * @param string $searchType Whether normal or zoom search
  91      */
  92     public function __construct($db, $table, $searchType)
  93     {
  94         $this->_db = $db;
  95         $this->_table = $table;
  96         $this->_searchType = $searchType;
  97         $this->_columnNames = array();
  98         $this->_columnNullFlags = array();
  99         $this->_columnTypes = array();
 100         $this->_columnCollations = array();
 101         $this->_geomColumnFlag = false;
 102         $this->_foreigners = array();
 103         // Loads table's information
 104         $this->_loadTableInfo();
 105     }
 106
 107     /**
 108      * Returns Column names array
 109      *
 110      * @return array column names
 111      */
 112     public function getColumnNames()
 113     {
 114         return $this->_columnNames;
 115     }
 116
 117     /**
 118      * Gets all the columns of a table along with their types, collations
 119      * and whether null or not.
 120      *
 121      * @return void
 122      */
 123     private function _loadTableInfo()
 124     {
 125         // Gets the list and number of columns
 126         $columns = $GLOBALS['dbi']->getColumns(
 127             $this->_db, $this->_table, null, true
 128         );
 129         // Get details about the geometry functions
 130         $geom_types = PMA_Util::getGISDatatypes();
 131
 132         foreach ($columns as $row) {
 133             // set column name
 134             $this->_columnNames[] = $row['Field'];
 135
 136             $type = $row['Type'];
 137             // check whether table contains geometric columns
 138             if (in_array($type, $geom_types)) {
 139                 $this->_geomColumnFlag = true;
 140             }
 141             // reformat mysql query output
 142             if (strncasecmp($type, 'set', 3) == 0
 143                 || strncasecmp($type, 'enum', 4) == 0
 144             ) {
 145                 $type = str_replace(',', ', ', $type);
 146             } else {
 147                 // strip the "BINARY" attribute, except if we find "BINARY(" because
 148                 // this would be a BINARY or VARBINARY column type
 149                 if (! preg_match('@BINARY[\(]@i', $type)) {
 150                     $type = preg_replace('@BINARY@i', '', $type);
 151                 }
 152                 $type = preg_replace('@ZEROFILL@i', '', $type);
 153                 $type = preg_replace('@UNSIGNED@i', '', $type);
 154                 $type = /*overload*/mb_strtolower($type);
 155             }
 156             if (empty($type)) {
 157                 $type = '&nbsp;';
 158             }
 159             $this->_columnTypes[] = $type;
 160             $this->_columnNullFlags[] = $row['Null'];
 161             $this->_columnCollations[]
 162                 = ! empty($row['Collation']) && $row['Collation'] != 'NULL'
 163                 ? $row['Collation']
 164                 : '';
 165         } // end for
 166
 167         // Retrieve foreign keys
 168         $this->_foreigners = PMA_getForeigners($this->_db, $this->_table);
 169     }
 170
 171     /**
 172      * Sets the table header for displaying a table in query-by-example format.
 173      *
 174      * @return string HTML content, the tags and content for table header
 175      */
 176     private function _getTableHeader()
 177     {
 178         // Display the Function column only if there is at least one geometry column
 179         $func = '';
 180         if ($this->_geomColumnFlag) {
 181             $func = '<th>' . __('Function') . '</th>';
 182         }
 183
 184         return '<thead>
 185             <tr>' . $func . '<th>' .  __('Column') . '</th>
 186             <th>' .  __('Type') . '</th>
 187             <th>' .  __('Collation') . '</th>
 188             <th>' .  __('Operator') . '</th>
 189             <th>' .  __('Value') . '</th>
 190             </tr>
 191             </thead>';
 192     }
 193
 194     /**
 195      * Returns an array with necessary configurations to create
 196      * sub-tabs in the table_select page.
 197      *
 198      * @return array Array containing configuration (icon, text, link, id, args)
 199      * of sub-tabs
 200      */
 201     private function _getSubTabs()
 202     {
 203         $subtabs = array();
 204         $subtabs['search']['icon'] = 'b_search.png';
 205         $subtabs['search']['text'] = __('Table search');
 206         $subtabs['search']['link'] = 'tbl_select.php';
 207         $subtabs['search']['id'] = 'tbl_search_id';
 208         $subtabs['search']['args']['pos'] = 0;
 209
 210         $subtabs['zoom']['icon'] = 'b_props.png';
 211         $subtabs['zoom']['link'] = 'tbl_zoom_select.php';
 212         $subtabs['zoom']['text'] = __('Zoom search');
 213         $subtabs['zoom']['id'] = 'zoom_search_id';
 214
 215         $subtabs['replace']['icon'] = 'b_find_replace.png';
 216         $subtabs['replace']['link'] = 'tbl_find_replace.php';
 217         $subtabs['replace']['text'] = __('Find and replace');
 218         $subtabs['replace']['id'] = 'find_replace_id';
 219
 220         return $subtabs;
 221     }
 222
 223     /**
 224      * Provides html elements for search criteria inputbox
 225      * in case the column's type is geometrical
 226      *
 227      * @param int  $column_index Column's index
 228      * @param bool $in_fbs       Whether we are in 'function based search'
 229      *
 230      * @return string HTML elements.
 231      */
 232     private function _getGeometricalInputBox($column_index, $in_fbs)
 233     {
 234         $html_output = '<input type="text" name="criteriaValues['
 235             . $column_index . ']"'
 236             . ' size="40" class="textfield" id="field_' . $column_index . '" />';
 237
 238         if ($in_fbs) {
 239             $edit_url = 'gis_data_editor.php' . PMA_URL_getCommon();
 240             $edit_str = PMA_Util::getIcon('b_edit.png', __('Edit/Insert'));
 241             $html_output .= '<span class="open_search_gis_editor">';
 242             $html_output .= PMA_Util::linkOrButton(
 243                 $edit_url, $edit_str, array(), false, false, '_blank'
 244             );
 245             $html_output .= '</span>';
 246         }
 247         return $html_output;
 248     }
 249
 250     /**
 251      * Provides html elements for search criteria inputbox
 252      * in case the column is a Foreign Key
 253      *
 254      * @param array  $foreignData     Foreign keys data
 255      * @param string $column_name     Column name
 256      * @param int    $column_index    Column index
 257      * @param array  $titles          Selected title
 258      * @param int    $foreignMaxLimit Max limit of displaying foreign elements
 259      * @param array  $criteriaValues  Array of search criteria inputs
 260      * @param string $column_id       Column's inputbox's id
 261      *
 262      * @return string HTML elements.
 263      */
 264     private function _getForeignKeyInputBox($foreignData, $column_name,
 265         $column_index, $titles, $foreignMaxLimit, $criteriaValues, $column_id
 266     ) {
 267         $html_output = '';
 268         if (is_array($foreignData['disp_row'])) {
 269             $html_output .=  '<select name="criteriaValues[' . $column_index . ']"'
 270                 . ' id="' . $column_id . $column_index . '">';
 271             $html_output .= PMA_foreignDropdown(
 272                 $foreignData['disp_row'], $foreignData['foreign_field'],
 273                 $foreignData['foreign_display'], '', $foreignMaxLimit
 274             );
 275             $html_output .= '</select>';
 276
 277         } elseif ($foreignData['foreign_link'] == true) {
 278             $html_output .= '<input type="text" id="' . $column_id
 279                 . $column_index . '"'
 280                 . ' name="criteriaValues[' . $column_index . ']" id="field_'
 281                 . md5($column_name) . '[' . $column_index . ']" class="textfield"'
 282                 . (isset($criteriaValues[$column_index])
 283                     && is_string($criteriaValues[$column_index])
 284                     ? (' value="' . $criteriaValues[$column_index] . '"')
 285                     : '')
 286                 . ' />';
 287
 288             $html_output .= '<a class="ajax browse_foreign" href="'
 289                 . 'browse_foreigners.php'
 290                 . PMA_URL_getCommon(
 291                     array('db' => $this->_db, 'table' => $this->_table)
 292                 )
 293                 . '&amp;field=' . urlencode($column_name) . '&amp;fieldkey='
 294                 . $column_index . '&amp;fromsearch=1"';
 295             $html_output .= '>' . str_replace("'", "\'", $titles['Browse']) . '</a>';
 296         }
 297         return $html_output;
 298     }
 299
 300     /**
 301      * Provides html elements for search criteria inputbox
 302      * in case the column is of ENUM or SET type
 303      *
 304      * @param int    $column_index        Column index
 305      * @param array  $criteriaValues      Array of search criteria inputs
 306      * @param string $column_type         Column type
 307      * @param string $column_id           Column's inputbox's id
 308      * @param bool   $in_zoom_search_edit Whether we are in zoom search edit
 309      *
 310      * @return string HTML elements.
 311      */
 312     private function _getEnumSetInputBox($column_index, $criteriaValues,
 313         $column_type, $column_id, $in_zoom_search_edit = false
 314     ) {
 315         $column_type = htmlspecialchars($column_type);
 316         $html_output = '';
 317         $value = explode(
 318             ', ',
 319             str_replace("'", '', /*overload*/mb_substr($column_type, 5, -1))
 320         );
 321         $cnt_value = count($value);
 322
 323         /*
 324          * Enum in edit mode   --> dropdown
 325          * Enum in search mode --> multiselect
 326          * Set in edit mode    --> multiselect
 327          * Set in search mode  --> input (skipped here, so the 'else'
 328          *                                 section would handle it)
 329          */
 330         if ((strncasecmp($column_type, 'enum', 4) && ! $in_zoom_search_edit)
 331             || (strncasecmp($column_type, 'set', 3) && $in_zoom_search_edit)
 332         ) {
 333             $html_output .= '<select name="criteriaValues[' . ($column_index)
 334                 . ']" id="' . $column_id . $column_index . '">';
 335         } else {
 336             $html_output .= '<select name="criteriaValues[' . $column_index . ']"'
 337                 . ' id="' . $column_id . $column_index . '" multiple="multiple"'
 338                 . ' size="' . min(3, $cnt_value) . '">';
 339         }
 340
 341         //Add select options
 342         for ($j = 0; $j < $cnt_value; $j++) {
 343             if (isset($criteriaValues[$column_index])
 344                 && is_array($criteriaValues[$column_index])
 345                 && in_array($value[$j], $criteriaValues[$column_index])
 346             ) {
 347                 $html_output .= '<option value="' . $value[$j] . '" Selected>'
 348                     . $value[$j] . '</option>';
 349             } else {
 350                 $html_output .= '<option value="' . $value[$j] . '">'
 351                     . $value[$j] . '</option>';
 352             }
 353         } // end for
 354         $html_output .= '</select>';
 355         return $html_output;
 356     }
 357
 358     /**
 359      * Creates the HTML content for:
 360      * 1) Browsing foreign data for a column.
 361      * 2) Creating elements for search criteria input on columns.
 362      *
 363      * @param array  $foreignData         Foreign keys data
 364      * @param string $column_name         Column name
 365      * @param string $column_type         Column type
 366      * @param int    $column_index        Column index
 367      * @param array  $titles              Selected title
 368      * @param int    $foreignMaxLimit     Max limit of displaying foreign elements
 369      * @param array  $criteriaValues      Array of search criteria inputs
 370      * @param bool   $in_fbs              Whether we are in 'function based search'
 371      * @param bool   $in_zoom_search_edit Whether we are in zoom search edit
 372      *
 373      * @return string HTML content for viewing foreign data and elements
 374      * for search criteria input.
 375      */
 376     private function _getInputbox($foreignData, $column_name, $column_type,
 377         $column_index, $titles, $foreignMaxLimit, $criteriaValues, $in_fbs = false,
 378         $in_zoom_search_edit = false
 379     ) {
 380         $str = '';
 381         $column_type = (string)$column_type;
 382         $column_id = ($in_zoom_search_edit) ? 'edit_fieldID_' : 'fieldID_';
 383
 384         // Get inputbox based on different column types
 385         // (Foreign key, geometrical, enum)
 386         if ($this->_foreigners
 387             && PMA_searchColumnInForeigners($this->_foreigners, $column_name)
 388         ) {
 389             $str .= $this->_getForeignKeyInputBox(
 390                 $foreignData, $column_name, $column_index, $titles,
 391                 $foreignMaxLimit, $criteriaValues, $column_id
 392             );
 393
 394         } elseif (in_array($column_type, PMA_Util::getGISDatatypes())) {
 395             $str .= $this->_getGeometricalInputBox($column_index, $in_fbs);
 396
 397         } elseif (strncasecmp($column_type, 'enum', 4) == 0
 398             || (strncasecmp($column_type, 'set', 3) == 0 && $in_zoom_search_edit)
 399         ) {
 400             $str .= $this->_getEnumSetInputBox(
 401                 $column_index, $criteriaValues, $column_type, $column_id,
 402                 $in_zoom_search_edit = false
 403             );
 404
 405         } else {
 406             // other cases
 407             $the_class = 'textfield';
 408
 409             if ($column_type == 'date') {
 410                 $the_class .= ' datefield';
 411             } elseif ($column_type == 'datetime'
 412                 || substr($column_type, 0, 9) == 'timestamp'
 413             ) {
 414                 $the_class .= ' datetimefield';
 415             } elseif (substr($column_type, 0, 3) == 'bit') {
 416                 $the_class .= ' bit';
 417             }
 418
 419             $str .= '<input type="text" name="criteriaValues[' . $column_index . ']"'
 420                 . ' size="40" class="' . $the_class . '" id="'
 421                 . $column_id . $column_index . '"'
 422                 . (isset($criteriaValues[$column_index])
 423                     && is_string($criteriaValues[$column_index])
 424                     ? (' value="' . $criteriaValues[$column_index] . '"')
 425                     : '')
 426                 . ' />';
 427         }
 428         return $str;
 429     }
 430
 431     /**
 432      * Return the where clause in case column's type is ENUM.
 433      *
 434      * @param mixed  $criteriaValues Search criteria input
 435      * @param string $func_type      Search function/operator
 436      *
 437      * @return string part of where clause.
 438      */
 439     private function _getEnumWhereClause($criteriaValues, $func_type)
 440     {
 441         if (! is_array($criteriaValues)) {
 442             $criteriaValues = explode(',', $criteriaValues);
 443         }
 444         $enum_selected_count = count($criteriaValues);
 445         if ($func_type == '=' && $enum_selected_count > 1) {
 446             $func_type    = 'IN';
 447             $parens_open  = '(';
 448             $parens_close = ')';
 449
 450         } elseif ($func_type == '!=' && $enum_selected_count > 1) {
 451             $func_type    = 'NOT IN';
 452             $parens_open  = '(';
 453             $parens_close = ')';
 454
 455         } else {
 456             $parens_open  = '';
 457             $parens_close = '';
 458         }
 459         $enum_where = '\''
 460             . PMA_Util::sqlAddSlashes($criteriaValues[0]) . '\'';
 461         for ($e = 1; $e < $enum_selected_count; $e++) {
 462             $enum_where .= ', \''
 463                 . PMA_Util::sqlAddSlashes($criteriaValues[$e]) . '\'';
 464         }
 465
 466         return ' ' . $func_type . ' ' . $parens_open
 467             . $enum_where . $parens_close;
 468     }
 469
 470     /**
 471      * Return the where clause for a geometrical column.
 472      *
 473      * @param mixed  $criteriaValues Search criteria input
 474      * @param string $names          Name of the column on which search is submitted
 475      * @param string $func_type      Search function/operator
 476      * @param string $types          Type of the field
 477      * @param bool   $geom_func      Whether geometry functions should be applied
 478      *
 479      * @return string part of where clause.
 480      */
 481     private function _getGeomWhereClause($criteriaValues, $names,
 482         $func_type, $types, $geom_func = null
 483     ) {
 484         $geom_unary_functions = array(
 485             'IsEmpty' => 1,
 486             'IsSimple' => 1,
 487             'IsRing' => 1,
 488             'IsClosed' => 1,
 489         );
 490         $where = '';
 491
 492         // Get details about the geometry functions
 493         $geom_funcs = PMA_Util::getGISFunctions($types, true, false);
 494         // New output type is the output type of the function being applied
 495         $types = $geom_funcs[$geom_func]['type'];
 496
 497         // If the function takes a single parameter
 498         if ($geom_funcs[$geom_func]['params'] == 1) {
 499             $backquoted_name = $geom_func . '(' . PMA_Util::backquote($names) . ')';
 500         } else {
 501             // If the function takes two parameters
 502             // create gis data from the criteria input
 503             $gis_data = PMA_Util::createGISData($criteriaValues);
 504             $where = $geom_func . '(' . PMA_Util::backquote($names)
 505                 . ',' . $gis_data . ')';
 506             return $where;
 507         }
 508
 509         // If the where clause is something like 'IsEmpty(`spatial_col_name`)'
 510         if (isset($geom_unary_functions[$geom_func])
 511             && trim($criteriaValues) == ''
 512         ) {
 513             $where = $backquoted_name;
 514
 515         } elseif (in_array($types, PMA_Util::getGISDatatypes())
 516             && ! empty($criteriaValues)
 517         ) {
 518             // create gis data from the criteria input
 519             $gis_data = PMA_Util::createGISData($criteriaValues);
 520             $where = $backquoted_name . ' ' . $func_type . ' ' . $gis_data;
 521         }
 522         return $where;
 523     }
 524
 525     /**
 526      * Return the where clause for query generation based on the inputs provided.
 527      *
 528      * @param mixed  $criteriaValues Search criteria input
 529      * @param string $names          Name of the column on which search is submitted
 530      * @param string $types          Type of the field
 531      * @param string $func_type      Search function/operator
 532      * @param bool   $unaryFlag      Whether operator unary or not
 533      * @param bool   $geom_func      Whether geometry functions should be applied
 534      *
 535      * @return string generated where clause.
 536      */
 537     private function _getWhereClause($criteriaValues, $names, $types,
 538         $func_type, $unaryFlag, $geom_func = null
 539     ) {
 540         // If geometry function is set
 541         if ($geom_func != null && trim($geom_func) != '') {
 542             return $this->_getGeomWhereClause(
 543                 $criteriaValues, $names, $func_type, $types, $geom_func
 544             );
 545         }
 546
 547         $backquoted_name = PMA_Util::backquote($names);
 548         $where = '';
 549         if ($unaryFlag) {
 550             $where = $backquoted_name . ' ' . $func_type;
 551
 552         } elseif (strncasecmp($types, 'enum', 4) == 0 && ! empty($criteriaValues)) {
 553             $where = $backquoted_name;
 554             $where .= $this->_getEnumWhereClause($criteriaValues, $func_type);
 555
 556         } elseif ($criteriaValues != '') {
 557             // For these types we quote the value. Even if it's another type
 558             // (like INT), for a LIKE we always quote the value. MySQL converts
 559             // strings to numbers and numbers to strings as necessary
 560             // during the comparison
 561             if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types)
 562                 || /*overload*/mb_strpos(' ' . $func_type, 'LIKE')
 563             ) {
 564                 $quot = '\'';
 565             } else {
 566                 $quot = '';
 567             }
 568
 569             // LIKE %...%
 570             if ($func_type == 'LIKE %...%') {
 571                 $func_type = 'LIKE';
 572                 $criteriaValues = '%' . $criteriaValues . '%';
 573             }
 574             if ($func_type == 'REGEXP ^...$') {
 575                 $func_type = 'REGEXP';
 576                 $criteriaValues = '^' . $criteriaValues . '$';
 577             }
 578
 579             if ('IN (...)' != $func_type
 580                 && 'NOT IN (...)' != $func_type
 581                 && 'BETWEEN' != $func_type
 582                 && 'NOT BETWEEN' != $func_type
 583             ) {
 584                 if ($func_type == 'LIKE %...%' || $func_type == 'LIKE') {
 585                     $where = $backquoted_name . ' ' . $func_type . ' ' . $quot
 586                         . PMA_Util::sqlAddSlashes($criteriaValues, true) . $quot;
 587                 } else {
 588                     $where = $backquoted_name . ' ' . $func_type . ' ' . $quot
 589                         . PMA_Util::sqlAddSlashes($criteriaValues) . $quot;
 590                 }
 591                 return $where;
 592             }
 593             $func_type = str_replace(' (...)', '', $func_type);
 594
 595             //Don't explode if this is already an array
 596             //(Case for (NOT) IN/BETWEEN.)
 597             if (is_array($criteriaValues)) {
 598                 $values = $criteriaValues;
 599             } else {
 600                 $values = explode(',', $criteriaValues);
 601             }
 602             // quote values one by one
 603             $emptyKey = false;
 604             foreach ($values as $key => &$value) {
 605                 if ('' === $value) {
 606                     $emptyKey = $key;
 607                     $value = 'NULL';
 608                     continue;
 609                 }
 610                 $value = $quot . PMA_Util::sqlAddSlashes(trim($value))
 611                     . $quot;
 612             }
 613
 614             if ('BETWEEN' == $func_type || 'NOT BETWEEN' == $func_type) {
 615                 $where = $backquoted_name . ' ' . $func_type . ' '
 616                     . (isset($values[0]) ? $values[0] : '')
 617                     . ' AND ' . (isset($values[1]) ? $values[1] : '');
 618             } else { //[NOT] IN
 619                 if (false !== $emptyKey) {
 620                     unset($values[$emptyKey]);
 621                 }
 622                 $wheres = array();
 623                 if (!empty($values)) {
 624                     $wheres[] = $backquoted_name . ' ' . $func_type
 625                         . ' (' . implode(',', $values) . ')';
 626                 }
 627                 if (false !== $emptyKey) {
 628                     $wheres[] = $backquoted_name . ' IS NULL';
 629                 }
 630                 $where = implode(' OR ', $wheres);
 631                 if (1 < count($wheres)) {
 632                     $where = '(' . $where . ')';
 633                 }
 634             }
 635         } // end if
 636
 637         return $where;
 638     }
 639
 640     /**
 641      * Builds the sql search query from the post parameters
 642      *
 643      * @return string the generated SQL query
 644      */
 645     public function buildSqlQuery()
 646     {
 647         $sql_query = 'SELECT ';
 648
 649         // If only distinct values are needed
 650         $is_distinct = (isset($_POST['distinct'])) ? 'true' : 'false';
 651         if ($is_distinct == 'true') {
 652             $sql_query .= 'DISTINCT ';
 653         }
 654
 655         // if all column names were selected to display, we do a 'SELECT *'
 656         // (more efficient and this helps prevent a problem in IE
 657         // if one of the rows is edited and we come back to the Select results)
 658         if (isset($_POST['zoom_submit']) || ! empty($_POST['displayAllColumns'])) {
 659             $sql_query .= '* ';
 660         } else {
 661             $sql_query .= implode(
 662                 ', ',
 663                 PMA_Util::backquote($_POST['columnsToDisplay'])
 664             );
 665         } // end if
 666
 667         $sql_query .= ' FROM '
 668             . PMA_Util::backquote($_POST['table']);
 669         $whereClause = $this->_generateWhereClause();
 670         $sql_query .= $whereClause;
 671
 672         // if the search results are to be ordered
 673         if (isset($_POST['orderByColumn']) && $_POST['orderByColumn'] != '--nil--') {
 674             $sql_query .= ' ORDER BY '
 675                 . PMA_Util::backquote($_POST['orderByColumn'])
 676                 . ' ' . $_POST['order'];
 677         } // end if
 678         return $sql_query;
 679     }
 680
 681     /**
 682      * Generates the where clause for the SQL search query to be executed
 683      *
 684      * @return string the generated where clause
 685      */
 686     private function _generateWhereClause()
 687     {
 688         if (isset($_POST['customWhereClause'])
 689             && trim($_POST['customWhereClause']) != ''
 690         ) {
 691             return ' WHERE ' . $_POST['customWhereClause'];
 692         }
 693
 694         // If there are no search criteria set or no unary criteria operators,
 695         // return
 696         if (! isset($_POST['criteriaValues'])
 697             && ! isset($_POST['criteriaColumnOperators'])
 698         ) {
 699             return '';
 700         }
 701
 702         // else continue to form the where clause from column criteria values
 703         $fullWhereClause = array();
 704         reset($_POST['criteriaColumnOperators']);
 705         while (list($column_index, $operator) = each(
 706             $_POST['criteriaColumnOperators']
 707         )) {
 708
 709             $unaryFlag =  $GLOBALS['PMA_Types']->isUnaryOperator($operator);
 710             $tmp_geom_func = isset($geom_func[$column_index])
 711                 ? $geom_func[$column_index] : null;
 712
 713             $whereClause = $this->_getWhereClause(
 714                 $_POST['criteriaValues'][$column_index],
 715                 $_POST['criteriaColumnNames'][$column_index],
 716                 $_POST['criteriaColumnTypes'][$column_index],
 717                 $operator,
 718                 $unaryFlag,
 719                 $tmp_geom_func
 720             );
 721
 722             if ($whereClause) {
 723                 $fullWhereClause[] = $whereClause;
 724             }
 725         } // end while
 726
 727         if ($fullWhereClause) {
 728             return ' WHERE ' . implode(' AND ', $fullWhereClause);
 729         }
 730         return '';
 731     }
 732
 733     /**
 734      * Generates HTML for a geometrical function column to be displayed in table
 735      * search selection form
 736      *
 737      * @param integer $column_index index of current column in $columnTypes array
 738      *
 739      * @return string the generated HTML
 740      */
 741     private function _getGeomFuncHtml($column_index)
 742     {
 743         $html_output = '';
 744         // return if geometrical column is not present
 745         if (! $this->_geomColumnFlag) {
 746             return $html_output;
 747         }
 748
 749         /**
 750          * Displays 'Function' column if it is present
 751          */
 752         $html_output .= '<td>';
 753         $geom_types = PMA_Util::getGISDatatypes();
 754         // if a geometry column is present
 755         if (in_array($this->_columnTypes[$column_index], $geom_types)) {
 756             $html_output .= '<select class="geom_func" name="geom_func['
 757                 . $column_index . ']">';
 758             // get the relevant list of GIS functions
 759             $funcs = PMA_Util::getGISFunctions(
 760                 $this->_columnTypes[$column_index], true, true
 761             );
 762             /**
 763              * For each function in the list of functions,
 764              * add an option to select list
 765              */
 766             foreach ($funcs as $func_name => $func) {
 767                 $name = isset($func['display']) ? $func['display'] : $func_name;
 768                 $html_output .= '<option value="' . htmlspecialchars($name) . '">'
 769                         . htmlspecialchars($name) . '</option>';
 770             }
 771             $html_output .= '</select>';
 772         } else {
 773             $html_output .= '&nbsp;';
 774         }
 775         $html_output .= '</td>';
 776         return $html_output;
 777     }
 778
 779     /**
 780      * Generates formatted HTML for extra search options in table search form
 781      *
 782      * @return string the generated HTML
 783      */
 784     private function _getOptions()
 785     {
 786         $html_output = '';
 787         $html_output .= PMA_Util::getDivForSliderEffect(
 788             'searchoptions', __('Options')
 789         );
 790
 791         /**
 792          * Displays columns select list for selecting distinct columns in the search
 793          */
 794         $html_output .= '<fieldset id="fieldset_select_fields">'
 795             . '<legend>' . __('Select columns (at least one):') . '</legend>'
 796             . '<select name="columnsToDisplay[]"'
 797             . ' size="' . min(count($this->_columnNames), 10) . '"'
 798             . ' multiple="multiple">';
 799         // Displays the list of the fields
 800         foreach ($this->_columnNames as $each_field) {
 801             $html_output .= '        '
 802                 . '<option value="' . htmlspecialchars($each_field) . '"'
 803                 . ' selected="selected">' . htmlspecialchars($each_field)
 804                 . '</option>' . "\n";
 805         } // end for
 806         $html_output .= '</select>'
 807             . '<input type="checkbox" name="distinct" value="DISTINCT"'
 808             . ' id="oDistinct" />'
 809             . '<label for="oDistinct">DISTINCT</label></fieldset>';
 810
 811         /**
 812          * Displays input box for custom 'Where' clause to be used in the search
 813          */
 814         $html_output .= '<fieldset id="fieldset_search_conditions">'
 815             . '<legend>' . '<em>' . __('Or') . '</em> '
 816             . __('Add search conditions (body of the "where" clause):')
 817             . '</legend>';
 818         $html_output .= PMA_Util::showMySQLDocu('Functions');
 819         $html_output .= '<input type="text" name="customWhereClause"'
 820             . ' class="textfield" size="64" />';
 821         $html_output .= '</fieldset>';
 822
 823         /**
 824          * Displays option of changing default number of rows displayed per page
 825          */
 826         $html_output .= '<fieldset id="fieldset_limit_rows">'
 827             . '<legend>' . __('Number of rows per page') . '</legend>'
 828             . '<input type="number" name="session_max_rows" required="required" '
 829             . 'min="1" '
 830             . 'value="' . $GLOBALS['cfg']['MaxRows'] . '" class="textfield" />'
 831             . '</fieldset>';
 832
 833         /**
 834          * Displays option for ordering search results
 835          * by a column value (Asc or Desc)
 836          */
 837         $html_output .= '<fieldset id="fieldset_display_order">'
 838             . '<legend>' . __('Display order:') . '</legend>'
 839             . '<select name="orderByColumn"><option value="--nil--"></option>';
 840         foreach ($this->_columnNames as $each_field) {
 841             $html_output .= '        '
 842                 . '<option value="' . htmlspecialchars($each_field) . '">'
 843                 . htmlspecialchars($each_field) . '</option>' . "\n";
 844         } // end for
 845         $html_output .= '</select>';
 846         $choices = array(
 847             'ASC' => __('Ascending'),
 848             'DESC' => __('Descending')
 849         );
 850         $html_output .= PMA_Util::getRadioFields(
 851             'order', $choices, 'ASC', false, true, "formelement"
 852         );
 853         unset($choices);
 854
 855         $html_output .= '</fieldset><br style="clear: both;"/></div>';
 856         return $html_output;
 857     }
 858
 859     /**
 860      * Other search criteria like data label
 861      * (for tbl_zoom_select.php)
 862      *
 863      * @param string|null $dataLabel Label for points in zoom plot
 864      *
 865      * @return string the generated html
 866      */
 867     private function _getOptionsZoom($dataLabel)
 868     {
 869         $html_output = '';
 870         $html_output .= '<table class="data">';
 871         //Select options for datalabel
 872         $html_output .= '<tr>';
 873         $html_output .= '<td><label for="dataLabel">'
 874             . __("Use this column to label each point") . '</label></td>';
 875         $html_output .= '<td><select name="dataLabel" id="dataLabel" >'
 876             . '<option value = "">' . __('None') . '</option>';
 877         for ($j = 0, $nb = count($this->_columnNames); $j < $nb; $j++) {
 878             if (isset($dataLabel)
 879                 && $dataLabel == htmlspecialchars($this->_columnNames[$j])
 880             ) {
 881                 $html_output .= '<option value="'
 882                     . htmlspecialchars($this->_columnNames[$j])
 883                     . '" selected="selected">'
 884                     . htmlspecialchars($this->_columnNames[$j])
 885                     . '</option>';
 886             } else {
 887                 $html_output .= '<option value="'
 888                     . htmlspecialchars($this->_columnNames[$j]) . '" >'
 889                     . htmlspecialchars($this->_columnNames[$j]) . '</option>';
 890             }
 891         }
 892         $html_output .= '</select></td>';
 893         $html_output .= '</tr>';
 894         //Inputbox for changing default maximum rows to plot
 895         $html_output .= '<tr>';
 896         $html_output .= '<td><label for="maxRowPlotLimit">'
 897             . __("Maximum rows to plot") . '</label></td>';
 898         $html_output .= '<td>';
 899         $html_output .= '<input type="number" name="maxPlotLimit"'
 900             . ' id="maxRowPlotLimit" required="required"'
 901             . ' value="' . ((! empty($_POST['maxPlotLimit']))
 902                 ? htmlspecialchars($_POST['maxPlotLimit'])
 903                 : $GLOBALS['cfg']['maxRowPlotLimit'])
 904             . '" />';
 905         $html_output .= '</td></tr>';
 906         $html_output .= '</table>';
 907         return $html_output;
 908     }
 909
 910     /**
 911      * Provides a column's type, collation, operators list, and criteria value
 912      * to display in table search form
 913      *
 914      * @param integer $search_index Row number in table search form
 915      * @param integer $column_index Column index in ColumnNames array
 916      *
 917      * @return array Array containing column's properties
 918      */
 919     public function getColumnProperties($search_index, $column_index)
 920     {
 921         $selected_operator = (isset($_POST['criteriaColumnOperators'])
 922             ? $_POST['criteriaColumnOperators'][$search_index] : '');
 923         $entered_value = (isset($_POST['criteriaValues'])
 924             ? $_POST['criteriaValues'] : '');
 925         $titles = array(
 926             'Browse' => PMA_Util::getIcon(
 927                 'b_browse.png', __('Browse foreign values')
 928             )
 929         );
 930         //Gets column's type and collation
 931         $type = $this->_columnTypes[$column_index];
 932         $collation = $this->_columnCollations[$column_index];
 933         //Gets column's comparison operators depending on column type
 934         $func = '<select name="criteriaColumnOperators['
 935             . $search_index . ']" onchange="changeValueFieldType(this, '
 936             . $search_index . ')">';
 937         $func .= $GLOBALS['PMA_Types']->getTypeOperatorsHtml(
 938             preg_replace('@\(.*@s', '', $this->_columnTypes[$column_index]),
 939             $this->_columnNullFlags[$column_index], $selected_operator
 940         );
 941         $func .= '</select>';
 942         //Gets link to browse foreign data(if any) and criteria inputbox
 943         $foreignData = PMA_getForeignData(
 944             $this->_foreigners, $this->_columnNames[$column_index], false, '', ''
 945         );
 946         $value =  $this->_getInputbox(
 947             $foreignData, $this->_columnNames[$column_index], $type, $search_index,
 948             $titles, $GLOBALS['cfg']['ForeignKeyMaxLimit'], $entered_value
 949         );
 950         return array(
 951             'type' => $type,
 952             'collation' => $collation,
 953             'func' => $func,
 954             'value' => $value
 955         );
 956     }
 957
 958     /**
 959      * Provides the search form's table row in case of Normal Search
 960      * (for tbl_select.php)
 961      *
 962      * @return string the generated table row
 963      */
 964     private function _getRowsNormal()
 965     {
 966         $odd_row = true;
 967         $html_output = '';
 968         // for every column present in table
 969         for (
 970             $column_index = 0, $nb = count($this->_columnNames);
 971             $column_index < $nb;
 972             $column_index++
 973         ) {
 974             $html_output .= '<tr class="noclick '
 975                 . ($odd_row ? 'odd' : 'even')
 976                 . '">';
 977             $odd_row = !$odd_row;
 978             //If 'Function' column is present
 979             $html_output .= $this->_getGeomFuncHtml($column_index);
 980             //Displays column's name, type, collation and value
 981             $html_output .= '<th>'
 982                 . htmlspecialchars($this->_columnNames[$column_index]) . '</th>';
 983             $properties = $this->getColumnProperties($column_index, $column_index);
 984             $html_output .= '<td>'
 985                 . htmlspecialchars($properties['type'])
 986                 . '</td>';
 987             $html_output .= '<td>' . $properties['collation'] . '</td>';
 988             $html_output .= '<td>' . $properties['func'] . '</td>';
 989             // here, the data-type attribute is needed for a date/time picker
 990             $html_output .= '<td data-type="'
 991                 . htmlspecialchars($properties['type']) . '"'
 992                 . '>' . $properties['value'] . '</td>';
 993             $html_output .= '</tr>';
 994             //Displays hidden fields
 995             $html_output .= '<tr><td>';
 996             $html_output .= '<input type="hidden"'
 997                 . ' name="criteriaColumnNames[' . $column_index . ']"'
 998                 . ' value="'
 999                 . htmlspecialchars($this->_columnNames[$column_index])
1000                 . '" />';
1001             $html_output .= '<input type="hidden"'
1002                 . ' name="criteriaColumnTypes[' . $column_index . ']"'
1003                 . ' value="'
1004                 . htmlspecialchars($this->_columnTypes[$column_index]) . '" />';
1005             $html_output .= '<input type="hidden"'
1006                 . ' name="criteriaColumnCollations[' . $column_index . ']"'
1007                 . ' value="' . $this->_columnCollations[$column_index] . '" />';
1008             $html_output .= '</td></tr>';
1009         } // end for
1010
1011         return $html_output;
1012     }
1013
1014     /**
1015      * Provides the search form's table row in case of Zoom search
1016      * (for tbl_zoom_select.php)
1017      *
1018      * @return string the generated table row
1019      */
1020     private function _getRowsZoom()
1021     {
1022         $odd_row = true;
1023         $html_output = '';
1024         $type = $collation = $func = $value = array();
1025         /**
1026          * Get already set search criteria (if any)
1027          */
1028
1029         //Displays column rows for search criteria input
1030         for ($i = 0; $i < 4; $i++) {
1031             //After X-Axis and Y-Axis column rows, display additional criteria
1032             // option
1033             if ($i == 2) {
1034                 $html_output .= '<tr><td>';
1035                 $html_output .= __("Additional search criteria");
1036                 $html_output .= '</td></tr>';
1037             }
1038             $html_output .= '<tr class="noclick '
1039                 . ($odd_row ? 'odd' : 'even')
1040                 . '">';
1041             $odd_row = ! $odd_row;
1042             //Select options for column names
1043             $html_output .= '<th><select name="criteriaColumnNames[]" id="'
1044                 . 'tableid_' . $i . '" >';
1045             $html_output .= '<option value="' . 'pma_null' . '">' . __('None')
1046                 . '</option>';
1047             for ($j = 0, $nb = count($this->_columnNames); $j < $nb; $j++) {
1048                 if (isset($_POST['criteriaColumnNames'][$i])
1049                     && $_POST['criteriaColumnNames'][$i] == htmlspecialchars($this->_columnNames[$j])
1050                 ) {
1051                     $html_output .= '<option value="'
1052                         . htmlspecialchars($this->_columnNames[$j])
1053                         . '" selected="selected">'
1054                         . htmlspecialchars($this->_columnNames[$j])
1055                         . '</option>';
1056                 } else {
1057                     $html_output .= '<option value="'
1058                         . htmlspecialchars($this->_columnNames[$j]) . '">'
1059                         . htmlspecialchars($this->_columnNames[$j]) . '</option>';
1060                 }
1061             }
1062             $html_output .= '</select></th>';
1063             if (isset($_POST['criteriaColumnNames'])
1064                 && $_POST['criteriaColumnNames'][$i] != 'pma_null'
1065             ) {
1066                 $key = array_search(
1067                     $_POST['criteriaColumnNames'][$i],
1068                     $this->_columnNames
1069                 );
1070                 $properties = $this->getColumnProperties($i, $key);
1071                 $type[$i] = $properties['type'];
1072                 $collation[$i] = $properties['collation'];
1073                 $func[$i] = $properties['func'];
1074                 $value[$i] = $properties['value'];
1075             }
1076             //Column type
1077             $html_output .= '<td>' . (isset($type[$i]) ? $type[$i] : '') . '</td>';
1078             //Column Collation
1079             $html_output .= '<td>' . (isset($collation[$i]) ? $collation[$i] : '')
1080                 . '</td>';
1081             //Select options for column operators
1082             $html_output .= '<td>' . (isset($func[$i]) ? $func[$i] : '') . '</td>';
1083             //Inputbox for search criteria value
1084             $html_output .= '<td>' . (isset($value[$i]) ? $value[$i] : '') . '</td>';
1085             $html_output .= '</tr>';
1086             //Displays hidden fields
1087             $html_output .= '<tr><td>';
1088             $html_output
1089                 .= '<input type="hidden" name="criteriaColumnTypes[' . $i . ']"'
1090                 . ' id="types_' . $i . '" ';
1091             if (isset($_POST['criteriaColumnTypes'][$i])) {
1092                 $html_output .= 'value="' . $_POST['criteriaColumnTypes'][$i] . '" ';
1093             }
1094             $html_output .= '/>';
1095             $html_output .= '<input type="hidden" name="criteriaColumnCollations['
1096                 . $i . ']" id="collations_' . $i . '" />';
1097             $html_output .= '</td></tr>';
1098         }//end for
1099         return $html_output;
1100     }
1101
1102     /**
1103      * Generates HTML for displaying fields table in search form
1104      *
1105      * @return string the generated HTML
1106      */
1107     private function _getFieldsTableHtml()
1108     {
1109         $html_output = '';
1110         $html_output .= '<table class="data"'
1111             . ($this->_searchType == 'zoom' ? ' id="tableFieldsId"' : '') . '>';
1112         $html_output .= $this->_getTableHeader();
1113         $html_output .= '<tbody>';
1114
1115         if ($this->_searchType == 'zoom') {
1116             $html_output .= $this->_getRowsZoom();
1117         } else {
1118             $html_output .= $this->_getRowsNormal();
1119         }
1120
1121         $html_output .= '</tbody></table>';
1122         return $html_output;
1123     }
1124
1125     /**
1126      * Provides the form tag for table search form
1127      * (normal search or zoom search)
1128      *
1129      * @param string $goto Goto URL
1130      *
1131      * @return string the HTML for form tag
1132      */
1133     private function _getFormTag($goto)
1134     {
1135         $html_output = '';
1136         $scriptName = '';
1137         $formId = '';
1138         switch ($this->_searchType) {
1139         case 'normal' :
1140             $scriptName = 'tbl_select.php';
1141             $formId = 'tbl_search_form';
1142             break;
1143         case 'zoom' :
1144             $scriptName = 'tbl_zoom_select.php';
1145             $formId = 'zoom_search_form';
1146             break;
1147         case 'replace' :
1148             $scriptName = 'tbl_find_replace.php';
1149             $formId = 'find_replace_form';
1150             break;
1151         }
1152
1153         $html_output .= '<form method="post" action="' . $scriptName . '" '
1154             . 'name="insertForm" id="' . $formId . '" '
1155             . 'class="ajax"' . '>';
1156
1157         $html_output .= PMA_URL_getHiddenInputs($this->_db, $this->_table);
1158         $html_output .= '<input type="hidden" name="goto" value="' . $goto . '" />';
1159         $html_output .= '<input type="hidden" name="back" value="' . $scriptName
1160             . '" />';
1161
1162         return $html_output;
1163     }
1164
1165     /**
1166      * Returns the HTML for secondary levels tabs of the table search page
1167      *
1168      * @return string HTML for secondary levels tabs
1169      */
1170     public function getSecondaryTabs()
1171     {
1172         $url_params = array();
1173         $url_params['db'] = $this->_db;
1174         $url_params['table'] = $this->_table;
1175
1176         $html_output = '<ul id="topmenu2">';
1177         foreach ($this->_getSubTabs() as $tab) {
1178             $html_output .= PMA_Util::getHtmlTab($tab, $url_params);
1179         }
1180         $html_output .= '</ul>';
1181         $html_output .= '<div class="clearfloat"></div>';
1182         return $html_output;
1183     }
1184
1185     /**
1186      * Generates the table search form under table search tab
1187      *
1188      * @param string      $goto      Goto URL
1189      * @param string|null $dataLabel Label for points in zoom plot
1190      *
1191      * @return string the generated HTML for table search form
1192      */
1193     public function getSelectionForm($goto, $dataLabel = null)
1194     {
1195         $html_output = $this->_getFormTag($goto);
1196
1197         if ($this->_searchType == 'zoom') {
1198             $html_output .= '<fieldset id="fieldset_zoom_search">';
1199             $html_output .= '<fieldset id="inputSection">';
1200             $html_output .= '<legend>'
1201                 . __(
1202                     'Do a "query by example" (wildcard: "%") for two'
1203                     . ' different columns'
1204                 )
1205                 . '</legend>';
1206             $html_output .= $this->_getFieldsTableHtml();
1207             $html_output .= $this->_getOptionsZoom($dataLabel);
1208             $html_output .= '</fieldset>';
1209             $html_output .= '</fieldset>';
1210         } else if ($this->_searchType == 'normal') {
1211             $html_output .= '<fieldset id="fieldset_table_search">';
1212             $html_output .= '<fieldset id="fieldset_table_qbe">';
1213             $html_output .= '<legend>'
1214                 . __('Do a "query by example" (wildcard: "%")')
1215                 . '</legend>';
1216             $html_output .= $this->_getFieldsTableHtml();
1217             $html_output .= '<div id="gis_editor"></div>';
1218             $html_output .= '<div id="popup_background"></div>';
1219             $html_output .= '</fieldset>';
1220             $html_output .= $this->_getOptions();
1221             $html_output .= '</fieldset>';
1222         } else if ($this->_searchType == 'replace') {
1223             $html_output .= '<fieldset id="fieldset_find_replace">';
1224             $html_output .= '<fieldset id="fieldset_find">';
1225             $html_output .= '<legend>' . __('Find and replace') . '</legend>';
1226             $html_output .= $this->_getSearchAndReplaceHTML();
1227             $html_output .= '</fieldset>';
1228             $html_output .= '</fieldset>';
1229         }
1230
1231         /**
1232          * Displays selection form's footer elements
1233          */
1234         $html_output .= '<fieldset class="tblFooters">';
1235         $html_output .= '<input type="submit" name="'
1236             . ($this->_searchType == 'zoom' ? 'zoom_submit' : 'submit')
1237             . ($this->_searchType == 'zoom' ? '" id="inputFormSubmitId"' : '" ')
1238             . 'value="' . __('Go') . '" />';
1239         $html_output .= '</fieldset></form>';
1240         $html_output .= '<div id="sqlqueryresultsouter"></div>';
1241         return $html_output;
1242     }
1243
1244     /**
1245      * Provides form for displaying point data and also the scatter plot
1246      * (for tbl_zoom_select.php)
1247      *
1248      * @param string $goto Goto URL
1249      * @param array  $data Array containing SQL query data
1250      *
1251      * @return string form's html
1252      */
1253     public function getZoomResultsForm($goto, $data)
1254     {
1255         $html_output = '';
1256         $titles = array(
1257             'Browse' => PMA_Util::getIcon(
1258                 'b_browse.png',
1259                 __('Browse foreign values')
1260             )
1261         );
1262         $html_output .= '<form method="post" action="tbl_zoom_select.php"'
1263             . ' name="displayResultForm" id="zoom_display_form"'
1264             . ' class="ajax"' . '>';
1265         $html_output .= PMA_URL_getHiddenInputs($this->_db, $this->_table);
1266         $html_output .= '<input type="hidden" name="goto" value="' . $goto . '" />';
1267         $html_output
1268             .= '<input type="hidden" name="back" value="tbl_zoom_select.php" />';
1269
1270         $html_output .= '<fieldset id="displaySection">';
1271         $html_output .= '<legend>' . __('Browse/Edit the points') . '</legend>';
1272
1273         //JSON encode the data(query result)
1274         $html_output .= '<center>';
1275         if (isset($_POST['zoom_submit']) && ! empty($data)) {
1276             $html_output .= '<div id="resizer">';
1277             $html_output .= '<center><a href="#" onclick="displayHelp();">'
1278                 . __('How to use') . '</a></center>';
1279             $html_output .= '<div id="querydata" style="display:none">'
1280                 . json_encode($data) . '</div>';
1281             $html_output .= '<div id="querychart"></div>';
1282             $html_output .= '<button class="button-reset">'
1283                 . __('Reset zoom') . '</button>';
1284             $html_output .= '</div>';
1285         }
1286         $html_output .= '</center>';
1287
1288         //Displays rows in point edit form
1289         $html_output .= '<div id="dataDisplay" style="display:none">';
1290         $html_output .= '<table><thead>';
1291         $html_output .= '<tr>';
1292         $html_output .= '<th>' . __('Column') . '</th>'
1293             . '<th>' . __('Null') . '</th>'
1294             . '<th>' . __('Value') . '</th>';
1295         $html_output .= '</tr>';
1296         $html_output .= '</thead>';
1297
1298         $html_output .= '<tbody>';
1299         $odd_row = true;
1300         for (
1301             $column_index = 0, $nb = count($this->_columnNames);
1302             $column_index < $nb;
1303             $column_index++
1304         ) {
1305             $fieldpopup = $this->_columnNames[$column_index];
1306             $foreignData = PMA_getForeignData(
1307                 $this->_foreigners,
1308                 $fieldpopup,
1309                 false,
1310                 '',
1311                 ''
1312             );
1313             $html_output
1314                 .= '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">';
1315             $odd_row = ! $odd_row;
1316             //Display column Names
1317             $html_output
1318                 .= '<th>' . htmlspecialchars($this->_columnNames[$column_index])
1319                 . '</th>';
1320             //Null checkbox if column can be null
1321             $html_output .= '<th>'
1322                 . (($this->_columnNullFlags[$column_index] == 'YES')
1323                 ? '<input type="checkbox" class="checkbox_null"'
1324                     . ' name="criteriaColumnNullFlags[' . $column_index . ']"'
1325                     . ' id="edit_fields_null_id_' . $column_index . '" />'
1326                 : '');
1327             $html_output .= '</th>';
1328             //Column's Input box
1329             $html_output .= '<th>';
1330             $html_output .= $this->_getInputbox(
1331                 $foreignData, $fieldpopup, $this->_columnTypes[$column_index],
1332                 $column_index, $titles, $GLOBALS['cfg']['ForeignKeyMaxLimit'],
1333                 '', false, true
1334             );
1335             $html_output .= '</th></tr>';
1336         }
1337         $html_output .= '</tbody></table>';
1338         $html_output .= '</div>';
1339         $html_output .= '<input type="hidden" id="queryID" name="sql_query" />';
1340         $html_output .= '</form>';
1341         return $html_output;
1342     }
1343
1344     /**
1345      * Displays the 'Find and replace' form
1346      *
1347      * @return string HTML for 'Find and replace' form
1348      */
1349     function _getSearchAndReplaceHTML()
1350     {
1351         $htmlOutput  = __('Find:')
1352             . '<input type="text" value="" name="find" required />';
1353         $htmlOutput .= __('Replace with:')
1354             . '<input type="text" value="" name="replaceWith" required />';
1355
1356         $htmlOutput .= __('Column:') . '<select name="columnIndex">';
1357         for ($i = 0, $nb = count($this->_columnNames); $i < $nb; $i++) {
1358             $type = preg_replace('@\(.*@s', '', $this->_columnTypes[$i]);
1359             if ($GLOBALS['PMA_Types']->getTypeClass($type) == 'CHAR') {
1360                 $column = $this->_columnNames[$i];
1361                 $htmlOutput .= '<option value="' . $i . '">'
1362                     . htmlspecialchars($column) . '</option>';
1363             }
1364         }
1365         $htmlOutput .= '</select>';
1366
1367         $htmlOutput .= '<br>'
1368             . PMA_Util::getCheckbox(
1369                 'useRegex',
1370                 __('Use regular expression'),
1371                 false,
1372                 false,
1373                 'useRegex'
1374             );
1375         return $htmlOutput;
1376     }
1377
1378     /**
1379      * Finds and returns Regex pattern and their replacements
1380      *
1381      * @param int    $columnIndex index of the column
1382      * @param string $find        string to find in the column
1383      * @param string $replaceWith string to replace with
1384      * @param string $charSet     character set of the connection
1385      *
1386      * @return array Array containing original values, replaced values and count
1387      */
1388     function _getRegexReplaceRows($columnIndex, $find, $replaceWith, $charSet)
1389     {
1390         $column = $this->_columnNames[$columnIndex];
1391         $sql_query = "SELECT "
1392             . PMA_Util::backquote($column) . ","
1393             . " 1," // to add an extra column that will have replaced value
1394             . " COUNT(*)"
1395             . " FROM " . PMA_Util::backquote($this->_db)
1396             . "." . PMA_Util::backquote($this->_table)
1397             . " WHERE " . PMA_Util::backquote($column)
1398             . " RLIKE '" . PMA_Util::sqlAddSlashes($find) . "' COLLATE "
1399             . $charSet . "_bin"; // here we
1400             // change the collation of the 2nd operand to a case sensitive
1401             // binary collation to make sure that the comparison is case sensitive
1402         $sql_query .= " GROUP BY " . PMA_Util::backquote($column)
1403             . " ORDER BY " . PMA_Util::backquote($column) . " ASC";
1404
1405         $result = $GLOBALS['dbi']->fetchResult($sql_query, 0);
1406
1407         if (is_array($result)) {
1408             foreach ($result as $index=>$row) {
1409                 $result[$index][1] = preg_replace(
1410                     "/" . $find . "/",
1411                     $replaceWith,
1412                     $row[0]
1413                 );
1414             }
1415         }
1416         return $result;
1417     }
1418
1419     /**
1420      * Returns HTML for previewing strings found and their replacements
1421      *
1422      * @param int     $columnIndex index of the column
1423      * @param string  $find        string to find in the column
1424      * @param string  $replaceWith string to replace with
1425      * @param boolean $useRegex    to use Regex replace or not
1426      * @param string  $charSet     character set of the connection
1427      *
1428      * @return string HTML for previewing strings found and their replacements
1429      */
1430     function getReplacePreview($columnIndex, $find, $replaceWith, $useRegex,
1431         $charSet
1432     ) {
1433         $column = $this->_columnNames[$columnIndex];
1434         if ($useRegex) {
1435             $result = $this->_getRegexReplaceRows(
1436                 $columnIndex, $find, $replaceWith, $charSet
1437             );
1438         } else {
1439             $sql_query = "SELECT "
1440                 . PMA_Util::backquote($column) . ","
1441                 . " REPLACE("
1442                 . PMA_Util::backquote($column) . ", '" . $find . "', '"
1443                 . $replaceWith
1444                 . "'),"
1445                 . " COUNT(*)"
1446                 . " FROM " . PMA_Util::backquote($this->_db)
1447                 . "." . PMA_Util::backquote($this->_table)
1448                 . " WHERE " . PMA_Util::backquote($column)
1449                 . " LIKE '%" . $find . "%' COLLATE " . $charSet . "_bin"; // here we
1450                 // change the collation of the 2nd operand to a case sensitive
1451                 // binary collation to make sure that the comparison
1452                 // is case sensitive
1453             $sql_query .= " GROUP BY " . PMA_Util::backquote($column)
1454                 . " ORDER BY " . PMA_Util::backquote($column) . " ASC";
1455
1456             $result = $GLOBALS['dbi']->fetchResult($sql_query, 0);
1457         }
1458
1459         $htmlOutput = '<form method="post" action="tbl_find_replace.php"'
1460             . ' name="previewForm" id="previewForm" class="ajax">';
1461         $htmlOutput .= PMA_URL_getHiddenInputs($this->_db, $this->_table);
1462         $htmlOutput .= '<input type="hidden" name="replace" value="true" />';
1463         $htmlOutput .= '<input type="hidden" name="columnIndex" value="'
1464             . $columnIndex . '" />';
1465         $htmlOutput .= '<input type="hidden" name="findString"'
1466             . ' value="' . htmlspecialchars($find) . '" />';
1467         $htmlOutput .= '<input type="hidden" name="replaceWith"'
1468             . ' value="' . htmlspecialchars($replaceWith) . '" />';
1469         $htmlOutput .= '<input type="hidden" name="useRegex"'
1470             . ' value="' . $useRegex . '" />';
1471
1472         $htmlOutput .= '<fieldset id="fieldset_find_replace_preview">';
1473         $htmlOutput .= '<legend>' . __('Find and replace - preview') . '</legend>';
1474
1475         $htmlOutput .= '<table id="previewTable">'
1476             . '<thead><tr>'
1477             . '<th>' . __('Count') . '</th>'
1478             . '<th>' . __('Original string') . '</th>'
1479             . '<th>' . __('Replaced string') . '</th>'
1480             . '</tr></thead>';
1481
1482         $htmlOutput .= '<tbody>';
1483         $odd = true;
1484         if (is_array($result)) {
1485             foreach ($result as $row) {
1486                 $val = $row[0];
1487                 $replaced = $row[1];
1488                 $count = $row[2];
1489
1490                 $htmlOutput .= '<tr class="' . ($odd ? 'odd' : 'even') . '">';
1491                 $htmlOutput .= '<td class="right">' . htmlspecialchars($count)
1492                     . '</td>';
1493                 $htmlOutput .= '<td>' . htmlspecialchars($val) . '</td>';
1494                 $htmlOutput .= '<td>' . htmlspecialchars($replaced) . '</td>';
1495                 $htmlOutput .= '</tr>';
1496
1497                 $odd = ! $odd;
1498             }
1499         }
1500         $htmlOutput .= '</tbody>';
1501         $htmlOutput .= '</table>';
1502         $htmlOutput .= '</fieldset>';
1503
1504         $htmlOutput .= '<fieldset class="tblFooters">';
1505         $htmlOutput .= '<input type="submit" name="replace"'
1506             . ' value="' . __('Replace') . '" />';
1507         $htmlOutput .= '</fieldset>';
1508
1509         $htmlOutput .= '</form>';
1510         return $htmlOutput;
1511     }
1512
1513     /**
1514      * Replaces a given string in a column with a give replacement
1515      *
1516      * @param int     $columnIndex index of the column
1517      * @param string  $find        string to find in the column
1518      * @param string  $replaceWith string to replace with
1519      * @param boolean $useRegex    to use Regex replace or not
1520      * @param string  $charSet     character set of the connection
1521      *
1522      * @return void
1523      */
1524     function replace($columnIndex, $find, $replaceWith, $useRegex, $charSet)
1525     {
1526         $column = $this->_columnNames[$columnIndex];
1527         if ($useRegex) {
1528             $toReplace = $this->_getRegexReplaceRows(
1529                 $columnIndex, $find, $replaceWith, $charSet
1530             );
1531             $sql_query = "UPDATE " . PMA_Util::backquote($this->_db)
1532                 . "." . PMA_Util::backquote($this->_table)
1533                 . " SET " . PMA_Util::backquote($column) . " = CASE";
1534             if (is_array($toReplace)) {
1535                 foreach ($toReplace as $row) {
1536                     $sql_query .= "\n WHEN " . PMA_Util::backquote($column)
1537                         . " = '" . PMA_Util::sqlAddSlashes($row[0])
1538                         . "' THEN '" . PMA_Util::sqlAddSlashes($row[1]) . "'";
1539                 }
1540             }
1541             $sql_query .= " END"
1542                 . " WHERE " . PMA_Util::backquote($column)
1543                 . " RLIKE '" . PMA_Util::sqlAddSlashes($find) . "' COLLATE "
1544                 . $charSet . "_bin"; // here we
1545                 // change the collation of the 2nd operand to a case sensitive
1546                 // binary collation to make sure that the comparison
1547                 // is case sensitive
1548         } else {
1549             $sql_query = "UPDATE " . PMA_Util::backquote($this->_db)
1550                 . "." . PMA_Util::backquote($this->_table)
1551                 . " SET " . PMA_Util::backquote($column) . " ="
1552                 . " REPLACE("
1553                 . PMA_Util::backquote($column) . ", '" . $find . "', '"
1554                 . $replaceWith
1555                 . "')"
1556                 . " WHERE " . PMA_Util::backquote($column)
1557                 . " LIKE '%" . $find . "%' COLLATE " . $charSet . "_bin"; // here we
1558                 // change the collation of the 2nd operand to a case sensitive
1559                 // binary collation to make sure that the comparison
1560                 // is case sensitive
1561         }
1562         $GLOBALS['dbi']->query(
1563             $sql_query, null, PMA_DatabaseInterface::QUERY_STORE
1564         );
1565         $GLOBALS['sql_query'] = $sql_query;
1566     }
1567
1568     /**
1569      * Finds minimum and maximum value of a given column.
1570      *
1571      * @param string $column Column name
1572      *
1573      * @return array
1574      */
1575     public function getColumnMinMax($column)
1576     {
1577         $sql_query = 'SELECT MIN(' . PMA_Util::backquote($column) . ') AS `min`, '
1578             . 'MAX(' . PMA_Util::backquote($column) . ') AS `max` '
1579             . 'FROM ' . PMA_Util::backquote($this->_db) . '.'
1580             . PMA_Util::backquote($this->_table);
1581
1582         $result = $GLOBALS['dbi']->fetchSingleRow($sql_query);
1583
1584         return $result;
1585     }
1586 }
1587 ?>