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 fucntions
 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 = 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 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 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      * @param bool   $in_zoom_search_edit Whether we are in zoom search edit
 262      *
 263      * @return HTML elements.
 264      */
 265     private function _getForeignKeyInputBox($foreignData, $column_name,
 266         $column_index, $titles, $foreignMaxLimit, $criteriaValues, $column_id,
 267         $in_zoom_search_edit = false
 268     ) {
 269         $html_output = '';
 270         if (is_array($foreignData['disp_row'])) {
 271             $html_output .=  '<select name="criteriaValues[' . $column_index . ']"'
 272                 . ' id="' . $column_id . $column_index .'">';
 273             $html_output .= PMA_foreignDropdown(
 274                 $foreignData['disp_row'], $foreignData['foreign_field'],
 275                 $foreignData['foreign_display'], '', $foreignMaxLimit
 276             );
 277             $html_output .= '</select>';
 278
 279         } elseif ($foreignData['foreign_link'] == true) {
 280             $html_output .= '<input type="text" id="' . $column_id
 281                 . $column_index . '"'
 282                 . ' name="criteriaValues[' . $column_index . ']" id="field_'
 283                 . md5($column_name) . '[' . $column_index .']" class="textfield"'
 284                 . (isset($criteriaValues[$column_index])
 285                     && is_string($criteriaValues[$column_index])
 286                     ? (' value="' . $criteriaValues[$column_index] . '"')
 287                     : '')
 288                 . ' />';
 289
 290             $html_output .=  <<<EOT
 291 <a target="_blank" onclick="window.open(this.href, 'foreigners', 'width=640,height=240,scrollbars=yes'); return false" href="browse_foreigners.php?
 292 EOT;
 293             $html_output .= '' . PMA_URL_getCommon($this->_db, $this->_table)
 294                 . '&amp;field=' . urlencode($column_name) . '&amp;fieldkey='
 295                 . $column_index . '&amp;fromsearch=1"';
 296             if ($in_zoom_search_edit) {
 297                 $html_output .= ' class="browse_foreign"';
 298             }
 299             $html_output .= '>' . str_replace("'", "\'", $titles['Browse']) . '</a>';
 300         }
 301         return $html_output;
 302     }
 303
 304     /**
 305      * Provides html elements for search criteria inputbox
 306      * in case the column is of ENUM or SET type
 307      *
 308      * @param int    $column_index        Column index
 309      * @param array  $criteriaValues      Array of search criteria inputs
 310      * @param string $column_type         Column type
 311      * @param string $column_id           Column's inputbox's id
 312      * @param bool   $in_zoom_search_edit Whether we are in zoom search edit
 313      *
 314      * @return HTML elements.
 315      */
 316     private function _getEnumSetInputBox($column_index, $criteriaValues,
 317         $column_type, $column_id, $in_zoom_search_edit = false
 318     ) {
 319         $html_output = '';
 320         $value = explode(
 321             ', ',
 322             str_replace("'", '', substr($column_type, 5, -1))
 323         );
 324         $cnt_value = count($value);
 325
 326         /*
 327          * Enum in edit mode   --> dropdown
 328          * Enum in search mode --> multiselect
 329          * Set in edit mode    --> multiselect
 330          * Set in search mode  --> input (skipped here, so the 'else'
 331          *                                 section would handle it)
 332          */
 333         if ((strncasecmp($column_type, 'enum', 4) && ! $in_zoom_search_edit)
 334             || (strncasecmp($column_type, 'set', 3) && $in_zoom_search_edit)
 335         ) {
 336             $html_output .= '<select name="criteriaValues[' . ($column_index)
 337                 . ']" id="' . $column_id . $column_index .'">';
 338         } else {
 339             $html_output .= '<select name="criteriaValues[' . $column_index . ']"'
 340                 . ' id="' . $column_id . $column_index . '" multiple="multiple"'
 341                 . ' size="' . min(3, $cnt_value) . '">';
 342         }
 343
 344         //Add select options
 345         for ($j = 0; $j < $cnt_value; $j++) {
 346             if (isset($criteriaValues[$column_index])
 347                 && is_array($criteriaValues[$column_index])
 348                 && in_array($value[$j], $criteriaValues[$column_index])
 349             ) {
 350                 $html_output .= '<option value="' . $value[$j] . '" Selected>'
 351                     . $value[$j] . '</option>';
 352             } else {
 353                 $html_output .= '<option value="' . $value[$j] . '">'
 354                     . $value[$j] . '</option>';
 355             }
 356         } // end for
 357         $html_output .= '</select>';
 358         return $html_output;
 359     }
 360
 361     /**
 362      * Creates the HTML content for:
 363      * 1) Browsing foreign data for a column.
 364      * 2) Creating elements for search criteria input on columns.
 365      *
 366      * @param array  $foreignData         Foreign keys data
 367      * @param string $column_name         Column name
 368      * @param string $column_type         Column type
 369      * @param int    $column_index        Column index
 370      * @param array  $titles              Selected title
 371      * @param int    $foreignMaxLimit     Max limit of displaying foreign elements
 372      * @param array  $criteriaValues      Array of search criteria inputs
 373      * @param bool   $in_fbs              Whether we are in 'function based search'
 374      * @param bool   $in_zoom_search_edit Whether we are in zoom search edit
 375      *
 376      * @return string HTML content for viewing foreign data and elements
 377      * for search criteria input.
 378      */
 379     private function _getInputbox($foreignData, $column_name, $column_type,
 380         $column_index, $titles, $foreignMaxLimit, $criteriaValues, $in_fbs = false,
 381         $in_zoom_search_edit = false
 382     ) {
 383         $str = '';
 384         $column_type = (string)$column_type;
 385         $column_id = ($in_zoom_search_edit) ? 'edit_fieldID_' : 'fieldID_';
 386
 387         // Get inputbox based on different column types
 388         // (Foreign key, geometrical, enum)
 389         if ($this->_foreigners && isset($this->_foreigners[$column_name])) {
 390             $str .= $this->_getForeignKeyInputBox(
 391                 $foreignData, $column_name, $column_index, $titles,
 392                 $foreignMaxLimit, $criteriaValues, $column_id
 393             );
 394
 395         } elseif (in_array($column_type, PMA_Util::getGISDatatypes())) {
 396             $str .= $this->_getGeometricalInputBox($column_index, $in_fbs);
 397
 398         } elseif (strncasecmp($column_type, 'enum', 4) == 0
 399             || (strncasecmp($column_type, 'set', 3) == 0 && $in_zoom_search_edit)
 400         ) {
 401             $str .= $this->_getEnumSetInputBox(
 402                 $column_index, $criteriaValues, $column_type, $column_id,
 403                 $in_zoom_search_edit = false
 404             );
 405
 406         } else {
 407             // other cases
 408             $the_class = 'textfield';
 409
 410             if ($column_type == 'date') {
 411                 $the_class .= ' datefield';
 412             } elseif ($column_type == 'datetime'
 413                 || substr($column_type, 0, 9) == 'timestamp'
 414             ) {
 415                 $the_class .= ' datetimefield';
 416             } elseif (substr($column_type, 0, 3) == 'bit') {
 417                 $the_class .= ' bit';
 418             }
 419
 420             $str .= '<input type="text" name="criteriaValues[' . $column_index . ']"'
 421                 .' size="40" class="' . $the_class . '" id="'
 422                 . $column_id . $column_index . '"'
 423                 . (isset($criteriaValues[$column_index])
 424                     && is_string($criteriaValues[$column_index])
 425                     ? (' value="' . $criteriaValues[$column_index] . '"')
 426                     : '')
 427                 . ' />';
 428         }
 429         return $str;
 430     }
 431
 432     /**
 433      * Return the where clause in case column's type is ENUM.
 434      *
 435      * @param mixed  $criteriaValues Search criteria input
 436      * @param string $func_type      Search function/operator
 437      *
 438      * @return string part of where clause.
 439      */
 440     private function _getEnumWhereClause($criteriaValues, $func_type)
 441     {
 442         if (! is_array($criteriaValues)) {
 443             $criteriaValues = explode(',', $criteriaValues);
 444         }
 445         $enum_selected_count = count($criteriaValues);
 446         if ($func_type == '=' && $enum_selected_count > 1) {
 447             $func_type    = 'IN';
 448             $parens_open  = '(';
 449             $parens_close = ')';
 450
 451         } elseif ($func_type == '!=' && $enum_selected_count > 1) {
 452             $func_type    = 'NOT IN';
 453             $parens_open  = '(';
 454             $parens_close = ')';
 455
 456         } else {
 457             $parens_open  = '';
 458             $parens_close = '';
 459         }
 460         $enum_where = '\''
 461             . PMA_Util::sqlAddSlashes($criteriaValues[0]) . '\'';
 462         for ($e = 1; $e < $enum_selected_count; $e++) {
 463             $enum_where .= ', \''
 464                 . PMA_Util::sqlAddSlashes($criteriaValues[$e]) . '\'';
 465         }
 466
 467         return ' ' . $func_type . ' ' . $parens_open
 468             . $enum_where . $parens_close;
 469     }
 470
 471     /**
 472      * Return the where clause for a geometrical column.
 473      *
 474      * @param mixed  $criteriaValues Search criteria input
 475      * @param string $names          Name of the column on which search is submitted
 476      * @param string $func_type      Search function/operator
 477      * @param string $types          Type of the field
 478      * @param bool   $geom_func      Whether geometry functions should be applied
 479      *
 480      * @return string part of where clause.
 481      */
 482     private function _getGeomWhereClause($criteriaValues, $names,
 483         $func_type, $types, $geom_func = null
 484     ) {
 485         $geom_unary_functions = array(
 486             'IsEmpty' => 1,
 487             'IsSimple' => 1,
 488             'IsRing' => 1,
 489             'IsClosed' => 1,
 490         );
 491         $where = '';
 492
 493         // Get details about the geometry functions
 494         $geom_funcs = PMA_Util::getGISFunctions($types, true, false);
 495         // New output type is the output type of the function being applied
 496         $types = $geom_funcs[$geom_func]['type'];
 497
 498         // If the function takes a single parameter
 499         if ($geom_funcs[$geom_func]['params'] == 1) {
 500             $backquoted_name = $geom_func . '(' . PMA_Util::backquote($names) . ')';
 501         } else {
 502             // If the function takes two parameters
 503             // create gis data from the criteria input
 504             $gis_data = PMA_Util::createGISData($criteriaValues);
 505             $where = $geom_func . '(' . PMA_Util::backquote($names)
 506                 . ',' . $gis_data . ')';
 507             return $where;
 508         }
 509
 510         // If the where clause is something like 'IsEmpty(`spatial_col_name`)'
 511         if (isset($geom_unary_functions[$geom_func])
 512             && trim($criteriaValues) == ''
 513         ) {
 514             $where = $backquoted_name;
 515
 516         } elseif (in_array($types, PMA_Util::getGISDatatypes())
 517             && ! empty($criteriaValues)
 518         ) {
 519             // create gis data from the criteria input
 520             $gis_data = PMA_Util::createGISData($criteriaValues);
 521             $where = $backquoted_name . ' ' . $func_type . ' ' . $gis_data;
 522         }
 523         return $where;
 524     }
 525
 526     /**
 527      * Return the where clause for query generation based on the inputs provided.
 528      *
 529      * @param mixed  $criteriaValues Search criteria input
 530      * @param string $names          Name of the column on which search is submitted
 531      * @param string $types          Type of the field
 532      * @param string $collations     Field collation
 533      * @param string $func_type      Search function/operator
 534      * @param bool   $unaryFlag      Whether operator unary or not
 535      * @param bool   $geom_func      Whether geometry functions should be applied
 536      *
 537      * @return string generated where clause.
 538      */
 539     private function _getWhereClause($criteriaValues, $names, $types, $collations,
 540         $func_type, $unaryFlag, $geom_func = null
 541     ) {
 542         // If geometry function is set
 543         if ($geom_func != null && trim($geom_func) != '') {
 544             return $this->_getGeomWhereClause(
 545                 $criteriaValues, $names, $func_type, $types, $geom_func
 546             );
 547         }
 548
 549         $backquoted_name = PMA_Util::backquote($names);
 550         $where = '';
 551         if ($unaryFlag) {
 552             $criteriaValues = '';
 553             $where = $backquoted_name . ' ' . $func_type;
 554
 555         } elseif (strncasecmp($types, 'enum', 4) == 0 && ! empty($criteriaValues)) {
 556             $where = $backquoted_name;
 557             $where .= $this->_getEnumWhereClause($criteriaValues, $func_type);
 558
 559         } elseif ($criteriaValues != '') {
 560             // For these types we quote the value. Even if it's another type
 561             // (like INT), for a LIKE we always quote the value. MySQL converts
 562             // strings to numbers and numbers to strings as necessary
 563             // during the comparison
 564             if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types)
 565                 || strpos(' ' . $func_type, 'LIKE')
 566             ) {
 567                 $quot = '\'';
 568             } else {
 569                 $quot = '';
 570             }
 571
 572             // LIKE %...%
 573             if ($func_type == 'LIKE %...%') {
 574                 $func_type = 'LIKE';
 575                 $criteriaValues = '%' . $criteriaValues . '%';
 576             }
 577             if ($func_type == 'REGEXP ^...$') {
 578                 $func_type = 'REGEXP';
 579                 $criteriaValues = '^' . $criteriaValues . '$';
 580             }
 581
 582             if ($func_type == 'IN (...)'
 583                 || $func_type == 'NOT IN (...)'
 584                 || $func_type == 'BETWEEN'
 585                 || $func_type == 'NOT BETWEEN'
 586             ) {
 587                 $func_type = str_replace(' (...)', '', $func_type);
 588
 589                 // quote values one by one
 590                 $values = explode(',', $criteriaValues);
 591                 foreach ($values as &$value) {
 592                     $value = $quot . PMA_Util::sqlAddSlashes(trim($value))
 593                         . $quot;
 594                 }
 595
 596                 if ($func_type == 'BETWEEN' || $func_type == 'NOT BETWEEN') {
 597                     $where = $backquoted_name . ' ' . $func_type . ' '
 598                         . (isset($values[0]) ? $values[0] : '')
 599                         . ' AND ' . (isset($values[1]) ? $values[1] : '');
 600                 } else {
 601                     $where = $backquoted_name . ' ' . $func_type
 602                         . ' (' . implode(',', $values) . ')';
 603                 }
 604             } else {
 605                 if ($func_type == 'LIKE %...%' || $func_type == 'LIKE') {
 606                     $where = $backquoted_name . ' ' . $func_type . ' ' . $quot
 607                         . PMA_Util::sqlAddSlashes($criteriaValues, true) . $quot;
 608                 } else {
 609                     $where = $backquoted_name . ' ' . $func_type . ' ' . $quot
 610                         . PMA_Util::sqlAddSlashes($criteriaValues) . $quot;
 611                 }
 612             }
 613         } // end if
 614
 615         return $where;
 616     }
 617
 618     /**
 619      * Builds the sql search query from the post parameters
 620      *
 621      * @return string the generated SQL query
 622      */
 623     public function buildSqlQuery()
 624     {
 625         $sql_query = 'SELECT ';
 626
 627         // If only distinct values are needed
 628         $is_distinct = (isset($_POST['distinct'])) ? 'true' : 'false';
 629         if ($is_distinct == 'true') {
 630             $sql_query .= 'DISTINCT ';
 631         }
 632
 633         // if all column names were selected to display, we do a 'SELECT *'
 634         // (more efficient and this helps prevent a problem in IE
 635         // if one of the rows is edited and we come back to the Select results)
 636         if (isset($_POST['zoom_submit']) || ! empty($_POST['displayAllColumns'])) {
 637             $sql_query .= '* ';
 638         } else {
 639             $sql_query .= implode(
 640                 ', ',
 641                 PMA_Util::backquote($_POST['columnsToDisplay'])
 642             );
 643         } // end if
 644
 645         $sql_query .= ' FROM '
 646             . PMA_Util::backquote($_POST['table']);
 647         $whereClause = $this->_generateWhereClause();
 648         $sql_query .= $whereClause;
 649
 650         // if the search results are to be ordered
 651         if (isset($_POST['orderByColumn']) && $_POST['orderByColumn'] != '--nil--') {
 652             $sql_query .= ' ORDER BY '
 653                 . PMA_Util::backquote($_POST['orderByColumn'])
 654                 . ' ' . $_POST['order'];
 655         } // end if
 656         return $sql_query;
 657     }
 658
 659     /**
 660      * Generates the where clause for the SQL search query to be executed
 661      *
 662      * @return string the generated where clause
 663      */
 664     private function _generateWhereClause()
 665     {
 666         if (isset($_POST['customWhereClause'])
 667             && trim($_POST['customWhereClause']) != ''
 668         ) {
 669             return ' WHERE ' . $_POST['customWhereClause'];
 670         }
 671
 672         // If there are no search criteria set or no unary criteria operators,
 673         // return
 674         if (! isset($_POST['criteriaValues'])
 675             && ! isset($_POST['criteriaColumnOperators'])
 676         ) {
 677             return '';
 678         }
 679
 680         // else continue to form the where clause from column criteria values
 681         $fullWhereClause = $charsets = array();
 682         reset($_POST['criteriaColumnOperators']);
 683         while (list($column_index, $operator) = each(
 684             $_POST['criteriaColumnOperators']
 685         )) {
 686             list($charsets[$column_index]) = explode(
 687                 '_', $_POST['criteriaColumnCollations'][$column_index]
 688             );
 689             $unaryFlag =  $GLOBALS['PMA_Types']->isUnaryOperator($operator);
 690             $tmp_geom_func = isset($geom_func[$column_index])
 691                 ? $geom_func[$column_index] : null;
 692
 693             $whereClause = $this->_getWhereClause(
 694                 $_POST['criteriaValues'][$column_index],
 695                 $_POST['criteriaColumnNames'][$column_index],
 696                 $_POST['criteriaColumnTypes'][$column_index],
 697                 $_POST['criteriaColumnCollations'][$column_index],
 698                 $operator,
 699                 $unaryFlag,
 700                 $tmp_geom_func
 701             );
 702
 703             if ($whereClause) {
 704                 $fullWhereClause[] = $whereClause;
 705             }
 706         } // end while
 707
 708         if ($fullWhereClause) {
 709             return ' WHERE ' . implode(' AND ', $fullWhereClause);
 710         }
 711         return '';
 712     }
 713
 714     /**
 715      * Generates HTML for a geometrical function column to be displayed in table
 716      * search selection form
 717      *
 718      * @param integer $column_index index of current column in $columnTypes array
 719      *
 720      * @return string the generated HTML
 721      */
 722     private function _getGeomFuncHtml($column_index)
 723     {
 724         $html_output = '';
 725         // return if geometrical column is not present
 726         if (! $this->_geomColumnFlag) {
 727             return $html_output;
 728         }
 729
 730         /**
 731          * Displays 'Function' column if it is present
 732          */
 733         $html_output .= '<td>';
 734         $geom_types = PMA_Util::getGISDatatypes();
 735         // if a geometry column is present
 736         if (in_array($this->_columnTypes[$column_index], $geom_types)) {
 737             $html_output .= '<select class="geom_func" name="geom_func['
 738                 . $column_index . ']">';
 739             // get the relevant list of GIS functions
 740             $funcs = PMA_Util::getGISFunctions(
 741                 $this->_columnTypes[$column_index], true, true
 742             );
 743             /**
 744              * For each function in the list of functions,
 745              * add an option to select list
 746              */
 747             foreach ($funcs as $func_name => $func) {
 748                 $name = isset($func['display']) ? $func['display'] : $func_name;
 749                 $html_output .= '<option value="' . htmlspecialchars($name) . '">'
 750                         . htmlspecialchars($name) . '</option>';
 751             }
 752             $html_output .= '</select>';
 753         } else {
 754             $html_output .= '&nbsp;';
 755         }
 756         $html_output .= '</td>';
 757         return $html_output;
 758     }
 759
 760     /**
 761      * Generates formatted HTML for extra search options in table search form
 762      *
 763      * @return string the generated HTML
 764      */
 765     private function _getOptions()
 766     {
 767         $html_output = '';
 768         $html_output .= PMA_Util::getDivForSliderEffect(
 769             'searchoptions', __('Options')
 770         );
 771
 772         /**
 773          * Displays columns select list for selecting distinct columns in the search
 774          */
 775         $html_output .= '<fieldset id="fieldset_select_fields">'
 776             . '<legend>' . __('Select columns (at least one):') . '</legend>'
 777             . '<select name="columnsToDisplay[]"'
 778             . ' size="' . min(count($this->_columnNames), 10) . '"'
 779             . ' multiple="multiple">';
 780         // Displays the list of the fields
 781         foreach ($this->_columnNames as $each_field) {
 782             $html_output .= '        '
 783                 . '<option value="' . htmlspecialchars($each_field) . '"'
 784                 . ' selected="selected">' . htmlspecialchars($each_field)
 785                 . '</option>' . "\n";
 786         } // end for
 787         $html_output .= '</select>'
 788             . '<input type="checkbox" name="distinct" value="DISTINCT"'
 789             . ' id="oDistinct" />'
 790             . '<label for="oDistinct">DISTINCT</label></fieldset>';
 791
 792         /**
 793          * Displays input box for custom 'Where' clause to be used in the search
 794          */
 795         $html_output .= '<fieldset id="fieldset_search_conditions">'
 796             . '<legend>' . '<em>' . __('Or') . '</em> '
 797             . __('Add search conditions (body of the "where" clause):') . '</legend>';
 798         $html_output .= PMA_Util::showMySQLDocu('Functions');
 799         $html_output .= '<input type="text" name="customWhereClause"'
 800             . ' class="textfield" size="64" />';
 801         $html_output .= '</fieldset>';
 802
 803         /**
 804          * Displays option of changing default number of rows displayed per page
 805          */
 806         $html_output .= '<fieldset id="fieldset_limit_rows">'
 807             . '<legend>' . __('Number of rows per page') . '</legend>'
 808             . '<input type="number" size="4" name="session_max_rows" required '
 809             . 'min="1" '
 810             . 'value="' . $GLOBALS['cfg']['MaxRows'] . '" class="textfield" />'
 811             . '</fieldset>';
 812
 813         /**
 814          * Displays option for ordering search results
 815          * by a column value (Asc or Desc)
 816          */
 817         $html_output .= '<fieldset id="fieldset_display_order">'
 818             . '<legend>' . __('Display order:') . '</legend>'
 819             . '<select name="orderByColumn"><option value="--nil--"></option>';
 820         foreach ($this->_columnNames as $each_field) {
 821             $html_output .= '        '
 822                 . '<option value="' . htmlspecialchars($each_field) . '">'
 823                 . htmlspecialchars($each_field) . '</option>' . "\n";
 824         } // end for
 825         $html_output .= '</select>';
 826         $choices = array(
 827             'ASC' => __('Ascending'),
 828             'DESC' => __('Descending')
 829         );
 830         $html_output .= PMA_Util::getRadioFields(
 831             'order', $choices, 'ASC', false, true, "formelement"
 832         );
 833         unset($choices);
 834
 835         $html_output .= '</fieldset><br style="clear: both;"/></div>';
 836         return $html_output;
 837     }
 838
 839     /**
 840      * Other search criteria like data label
 841      * (for tbl_zoom_select.php)
 842      *
 843      * @param array $dataLabel Label for points in zoom plot
 844      *
 845      * @return string the generated html
 846      */
 847     private function _getOptionsZoom($dataLabel)
 848     {
 849         $html_output = '';
 850         $html_output .= '<table class="data">';
 851         //Select options for datalabel
 852         $html_output .= '<tr>';
 853         $html_output .= '<td><label for="dataLabel">'
 854             . __("Use this column to label each point") . '</label></td>';
 855         $html_output .= '<td><select name="dataLabel" id="dataLabel" >'
 856             . '<option value = "">' . __('None') . '</option>';
 857         for ($j = 0; $j < count($this->_columnNames); $j++) {
 858             if (isset($dataLabel)
 859                 && $dataLabel == htmlspecialchars($this->_columnNames[$j])
 860             ) {
 861                 $html_output .= '<option value="'
 862                     . htmlspecialchars($this->_columnNames[$j])
 863                     . '" selected="selected">'
 864                     . htmlspecialchars($this->_columnNames[$j])
 865                     . '</option>';
 866             } else {
 867                 $html_output .= '<option value="'
 868                     . htmlspecialchars($this->_columnNames[$j]) . '" >'
 869                     . htmlspecialchars($this->_columnNames[$j]) . '</option>';
 870             }
 871         }
 872         $html_output .= '</select></td>';
 873         $html_output .= '</tr>';
 874         //Inputbox for changing default maximum rows to plot
 875         $html_output .= '<tr>';
 876         $html_output .= '<td><label for="maxRowPlotLimit">'
 877             . __("Maximum rows to plot") . '</label></td>';
 878         $html_output .= '<td>';
 879         $html_output .= '<input type="number" name="maxPlotLimit"'
 880             . ' id="maxRowPlotLimit" required'
 881             . ' value="' . ((! empty($_POST['maxPlotLimit']))
 882                 ? htmlspecialchars($_POST['maxPlotLimit'])
 883                 : $GLOBALS['cfg']['maxRowPlotLimit'])
 884             . '" />';
 885         $html_output .= '</td></tr>';
 886         $html_output .= '</table>';
 887         return $html_output;
 888     }
 889
 890     /**
 891      * Provides a column's type, collation, operators list, and crietria value
 892      * to display in table search form
 893      *
 894      * @param integer $search_index Row number in table search form
 895      * @param integer $column_index Column index in ColumnNames array
 896      *
 897      * @return array Array contaning column's properties
 898      */
 899     public function getColumnProperties($search_index, $column_index)
 900     {
 901         $selected_operator = (isset($_POST['criteriaColumnOperators'])
 902             ? $_POST['criteriaColumnOperators'][$search_index] : '');
 903         $entered_value = (isset($_POST['criteriaValues'])
 904             ? $_POST['criteriaValues'] : '');
 905         $titles['Browse'] = PMA_Util::getIcon(
 906             'b_browse.png', __('Browse foreign values')
 907         );
 908         //Gets column's type and collation
 909         $type = $this->_columnTypes[$column_index];
 910         $collation = $this->_columnCollations[$column_index];
 911         //Gets column's comparison operators depending on column type
 912         $func = '<select name="criteriaColumnOperators[' . $search_index . ']">';
 913         $func .= $GLOBALS['PMA_Types']->getTypeOperatorsHtml(
 914             preg_replace('@\(.*@s', '', $this->_columnTypes[$column_index]),
 915             $this->_columnNullFlags[$column_index], $selected_operator
 916         );
 917         $func .= '</select>';
 918         //Gets link to browse foreign data(if any) and criteria inputbox
 919         $foreignData = PMA_getForeignData(
 920             $this->_foreigners, $this->_columnNames[$column_index], false, '', ''
 921         );
 922         $value =  $this->_getInputbox(
 923             $foreignData, $this->_columnNames[$column_index], $type, $search_index,
 924             $titles, $GLOBALS['cfg']['ForeignKeyMaxLimit'], $entered_value
 925         );
 926         return array(
 927             'type' => $type,
 928             'collation' => $collation,
 929             'func' => $func,
 930             'value' => $value
 931         );
 932     }
 933
 934     /**
 935      * Provides the search form's table row in case of Normal Search
 936      * (for tbl_select.php)
 937      *
 938      * @return string the generated table row
 939      */
 940     private function _getRowsNormal()
 941     {
 942         $odd_row = true;
 943         $html_output = '';
 944         // for every column present in table
 945         for (
 946             $column_index = 0;
 947             $column_index < count($this->_columnNames);
 948             $column_index++
 949         ) {
 950             $html_output .= '<tr class="noclick '
 951                 . ($odd_row ? 'odd' : 'even')
 952                 . '">';
 953             $odd_row = !$odd_row;
 954             //If 'Function' column is present
 955             $html_output .= $this->_getGeomFuncHtml($column_index);
 956             //Displays column's name, type, collation and value
 957             $html_output .= '<th>'
 958                 . htmlspecialchars($this->_columnNames[$column_index]) . '</th>';
 959             $properties = $this->getColumnProperties($column_index, $column_index);
 960             $html_output .= '<td>' . $properties['type'] . '</td>';
 961             $html_output .= '<td>' . $properties['collation'] . '</td>';
 962             $html_output .= '<td>' . $properties['func'] . '</td>';
 963             $html_output .= '<td>' . $properties['value'] . '</td>';
 964             $html_output .= '</tr>';
 965             //Displays hidden fields
 966             $html_output .= '<tr><td>';
 967             $html_output .= '<input type="hidden"'
 968                 . ' name="criteriaColumnNames[' . $column_index . ']"'
 969                 . ' value="' . htmlspecialchars($this->_columnNames[$column_index])
 970                 . '" />';
 971             $html_output .= '<input type="hidden"'
 972                 . ' name="criteriaColumnTypes[' . $column_index . ']"'
 973                 . ' value="' . $this->_columnTypes[$column_index] . '" />';
 974             $html_output .= '<input type="hidden"'
 975                 . ' name="criteriaColumnCollations[' . $column_index . ']"'
 976                 . ' value="' . $this->_columnCollations[$column_index] . '" />';
 977             $html_output .= '</td></tr>';
 978         } // end for
 979
 980         return $html_output;
 981     }
 982
 983     /**
 984      * Provides the search form's table row in case of Zoom Search
 985      * (for tbl_zoom_select.php)
 986      *
 987      * @return string the generated table row
 988      */
 989     private function _getRowsZoom()
 990     {
 991         $odd_row = true;
 992         $html_output = '';
 993         /**
 994          * Get already set search criteria (if any)
 995          */
 996
 997         //Displays column rows for search criteria input
 998         for ($i = 0; $i < 4; $i++) {
 999             //After X-Axis and Y-Axis column rows, display additional criteria
1000             // option
1001             if ($i == 2) {
1002                 $html_output .= '<tr><td>';
1003                 $html_output .= __("Additional search criteria");
1004                 $html_output .= '</td></tr>';
1005             }
1006             $html_output .= '<tr class="noclick '
1007                 . ($odd_row ? 'odd' : 'even')
1008                 . '">';
1009             $odd_row = ! $odd_row;
1010             //Select options for column names
1011             $html_output .= '<th><select name="criteriaColumnNames[]" id="'
1012                 . 'tableid_' . $i . '" >';
1013             $html_output .= '<option value="' . 'pma_null' . '">' . __('None')
1014                 . '</option>';
1015             for ($j = 0 ; $j < count($this->_columnNames); $j++) {
1016                 if (isset($_POST['criteriaColumnNames'][$i])
1017                     && $_POST['criteriaColumnNames'][$i] == htmlspecialchars($this->_columnNames[$j])
1018                 ) {
1019                     $html_output .= '<option value="'
1020                         . htmlspecialchars($this->_columnNames[$j])
1021                         . '" selected="selected">'
1022                         . htmlspecialchars($this->_columnNames[$j])
1023                         . '</option>';
1024                 } else {
1025                     $html_output .= '<option value="'
1026                         . htmlspecialchars($this->_columnNames[$j]) . '">'
1027                         . htmlspecialchars($this->_columnNames[$j]) . '</option>';
1028                 }
1029             }
1030             $html_output .= '</select></th>';
1031             if (isset($_POST['criteriaColumnNames'])
1032                 && $_POST['criteriaColumnNames'][$i] != 'pma_null'
1033             ) {
1034                 $key = array_search(
1035                     $_POST['criteriaColumnNames'][$i],
1036                     $this->_columnNames
1037                 );
1038                 $properties = $this->getColumnProperties($i, $key);
1039                 $type[$i] = $properties['type'];
1040                 $collation[$i] = $properties['collation'];
1041                 $func[$i] = $properties['func'];
1042                 $value[$i] = $properties['value'];
1043             }
1044             //Column type
1045             $html_output .= '<td>' . (isset($type[$i]) ? $type[$i] : '') . '</td>';
1046             //Column Collation
1047             $html_output .= '<td>' . (isset($collation[$i]) ? $collation[$i] : '')
1048                 . '</td>';
1049             //Select options for column operators
1050             $html_output .= '<td>' . (isset($func[$i]) ? $func[$i] : '') . '</td>';
1051             //Inputbox for search criteria value
1052             $html_output .= '<td>' . (isset($value[$i]) ? $value[$i] : '') . '</td>';
1053             $html_output .= '</tr>';
1054             //Displays hidden fields
1055             $html_output .= '<tr><td>';
1056             $html_output
1057                 .= '<input type="hidden" name="criteriaColumnTypes[' . $i . ']"'
1058                 . ' id="types_' . $i . '" ';
1059             if (isset($_POST['criteriaColumnTypes'][$i])) {
1060                 $html_output .= 'value="' . $_POST['criteriaColumnTypes'][$i] . '" ';
1061             }
1062             $html_output .= '/>';
1063             $html_output .= '<input type="hidden" name="criteriaColumnCollations['
1064                 . $i . ']" id="collations_' . $i . '" />';
1065             $html_output .= '</td></tr>';
1066         }//end for
1067         return $html_output;
1068     }
1069
1070     /**
1071      * Generates HTML for displaying fields table in search form
1072      *
1073      * @return string the generated HTML
1074      */
1075     private function _getFieldsTableHtml()
1076     {
1077         $html_output = '';
1078         $html_output .= '<table class="data"'
1079             . ($this->_searchType == 'zoom' ? ' id="tableFieldsId"' : '') . '>';
1080         $html_output .= $this->_getTableHeader();
1081         $html_output .= '<tbody>';
1082
1083         if ($this->_searchType == 'zoom') {
1084             $html_output .= $this->_getRowsZoom();
1085         } else {
1086             $html_output .= $this->_getRowsNormal();
1087         }
1088
1089         $html_output .= '</tbody></table>';
1090         return $html_output;
1091     }
1092
1093     /**
1094      * Provides the form tag for table search form
1095      * (normal search or zoom search)
1096      *
1097      * @param string $goto Goto URL
1098      *
1099      * @return string the HTML for form tag
1100      */
1101     private function _getFormTag($goto)
1102     {
1103         $html_output = '';
1104         $scriptName = '';
1105         $formId = '';
1106         switch ($this->_searchType) {
1107         case 'normal' :
1108             $scriptName = 'tbl_select.php';
1109             $formId = 'tbl_search_form';
1110             break;
1111         case 'zoom' :
1112             $scriptName = 'tbl_zoom_select.php';
1113             $formId = 'zoom_search_form';
1114             break;
1115         case 'replace' :
1116             $scriptName = 'tbl_find_replace.php';
1117             $formId = 'find_replace_form';
1118             break;
1119         }
1120
1121         $html_output .= '<form method="post" action="' . $scriptName . '" '
1122             . 'name="insertForm" id="' . $formId . '" '
1123             . 'class="ajax"' . '>';
1124
1125         $html_output .= PMA_URL_getHiddenInputs($this->_db, $this->_table);
1126         $html_output .= '<input type="hidden" name="goto" value="' . $goto . '" />';
1127         $html_output .= '<input type="hidden" name="back" value="' . $scriptName
1128             . '" />';
1129
1130         return $html_output;
1131     }
1132
1133     /**
1134      * Returns the HTML for secondary levels tabs of the table search page
1135      *
1136      * @return string HTML for secondary levels tabs
1137      */
1138     public function getSecondaryTabs()
1139     {
1140         $url_params = array();
1141         $url_params['db'] = $this->_db;
1142         $url_params['table'] = $this->_table;
1143
1144         $html_output = '<ul id="topmenu2">';
1145         foreach ($this->_getSubTabs() as $tab) {
1146             $html_output .= PMA_Util::getHtmlTab($tab, $url_params);
1147         }
1148         $html_output .= '</ul>';
1149         $html_output .= '<div class="clearfloat"></div>';
1150         return $html_output;
1151     }
1152
1153     /**
1154      * Generates the table search form under table search tab
1155      *
1156      * @param string $goto      Goto URL
1157      * @param string $dataLabel Label for points in zoom plot
1158      *
1159      * @return string the generated HTML for table search form
1160      */
1161     public function getSelectionForm($goto, $dataLabel = null)
1162     {
1163         $html_output = $this->_getFormTag($goto);
1164
1165         if ($this->_searchType == 'zoom') {
1166             $html_output .= '<fieldset id="fieldset_zoom_search">';
1167             $html_output .= '<fieldset id="inputSection">';
1168             $html_output .= '<legend>'
1169                 . __('Do a "query by example" (wildcard: "%") for two different columns')
1170                 . '</legend>';
1171             $html_output .= $this->_getFieldsTableHtml();
1172             $html_output .= $this->_getOptionsZoom($dataLabel);
1173             $html_output .= '</fieldset>';
1174             $html_output .= '</fieldset>';
1175         } else if ($this->_searchType == 'normal') {
1176             $html_output .= '<fieldset id="fieldset_table_search">';
1177             $html_output .= '<fieldset id="fieldset_table_qbe">';
1178             $html_output .= '<legend>'
1179                 . __('Do a "query by example" (wildcard: "%")')
1180                 . '</legend>';
1181             $html_output .= $this->_getFieldsTableHtml();
1182             $html_output .= '<div id="gis_editor"></div>';
1183             $html_output .= '<div id="popup_background"></div>';
1184             $html_output .= '</fieldset>';
1185             $html_output .= $this->_getOptions();
1186             $html_output .= '</fieldset>';
1187         } else if ($this->_searchType == 'replace') {
1188             $html_output .= '<fieldset id="fieldset_find_replace">';
1189             $html_output .= '<fieldset id="fieldset_find">';
1190             $html_output .= '<legend>' . __('Find and Replace') . '</legend>';
1191             $html_output .= $this->_getSearchAndReplaceHTML();
1192             $html_output .= '</fieldset>';
1193             $html_output .= '</fieldset>';
1194         }
1195
1196         /**
1197          * Displays selection form's footer elements
1198          */
1199         $html_output .= '<fieldset class="tblFooters">';
1200         $html_output .= '<input type="submit" name="'
1201             . ($this->_searchType == 'zoom' ? 'zoom_submit' : 'submit')
1202             . ($this->_searchType == 'zoom' ? '" id="inputFormSubmitId"' : '" ')
1203             . 'value="' . __('Go') . '" />';
1204         $html_output .= '</fieldset></form>';
1205         $html_output .= '<div id="sqlqueryresults"></div>';
1206         return $html_output;
1207     }
1208
1209     /**
1210      * Provides form for displaying point data and also the scatter plot
1211      * (for tbl_zoom_select.php)
1212      *
1213      * @param string $goto Goto URL
1214      * @param array  $data Array containing SQL query data
1215      *
1216      * @return string form's html
1217      */
1218     public function getZoomResultsForm($goto, $data)
1219     {
1220         $html_output = '';
1221         $titles['Browse'] = PMA_Util::getIcon(
1222             'b_browse.png',
1223             __('Browse foreign values')
1224         );
1225         $html_output .= '<form method="post" action="tbl_zoom_select.php"'
1226             . ' name="displayResultForm" id="zoom_display_form"'
1227             . ' class="ajax"' . '>';
1228         $html_output .= PMA_URL_getHiddenInputs($this->_db, $this->_table);
1229         $html_output .= '<input type="hidden" name="goto" value="' . $goto . '" />';
1230         $html_output
1231             .= '<input type="hidden" name="back" value="tbl_zoom_select.php" />';
1232
1233         $html_output .= '<fieldset id="displaySection">';
1234         $html_output .= '<legend>' . __('Browse/Edit the points') . '</legend>';
1235
1236         //JSON encode the data(query result)
1237         $html_output .= '<center>';
1238         if (isset($_POST['zoom_submit']) && ! empty($data)) {
1239             $html_output .= '<div id="resizer">';
1240             $html_output .= '<center><a href="#" onclick="displayHelp();">'
1241                 . __('How to use') . '</a></center>';
1242             $html_output .= '<div id="querydata" style="display:none">'
1243                 . json_encode($data) . '</div>';
1244             $html_output .= '<div id="querychart"></div>';
1245             $html_output .= '<button class="button-reset">'
1246                 . __('Reset zoom') . '</button>';
1247             $html_output .= '</div>';
1248         }
1249         $html_output .= '</center>';
1250
1251         //Displays rows in point edit form
1252         $html_output .= '<div id="dataDisplay" style="display:none">';
1253         $html_output .= '<table><thead>';
1254         $html_output .= '<tr>';
1255         $html_output .= '<th>' . __('Column') . '</th>'
1256             . '<th>' . __('Null') . '</th>'
1257             . '<th>' . __('Value') . '</th>';
1258         $html_output .= '</tr>';
1259         $html_output .= '</thead>';
1260
1261         $html_output .= '<tbody>';
1262         $odd_row = true;
1263         for (
1264             $column_index = 0;
1265             $column_index < count($this->_columnNames);
1266             $column_index++
1267         ) {
1268             $fieldpopup = $this->_columnNames[$column_index];
1269             $foreignData = PMA_getForeignData(
1270                 $this->_foreigners,
1271                 $fieldpopup,
1272                 false,
1273                 '',
1274                 ''
1275             );
1276             $html_output
1277                 .= '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">';
1278             $odd_row = ! $odd_row;
1279             //Display column Names
1280             $html_output
1281                 .= '<th>' . htmlspecialchars($this->_columnNames[$column_index])
1282                 . '</th>';
1283             //Null checkbox if column can be null
1284             $html_output .= '<th>'
1285                 . (($this->_columnNullFlags[$column_index] == 'YES')
1286                 ? '<input type="checkbox" class="checkbox_null"'
1287                     . ' name="criteriaColumnNullFlags[' . $column_index . ']"'
1288                     . ' id="edit_fields_null_id_' . $column_index . '" />'
1289                 : '');
1290             $html_output .= '</th>';
1291             //Column's Input box
1292             $html_output .= '<th>';
1293             $html_output .= $this->_getInputbox(
1294                 $foreignData, $fieldpopup, $this->_columnTypes[$column_index],
1295                 $column_index, $titles, $GLOBALS['cfg']['ForeignKeyMaxLimit'],
1296                 '', false, true
1297             );
1298             $html_output .= '</th></tr>';
1299         }
1300         $html_output .= '</tbody></table>';
1301         $html_output .= '</div>';
1302         $html_output .= '<input type="hidden" id="queryID" name="sql_query" />';
1303         $html_output .= '</form>';
1304         return $html_output;
1305     }
1306
1307     /**
1308      * Displays the 'Find and Replace' form
1309      *
1310      * @return HTML for 'Find and Replace' form
1311      */
1312     function _getSearchAndReplaceHTML()
1313     {
1314         $htmlOutput  = __('Find:')
1315             . '<input type="text" value="" name="find" required />';
1316         $htmlOutput .= __('Replace with:')
1317             . '<input type="text" value="" name="replaceWith" required />';
1318
1319         $htmlOutput .= __('Column:') . '<select name="columnIndex">';
1320         for ($i = 0; $i < count($this->_columnNames); $i++) {
1321             $type = preg_replace('@\(.*@s', '', $this->_columnTypes[$i]);
1322             if ($GLOBALS['PMA_Types']->getTypeClass($type) == 'CHAR') {
1323                 $column = $this->_columnNames[$i];
1324                 $htmlOutput .= '<option value="' . $i . '">'
1325                     . htmlspecialchars($column) . '</option>';
1326             }
1327         }
1328         $htmlOutput .= '</select>';
1329         return $htmlOutput;
1330     }
1331
1332     /**
1333      * Returns HTML for prviewing strings found and their replacements
1334      *
1335      * @param int    $columnIndex index of the column
1336      * @param string $find        string to find in the column
1337      * @param string $replaceWith string to replace with
1338      * @param string $charSet     character set of the connection
1339      *
1340      * @return HTML for prviewing strings found and their replacements
1341      */
1342     function getReplacePreview($columnIndex, $find, $replaceWith, $charSet)
1343     {
1344         $column = $this->_columnNames[$columnIndex];
1345         $sql_query = "SELECT "
1346             . PMA_Util::backquote($column) . ","
1347             . " REPLACE("
1348             . PMA_Util::backquote($column) . ", '" . $find . "', '" . $replaceWith
1349             . "'),"
1350             . " COUNT(*)"
1351             . " FROM " . PMA_Util::backquote($this->_db)
1352             . "." . PMA_Util::backquote($this->_table)
1353             . " WHERE " . PMA_Util::backquote($column)
1354             . " LIKE '%" . $find . "%' COLLATE " . $charSet . "_bin"; // here we
1355             // change the collation of the 2nd operand to a case sensitive
1356             // binary collation to make sure that the comparison is case sensitive
1357         $sql_query .= " GROUP BY " . PMA_Util::backquote($column)
1358             . " ORDER BY " . PMA_Util::backquote($column) . " ASC";
1359
1360         $rs = $GLOBALS['dbi']->query(
1361             $sql_query, null, PMA_DatabaseInterface::QUERY_STORE
1362         );
1363
1364         $htmlOutput = '<form method="post" action="tbl_find_replace.php"'
1365             . ' name="previewForm" id="previewForm" class="ajax">';
1366         $htmlOutput .= PMA_URL_getHiddenInputs($this->_db, $this->_table);
1367         $htmlOutput .= '<input type="hidden" name="replace" value="true" />';
1368         $htmlOutput .= '<input type="hidden" name="columnIndex" value="'
1369             . $columnIndex . '" />';
1370         $htmlOutput .= '<input type="hidden" name="findString"'
1371             . ' value="' . $find . '" />';
1372         $htmlOutput .= '<input type="hidden" name="replaceWith"'
1373             . ' value="' . $replaceWith . '" />';
1374
1375         $htmlOutput .= '<fieldset id="fieldset_find_replace_preview">';
1376         $htmlOutput .= '<legend>' . __('Find and replace - preview') . '</legend>';
1377
1378         $htmlOutput .= '<table id="previewTable">'
1379             . '<thead><tr>'
1380             . '<th>' . __('Count') . '</th>'
1381             . '<th>' . __('Original string') . '</th>'
1382             . '<th>' . __('Replaced string') . '</th>'
1383             . '</tr></thead>';
1384
1385         $htmlOutput .= '<tbody>';
1386         $odd = true;
1387         while ($row = $GLOBALS['dbi']->fetchRow($rs)) {
1388             $val = $row[0];
1389             $replaced = $row[1];
1390             $count = $row[2];
1391
1392             $htmlOutput .= '<tr class="' . ($odd ? 'odd' : 'even') . '">';
1393             $htmlOutput .= '<td class="right">' . htmlspecialchars($count) . '</td>';
1394             $htmlOutput .= '<td>' . htmlspecialchars($val) . '</td>';
1395             $htmlOutput .= '<td>' . htmlspecialchars($replaced) . '</td>';
1396             $htmlOutput .= '</tr>';
1397
1398             $odd = ! $odd;
1399         }
1400         $htmlOutput .= '</tbody>';
1401         $htmlOutput .= '</table>';
1402         $htmlOutput .= '</fieldset>';
1403
1404         $htmlOutput .= '<fieldset class="tblFooters">';
1405         $htmlOutput .= '<input type="submit" name="replace"'
1406             . ' value="' . __('Replace') . '" />';
1407         $htmlOutput .= '</fieldset>';
1408
1409         $htmlOutput .= '</form>';
1410         return $htmlOutput;
1411     }
1412
1413     /**
1414      * Replaces a given string in a column with a give replacement
1415      *
1416      * @param int    $columnIndex index of the column
1417      * @param string $find        string to find in the column
1418      * @param string $replaceWith string to replace with
1419      * @param string $charSet     character set of the connection
1420      *
1421      * @return void
1422      */
1423     function replace($columnIndex, $find, $replaceWith, $charSet)
1424     {
1425         $column = $this->_columnNames[$columnIndex];
1426         $sql_query = "UPDATE " . PMA_Util::backquote($this->_db)
1427             . "." . PMA_Util::backquote($this->_table)
1428             . " SET " . PMA_Util::backquote($column) . " ="
1429             . " REPLACE("
1430             . PMA_Util::backquote($column) . ", '" . $find . "', '" . $replaceWith
1431             . "')"
1432             . " WHERE " . PMA_Util::backquote($column)
1433             . " LIKE '%" . $find . "%' COLLATE " . $charSet . "_bin"; // here we
1434             // change the collation of the 2nd operand to a case sensitive
1435             // binary collation to make sure that the comparison is case sensitive
1436         $GLOBALS['dbi']->query(
1437             $sql_query, null, PMA_DatabaseInterface::QUERY_STORE
1438         );
1439         $GLOBALS['sql_query'] = $sql_query;
1440     }
1441 }
1442 ?>