phpmyadmin/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 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      * @param bool   $in_zoom_search_edit Whether we are in zoom search edit
 262      *
 263      * @return string 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 string 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             $where = $backquoted_name . ' ' . $func_type;
 553
 554         } elseif (strncasecmp($types, 'enum', 4) == 0 && ! empty($criteriaValues)) {
 555             $where = $backquoted_name;
 556             $where .= $this->_getEnumWhereClause($criteriaValues, $func_type);
 557
 558         } elseif ($criteriaValues != '') {
 559             // For these types we quote the value. Even if it's another type
 560             // (like INT), for a LIKE we always quote the value. MySQL converts
 561             // strings to numbers and numbers to strings as necessary
 562             // during the comparison
 563             if (preg_match('@char|binary|blob|text|set|date|time|year@i', $types)
 564                 || strpos(' ' . $func_type, 'LIKE')
 565             ) {
 566                 $quot = '\'';
 567             } else {
 568                 $quot = '';
 569             }
 570
 571             // LIKE %...%
 572             if ($func_type == 'LIKE %...%') {
 573                 $func_type = 'LIKE';
 574                 $criteriaValues = '%' . $criteriaValues . '%';
 575             }
 576             if ($func_type == 'REGEXP ^...$') {
 577                 $func_type = 'REGEXP';
 578                 $criteriaValues = '^' . $criteriaValues . '$';
 579             }
 580
 581             if ('IN (...)' != $func_type
 582                 && 'NOT IN (...)' != $func_type
 583                 && 'BETWEEN' != $func_type
 584                 && 'NOT BETWEEN' != $func_type
 585             ) {
 586                 if ($func_type == 'LIKE %...%' || $func_type == 'LIKE') {
 587                     $where = $backquoted_name . ' ' . $func_type . ' ' . $quot
 588                         . PMA_Util::sqlAddSlashes($criteriaValues, true) . $quot;
 589                 } else {
 590                     $where = $backquoted_name . ' ' . $func_type . ' ' . $quot
 591                         . PMA_Util::sqlAddSlashes($criteriaValues) . $quot;
 592                 }
 593                 return $where;
 594             }
 595             $func_type = str_replace(' (...)', '', $func_type);
 596
 597             //Don't explode if this is already an array
 598             //(Case for (NOT) IN/BETWEEN.)
 599             if (is_array($criteriaValues)) {
 600                 $values = $criteriaValues;
 601             } else {
 602                 $values = explode(',', $criteriaValues);
 603             }
 604             // quote values one by one
 605             $emptyKey = false;
 606             foreach ($values as $key => &$value) {
 607                 if ('' === $value) {
 608                     $emptyKey = $key;
 609                     $value = 'NULL';
 610                     continue;
 611                 }
 612                 $value = $quot . PMA_Util::sqlAddSlashes(trim($value))
 613                     . $quot;
 614             }
 615
 616             if ('BETWEEN' == $func_type || 'NOT BETWEEN' == $func_type) {
 617                 $where = $backquoted_name . ' ' . $func_type . ' '
 618                     . (isset($values[0]) ? $values[0] : '')
 619                     . ' AND ' . (isset($values[1]) ? $values[1] : '');
 620             } else { //[NOT] IN
 621                 if (false !== $emptyKey) {
 622                     unset($values[$emptyKey]);
 623                 }
 624                 $wheres = array();
 625                 if (!empty($values)) {
 626                     $wheres[] = $backquoted_name . ' ' . $func_type
 627                         . ' (' . implode(',', $values) . ')';
 628                 }
 629                 if (false !== $emptyKey) {
 630                     $wheres[] = $backquoted_name . ' IS NULL';
 631                 }
 632                 $where = implode(' OR ', $wheres);
 633                 if (1 < count($wheres)) {
 634                     $where = '(' . $where . ')';
 635                 }
 636             }
 637         } // end if
 638
 639         return $where;
 640     }
 641
 642     /**
 643      * Builds the sql search query from the post parameters
 644      *
 645      * @return string the generated SQL query
 646      */
 647     public function buildSqlQuery()
 648     {
 649         $sql_query = 'SELECT ';
 650
 651         // If only distinct values are needed
 652         $is_distinct = (isset($_POST['distinct'])) ? 'true' : 'false';
 653         if ($is_distinct == 'true') {
 654             $sql_query .= 'DISTINCT ';
 655         }
 656
 657         // if all column names were selected to display, we do a 'SELECT *'
 658         // (more efficient and this helps prevent a problem in IE
 659         // if one of the rows is edited and we come back to the Select results)
 660         if (isset($_POST['zoom_submit']) || ! empty($_POST['displayAllColumns'])) {
 661             $sql_query .= '* ';
 662         } else {
 663             $sql_query .= implode(
 664                 ', ',
 665                 PMA_Util::backquote($_POST['columnsToDisplay'])
 666             );
 667         } // end if
 668
 669         $sql_query .= ' FROM '
 670             . PMA_Util::backquote($_POST['table']);
 671         $whereClause = $this->_generateWhereClause();
 672         $sql_query .= $whereClause;
 673
 674         // if the search results are to be ordered
 675         if (isset($_POST['orderByColumn']) && $_POST['orderByColumn'] != '--nil--') {
 676             $sql_query .= ' ORDER BY '
 677                 . PMA_Util::backquote($_POST['orderByColumn'])
 678                 . ' ' . $_POST['order'];
 679         } // end if
 680         return $sql_query;
 681     }
 682
 683     /**
 684      * Generates the where clause for the SQL search query to be executed
 685      *
 686      * @return string the generated where clause
 687      */
 688     private function _generateWhereClause()
 689     {
 690         if (isset($_POST['customWhereClause'])
 691             && trim($_POST['customWhereClause']) != ''
 692         ) {
 693             return ' WHERE ' . $_POST['customWhereClause'];
 694         }
 695
 696         // If there are no search criteria set or no unary criteria operators,
 697         // return
 698         if (! isset($_POST['criteriaValues'])
 699             && ! isset($_POST['criteriaColumnOperators'])
 700         ) {
 701             return '';
 702         }
 703
 704         // else continue to form the where clause from column criteria values
 705         $fullWhereClause = $charsets = array();
 706         reset($_POST['criteriaColumnOperators']);
 707         while (list($column_index, $operator) = each(
 708             $_POST['criteriaColumnOperators']
 709         )) {
 710             list($charsets[$column_index]) = explode(
 711                 '_', $_POST['criteriaColumnCollations'][$column_index]
 712             );
 713             $unaryFlag =  $GLOBALS['PMA_Types']->isUnaryOperator($operator);
 714             $tmp_geom_func = isset($geom_func[$column_index])
 715                 ? $geom_func[$column_index] : null;
 716
 717             $whereClause = $this->_getWhereClause(
 718                 $_POST['criteriaValues'][$column_index],
 719                 $_POST['criteriaColumnNames'][$column_index],
 720                 $_POST['criteriaColumnTypes'][$column_index],
 721                 $_POST['criteriaColumnCollations'][$column_index],
 722                 $operator,
 723                 $unaryFlag,
 724                 $tmp_geom_func
 725             );
 726
 727             if ($whereClause) {
 728                 $fullWhereClause[] = $whereClause;
 729             }
 730         } // end while
 731
 732         if ($fullWhereClause) {
 733             return ' WHERE ' . implode(' AND ', $fullWhereClause);
 734         }
 735         return '';
 736     }
 737
 738     /**
 739      * Generates HTML for a geometrical function column to be displayed in table
 740      * search selection form
 741      *
 742      * @param integer $column_index index of current column in $columnTypes array
 743      *
 744      * @return string the generated HTML
 745      */
 746     private function _getGeomFuncHtml($column_index)
 747     {
 748         $html_output = '';
 749         // return if geometrical column is not present
 750         if (! $this->_geomColumnFlag) {
 751             return $html_output;
 752         }
 753
 754         /**
 755          * Displays 'Function' column if it is present
 756          */
 757         $html_output .= '<td>';
 758         $geom_types = PMA_Util::getGISDatatypes();
 759         // if a geometry column is present
 760         if (in_array($this->_columnTypes[$column_index], $geom_types)) {
 761             $html_output .= '<select class="geom_func" name="geom_func['
 762                 . $column_index . ']">';
 763             // get the relevant list of GIS functions
 764             $funcs = PMA_Util::getGISFunctions(
 765                 $this->_columnTypes[$column_index], true, true
 766             );
 767             /**
 768              * For each function in the list of functions,
 769              * add an option to select list
 770              */
 771             foreach ($funcs as $func_name => $func) {
 772                 $name = isset($func['display']) ? $func['display'] : $func_name;
 773                 $html_output .= '<option value="' . htmlspecialchars($name) . '">'
 774                         . htmlspecialchars($name) . '</option>';
 775             }
 776             $html_output .= '</select>';
 777         } else {
 778             $html_output .= '&nbsp;';
 779         }
 780         $html_output .= '</td>';
 781         return $html_output;
 782     }
 783
 784     /**
 785      * Generates formatted HTML for extra search options in table search form
 786      *
 787      * @return string the generated HTML
 788      */
 789     private function _getOptions()
 790     {
 791         $html_output = '';
 792         $html_output .= PMA_Util::getDivForSliderEffect(
 793             'searchoptions', __('Options')
 794         );
 795
 796         /**
 797          * Displays columns select list for selecting distinct columns in the search
 798          */
 799         $html_output .= '<fieldset id="fieldset_select_fields">'
 800             . '<legend>' . __('Select columns (at least one):') . '</legend>'
 801             . '<select name="columnsToDisplay[]"'
 802             . ' size="' . min(count($this->_columnNames), 10) . '"'
 803             . ' multiple="multiple">';
 804         // Displays the list of the fields
 805         foreach ($this->_columnNames as $each_field) {
 806             $html_output .= '        '
 807                 . '<option value="' . htmlspecialchars($each_field) . '"'
 808                 . ' selected="selected">' . htmlspecialchars($each_field)
 809                 . '</option>' . "\n";
 810         } // end for
 811         $html_output .= '</select>'
 812             . '<input type="checkbox" name="distinct" value="DISTINCT"'
 813             . ' id="oDistinct" />'
 814             . '<label for="oDistinct">DISTINCT</label></fieldset>';
 815
 816         /**
 817          * Displays input box for custom 'Where' clause to be used in the search
 818          */
 819         $html_output .= '<fieldset id="fieldset_search_conditions">'
 820             . '<legend>' . '<em>' . __('Or') . '</em> '
 821             . __('Add search conditions (body of the "where" clause):') . '</legend>';
 822         $html_output .= PMA_Util::showMySQLDocu('Functions');
 823         $html_output .= '<input type="text" name="customWhereClause"'
 824             . ' class="textfield" size="64" />';
 825         $html_output .= '</fieldset>';
 826
 827         /**
 828          * Displays option of changing default number of rows displayed per page
 829          */
 830         $html_output .= '<fieldset id="fieldset_limit_rows">'
 831             . '<legend>' . __('Number of rows per page') . '</legend>'
 832             . '<input type="number" name="session_max_rows" required="required" '
 833             . 'min="1" '
 834             . 'value="' . $GLOBALS['cfg']['MaxRows'] . '" class="textfield" />'
 835             . '</fieldset>';
 836
 837         /**
 838          * Displays option for ordering search results
 839          * by a column value (Asc or Desc)
 840          */
 841         $html_output .= '<fieldset id="fieldset_display_order">'
 842             . '<legend>' . __('Display order:') . '</legend>'
 843             . '<select name="orderByColumn"><option value="--nil--"></option>';
 844         foreach ($this->_columnNames as $each_field) {
 845             $html_output .= '        '
 846                 . '<option value="' . htmlspecialchars($each_field) . '">'
 847                 . htmlspecialchars($each_field) . '</option>' . "\n";
 848         } // end for
 849         $html_output .= '</select>';
 850         $choices = array(
 851             'ASC' => __('Ascending'),
 852             'DESC' => __('Descending')
 853         );
 854         $html_output .= PMA_Util::getRadioFields(
 855             'order', $choices, 'ASC', false, true, "formelement"
 856         );
 857         unset($choices);
 858
 859         $html_output .= '</fieldset><br style="clear: both;"/></div>';
 860         return $html_output;
 861     }
 862
 863     /**
 864      * Other search criteria like data label
 865      * (for tbl_zoom_select.php)
 866      *
 867      * @param array $dataLabel Label for points in zoom plot
 868      *
 869      * @return string the generated html
 870      */
 871     private function _getOptionsZoom($dataLabel)
 872     {
 873         $html_output = '';
 874         $html_output .= '<table class="data">';
 875         //Select options for datalabel
 876         $html_output .= '<tr>';
 877         $html_output .= '<td><label for="dataLabel">'
 878             . __("Use this column to label each point") . '</label></td>';
 879         $html_output .= '<td><select name="dataLabel" id="dataLabel" >'
 880             . '<option value = "">' . __('None') . '</option>';
 881         for ($j = 0, $nb = count($this->_columnNames); $j < $nb; $j++) {
 882             if (isset($dataLabel)
 883                 && $dataLabel == htmlspecialchars($this->_columnNames[$j])
 884             ) {
 885                 $html_output .= '<option value="'
 886                     . htmlspecialchars($this->_columnNames[$j])
 887                     . '" selected="selected">'
 888                     . htmlspecialchars($this->_columnNames[$j])
 889                     . '</option>';
 890             } else {
 891                 $html_output .= '<option value="'
 892                     . htmlspecialchars($this->_columnNames[$j]) . '" >'
 893                     . htmlspecialchars($this->_columnNames[$j]) . '</option>';
 894             }
 895         }
 896         $html_output .= '</select></td>';
 897         $html_output .= '</tr>';
 898         //Inputbox for changing default maximum rows to plot
 899         $html_output .= '<tr>';
 900         $html_output .= '<td><label for="maxRowPlotLimit">'
 901             . __("Maximum rows to plot") . '</label></td>';
 902         $html_output .= '<td>';
 903         $html_output .= '<input type="number" name="maxPlotLimit"'
 904             . ' id="maxRowPlotLimit" required="required"'
 905             . ' value="' . ((! empty($_POST['maxPlotLimit']))
 906                 ? htmlspecialchars($_POST['maxPlotLimit'])
 907                 : $GLOBALS['cfg']['maxRowPlotLimit'])
 908             . '" />';
 909         $html_output .= '</td></tr>';
 910         $html_output .= '</table>';
 911         return $html_output;
 912     }
 913
 914     /**
 915      * Provides a column's type, collation, operators list, and crietria value
 916      * to display in table search form
 917      *
 918      * @param integer $search_index Row number in table search form
 919      * @param integer $column_index Column index in ColumnNames array
 920      *
 921      * @return array Array contaning column's properties
 922      */
 923     public function getColumnProperties($search_index, $column_index)
 924     {
 925         $selected_operator = (isset($_POST['criteriaColumnOperators'])
 926             ? $_POST['criteriaColumnOperators'][$search_index] : '');
 927         $entered_value = (isset($_POST['criteriaValues'])
 928             ? $_POST['criteriaValues'] : '');
 929         $titles = array(
 930             'Browse' => PMA_Util::getIcon(
 931                 'b_browse.png', __('Browse foreign values')
 932             )
 933         );
 934         //Gets column's type and collation
 935         $type = $this->_columnTypes[$column_index];
 936         $collation = $this->_columnCollations[$column_index];
 937         //Gets column's comparison operators depending on column type
 938         $func = '<select name="criteriaColumnOperators['
 939             . $search_index . ']" onchange="changeValueFieldType(this, '
 940             . $search_index . ')">';
 941         $func .= $GLOBALS['PMA_Types']->getTypeOperatorsHtml(
 942             preg_replace('@\(.*@s', '', $this->_columnTypes[$column_index]),
 943             $this->_columnNullFlags[$column_index], $selected_operator
 944         );
 945         $func .= '</select>';
 946         //Gets link to browse foreign data(if any) and criteria inputbox
 947         $foreignData = PMA_getForeignData(
 948             $this->_foreigners, $this->_columnNames[$column_index], false, '', ''
 949         );
 950         $value =  $this->_getInputbox(
 951             $foreignData, $this->_columnNames[$column_index], $type, $search_index,
 952             $titles, $GLOBALS['cfg']['ForeignKeyMaxLimit'], $entered_value
 953         );
 954         return array(
 955             'type' => $type,
 956             'collation' => $collation,
 957             'func' => $func,
 958             'value' => $value
 959         );
 960     }
 961
 962     /**
 963      * Provides the search form's table row in case of Normal Search
 964      * (for tbl_select.php)
 965      *
 966      * @return string the generated table row
 967      */
 968     private function _getRowsNormal()
 969     {
 970         $odd_row = true;
 971         $html_output = '';
 972         // for every column present in table
 973         for (
 974             $column_index = 0, $nb = count($this->_columnNames);
 975             $column_index < $nb;
 976             $column_index++
 977         ) {
 978             $html_output .= '<tr class="noclick '
 979                 . ($odd_row ? 'odd' : 'even')
 980                 . '">';
 981             $odd_row = !$odd_row;
 982             //If 'Function' column is present
 983             $html_output .= $this->_getGeomFuncHtml($column_index);
 984             //Displays column's name, type, collation and value
 985             $html_output .= '<th>'
 986                 . htmlspecialchars($this->_columnNames[$column_index]) . '</th>';
 987             $properties = $this->getColumnProperties($column_index, $column_index);
 988             $html_output .= '<td>' . $properties['type'] . '</td>';
 989             $html_output .= '<td>' . $properties['collation'] . '</td>';
 990             $html_output .= '<td>' . $properties['func'] . '</td>';
 991             // here, the data-type attribute is needed for a date/time picker
 992             $html_output .= '<td data-type="' . $properties['type'] . '"'
 993                 . '>' . $properties['value'] . '</td>';
 994             $html_output .= '</tr>';
 995             //Displays hidden fields
 996             $html_output .= '<tr><td>';
 997             $html_output .= '<input type="hidden"'
 998                 . ' name="criteriaColumnNames[' . $column_index . ']"'
 999                 . ' value="' . htmlspecialchars($this->_columnNames[$column_index])
1000                 . '" />';
1001             $html_output .= '<input type="hidden"'
1002                 . ' name="criteriaColumnTypes[' . $column_index . ']"'
1003                 . ' value="' . $this->_columnTypes[$column_index] . '" />';
1004             $html_output .= '<input type="hidden"'
1005                 . ' name="criteriaColumnCollations[' . $column_index . ']"'
1006                 . ' value="' . $this->_columnCollations[$column_index] . '" />';
1007             $html_output .= '</td></tr>';
1008         } // end for
1009
1010         return $html_output;
1011     }
1012
1013     /**
1014      * Provides the search form's table row in case of Zoom Search
1015      * (for tbl_zoom_select.php)
1016      *
1017      * @return string the generated table row
1018      */
1019     private function _getRowsZoom()
1020     {
1021         $odd_row     = true;
1022         $html_output = '';
1023         $type        = array();
1024         $collation   = array();
1025         $func        = array();
1026         $value       = array();
1027         /**
1028          * Get already set search criteria (if any)
1029          */
1030
1031         //Displays column rows for search criteria input
1032         for ($i = 0; $i < 4; $i++) {
1033             //After X-Axis and Y-Axis column rows, display additional criteria
1034             // option
1035             if ($i == 2) {
1036                 $html_output .= '<tr><td>';
1037                 $html_output .= __("Additional search criteria");
1038                 $html_output .= '</td></tr>';
1039             }
1040             $html_output .= '<tr class="noclick '
1041                 . ($odd_row ? 'odd' : 'even')
1042                 . '">';
1043             $odd_row = ! $odd_row;
1044             //Select options for column names
1045             $html_output .= '<th><select name="criteriaColumnNames[]" id="'
1046                 . 'tableid_' . $i . '" >';
1047             $html_output .= '<option value="' . 'pma_null' . '">' . __('None')
1048                 . '</option>';
1049             for ($j = 0, $nb = count($this->_columnNames); $j < $nb; $j++) {
1050                 if (isset($_POST['criteriaColumnNames'][$i])
1051                     && $_POST['criteriaColumnNames'][$i] == htmlspecialchars($this->_columnNames[$j])
1052                 ) {
1053                     $html_output .= '<option value="'
1054                         . htmlspecialchars($this->_columnNames[$j])
1055                         . '" selected="selected">'
1056                         . htmlspecialchars($this->_columnNames[$j])
1057                         . '</option>';
1058                 } else {
1059                     $html_output .= '<option value="'
1060                         . htmlspecialchars($this->_columnNames[$j]) . '">'
1061                         . htmlspecialchars($this->_columnNames[$j]) . '</option>';
1062                 }
1063             }
1064             $html_output .= '</select></th>';
1065             if (isset($_POST['criteriaColumnNames'])
1066                 && $_POST['criteriaColumnNames'][$i] != 'pma_null'
1067             ) {
1068                 $key = array_search(
1069                     $_POST['criteriaColumnNames'][$i],
1070                     $this->_columnNames
1071                 );
1072                 $properties = $this->getColumnProperties($i, $key);
1073                 $type[$i] = $properties['type'];
1074                 $collation[$i] = $properties['collation'];
1075                 $func[$i] = $properties['func'];
1076                 $value[$i] = $properties['value'];
1077             }
1078             //Column type
1079             $html_output .= '<td>' . (isset($type[$i]) ? $type[$i] : '') . '</td>';
1080             //Column Collation
1081             $html_output .= '<td>' . (isset($collation[$i]) ? $collation[$i] : '')
1082                 . '</td>';
1083             //Select options for column operators
1084             $html_output .= '<td>' . (isset($func[$i]) ? $func[$i] : '') . '</td>';
1085             //Inputbox for search criteria value
1086             $html_output .= '<td>' . (isset($value[$i]) ? $value[$i] : '') . '</td>';
1087             $html_output .= '</tr>';
1088             //Displays hidden fields
1089             $html_output .= '<tr><td>';
1090             $html_output
1091                 .= '<input type="hidden" name="criteriaColumnTypes[' . $i . ']"'
1092                 . ' id="types_' . $i . '" ';
1093             if (isset($_POST['criteriaColumnTypes'][$i])) {
1094                 $html_output .= 'value="' . $_POST['criteriaColumnTypes'][$i] . '" ';
1095             }
1096             $html_output .= '/>';
1097             $html_output .= '<input type="hidden" name="criteriaColumnCollations['
1098                 . $i . ']" id="collations_' . $i . '" />';
1099             $html_output .= '</td></tr>';
1100         }//end for
1101         return $html_output;
1102     }
1103
1104     /**
1105      * Generates HTML for displaying fields table in search form
1106      *
1107      * @return string the generated HTML
1108      */
1109     private function _getFieldsTableHtml()
1110     {
1111         $html_output = '';
1112         $html_output .= '<table class="data"'
1113             . ($this->_searchType == 'zoom' ? ' id="tableFieldsId"' : '') . '>';
1114         $html_output .= $this->_getTableHeader();
1115         $html_output .= '<tbody>';
1116
1117         if ($this->_searchType == 'zoom') {
1118             $html_output .= $this->_getRowsZoom();
1119         } else {
1120             $html_output .= $this->_getRowsNormal();
1121         }
1122
1123         $html_output .= '</tbody></table>';
1124         return $html_output;
1125     }
1126
1127     /**
1128      * Provides the form tag for table search form
1129      * (normal search or zoom search)
1130      *
1131      * @param string $goto Goto URL
1132      *
1133      * @return string the HTML for form tag
1134      */
1135     private function _getFormTag($goto)
1136     {
1137         $html_output = '';
1138         $scriptName = '';
1139         $formId = '';
1140         switch ($this->_searchType) {
1141         case 'normal' :
1142             $scriptName = 'tbl_select.php';
1143             $formId = 'tbl_search_form';
1144             break;
1145         case 'zoom' :
1146             $scriptName = 'tbl_zoom_select.php';
1147             $formId = 'zoom_search_form';
1148             break;
1149         case 'replace' :
1150             $scriptName = 'tbl_find_replace.php';
1151             $formId = 'find_replace_form';
1152             break;
1153         }
1154
1155         $html_output .= '<form method="post" action="' . $scriptName . '" '
1156             . 'name="insertForm" id="' . $formId . '" '
1157             . 'class="ajax"' . '>';
1158
1159         $html_output .= PMA_URL_getHiddenInputs($this->_db, $this->_table);
1160         $html_output .= '<input type="hidden" name="goto" value="' . $goto . '" />';
1161         $html_output .= '<input type="hidden" name="back" value="' . $scriptName
1162             . '" />';
1163
1164         return $html_output;
1165     }
1166
1167     /**
1168      * Returns the HTML for secondary levels tabs of the table search page
1169      *
1170      * @return string HTML for secondary levels tabs
1171      */
1172     public function getSecondaryTabs()
1173     {
1174         $url_params = array();
1175         $url_params['db'] = $this->_db;
1176         $url_params['table'] = $this->_table;
1177
1178         $html_output = '<ul id="topmenu2">';
1179         foreach ($this->_getSubTabs() as $tab) {
1180             $html_output .= PMA_Util::getHtmlTab($tab, $url_params);
1181         }
1182         $html_output .= '</ul>';
1183         $html_output .= '<div class="clearfloat"></div>';
1184         return $html_output;
1185     }
1186
1187     /**
1188      * Generates the table search form under table search tab
1189      *
1190      * @param string $goto      Goto URL
1191      * @param string $dataLabel Label for points in zoom plot
1192      *
1193      * @return string the generated HTML for table search form
1194      */
1195     public function getSelectionForm($goto, $dataLabel = null)
1196     {
1197         $html_output = $this->_getFormTag($goto);
1198
1199         if ($this->_searchType == 'zoom') {
1200             $html_output .= '<fieldset id="fieldset_zoom_search">';
1201             $html_output .= '<fieldset id="inputSection">';
1202             $html_output .= '<legend>'
1203                 . __('Do a "query by example" (wildcard: "%") for two different columns')
1204                 . '</legend>';
1205             $html_output .= $this->_getFieldsTableHtml();
1206             $html_output .= $this->_getOptionsZoom($dataLabel);
1207             $html_output .= '</fieldset>';
1208             $html_output .= '</fieldset>';
1209         } else if ($this->_searchType == 'normal') {
1210             $html_output .= '<fieldset id="fieldset_table_search">';
1211             $html_output .= '<fieldset id="fieldset_table_qbe">';
1212             $html_output .= '<legend>'
1213                 . __('Do a "query by example" (wildcard: "%")')
1214                 . '</legend>';
1215             $html_output .= $this->_getFieldsTableHtml();
1216             $html_output .= '<div id="gis_editor"></div>';
1217             $html_output .= '<div id="popup_background"></div>';
1218             $html_output .= '</fieldset>';
1219             $html_output .= $this->_getOptions();
1220             $html_output .= '</fieldset>';
1221         } else if ($this->_searchType == 'replace') {
1222             $html_output .= '<fieldset id="fieldset_find_replace">';
1223             $html_output .= '<fieldset id="fieldset_find">';
1224             $html_output .= '<legend>' . __('Find and Replace') . '</legend>';
1225             $html_output .= $this->_getSearchAndReplaceHTML();
1226             $html_output .= '</fieldset>';
1227             $html_output .= '</fieldset>';
1228         }
1229
1230         /**
1231          * Displays selection form's footer elements
1232          */
1233         $html_output .= '<fieldset class="tblFooters">';
1234         $html_output .= '<input type="submit" name="'
1235             . ($this->_searchType == 'zoom' ? 'zoom_submit' : 'submit')
1236             . ($this->_searchType == 'zoom' ? '" id="inputFormSubmitId"' : '" ')
1237             . 'value="' . __('Go') . '" />';
1238         $html_output .= '</fieldset></form>';
1239         $html_output .= '<div id="sqlqueryresults"></div>';
1240         return $html_output;
1241     }
1242
1243     /**
1244      * Provides form for displaying point data and also the scatter plot
1245      * (for tbl_zoom_select.php)
1246      *
1247      * @param string $goto Goto URL
1248      * @param array  $data Array containing SQL query data
1249      *
1250      * @return string form's html
1251      */
1252     public function getZoomResultsForm($goto, $data)
1253     {
1254         $html_output = '';
1255         $titles = array(
1256             'Browse' => PMA_Util::getIcon(
1257                 'b_browse.png',
1258                 __('Browse foreign values')
1259             )
1260         );
1261         $html_output .= '<form method="post" action="tbl_zoom_select.php"'
1262             . ' name="displayResultForm" id="zoom_display_form"'
1263             . ' class="ajax"' . '>';
1264         $html_output .= PMA_URL_getHiddenInputs($this->_db, $this->_table);
1265         $html_output .= '<input type="hidden" name="goto" value="' . $goto . '" />';
1266         $html_output
1267             .= '<input type="hidden" name="back" value="tbl_zoom_select.php" />';
1268
1269         $html_output .= '<fieldset id="displaySection">';
1270         $html_output .= '<legend>' . __('Browse/Edit the points') . '</legend>';
1271
1272         //JSON encode the data(query result)
1273         $html_output .= '<center>';
1274         if (isset($_POST['zoom_submit']) && ! empty($data)) {
1275             $html_output .= '<div id="resizer">';
1276             $html_output .= '<center><a href="#" onclick="displayHelp();">'
1277                 . __('How to use') . '</a></center>';
1278             $html_output .= '<div id="querydata" style="display:none">'
1279                 . json_encode($data) . '</div>';
1280             $html_output .= '<div id="querychart"></div>';
1281             $html_output .= '<button class="button-reset">'
1282                 . __('Reset zoom') . '</button>';
1283             $html_output .= '</div>';
1284         }
1285         $html_output .= '</center>';
1286
1287         //Displays rows in point edit form
1288         $html_output .= '<div id="dataDisplay" style="display:none">';
1289         $html_output .= '<table><thead>';
1290         $html_output .= '<tr>';
1291         $html_output .= '<th>' . __('Column') . '</th>'
1292             . '<th>' . __('Null') . '</th>'
1293             . '<th>' . __('Value') . '</th>';
1294         $html_output .= '</tr>';
1295         $html_output .= '</thead>';
1296
1297         $html_output .= '<tbody>';
1298         $odd_row = true;
1299         for (
1300             $column_index = 0, $nb = count($this->_columnNames);
1301             $column_index < $nb;
1302             $column_index++
1303         ) {
1304             $fieldpopup = $this->_columnNames[$column_index];
1305             $foreignData = PMA_getForeignData(
1306                 $this->_foreigners,
1307                 $fieldpopup,
1308                 false,
1309                 '',
1310                 ''
1311             );
1312             $html_output
1313                 .= '<tr class="noclick ' . ($odd_row ? 'odd' : 'even') . '">';
1314             $odd_row = ! $odd_row;
1315             //Display column Names
1316             $html_output
1317                 .= '<th>' . htmlspecialchars($this->_columnNames[$column_index])
1318                 . '</th>';
1319             //Null checkbox if column can be null
1320             $html_output .= '<th>'
1321                 . (($this->_columnNullFlags[$column_index] == 'YES')
1322                 ? '<input type="checkbox" class="checkbox_null"'
1323                     . ' name="criteriaColumnNullFlags[' . $column_index . ']"'
1324                     . ' id="edit_fields_null_id_' . $column_index . '" />'
1325                 : '');
1326             $html_output .= '</th>';
1327             //Column's Input box
1328             $html_output .= '<th>';
1329             $html_output .= $this->_getInputbox(
1330                 $foreignData, $fieldpopup, $this->_columnTypes[$column_index],
1331                 $column_index, $titles, $GLOBALS['cfg']['ForeignKeyMaxLimit'],
1332                 '', false, true
1333             );
1334             $html_output .= '</th></tr>';
1335         }
1336         $html_output .= '</tbody></table>';
1337         $html_output .= '</div>';
1338         $html_output .= '<input type="hidden" id="queryID" name="sql_query" />';
1339         $html_output .= '</form>';
1340         return $html_output;
1341     }
1342
1343     /**
1344      * Displays the 'Find and Replace' form
1345      *
1346      * @return string HTML for 'Find and Replace' form
1347      */
1348     function _getSearchAndReplaceHTML()
1349     {
1350         $htmlOutput  = __('Find:')
1351             . '<input type="text" value="" name="find" required />';
1352         $htmlOutput .= __('Replace with:')
1353             . '<input type="text" value="" name="replaceWith" required />';
1354
1355         $htmlOutput .= __('Column:') . '<select name="columnIndex">';
1356         for ($i = 0, $nb = count($this->_columnNames); $i < $nb; $i++) {
1357             $type = preg_replace('@\(.*@s', '', $this->_columnTypes[$i]);
1358             if ($GLOBALS['PMA_Types']->getTypeClass($type) == 'CHAR') {
1359                 $column = $this->_columnNames[$i];
1360                 $htmlOutput .= '<option value="' . $i . '">'
1361                     . htmlspecialchars($column) . '</option>';
1362             }
1363         }
1364         $htmlOutput .= '</select>';
1365         return $htmlOutput;
1366     }
1367
1368     /**
1369      * Returns HTML for prviewing strings found and their replacements
1370      *
1371      * @param int    $columnIndex index of the column
1372      * @param string $find        string to find in the column
1373      * @param string $replaceWith string to replace with
1374      * @param string $charSet     character set of the connection
1375      *
1376      * @return string HTML for prviewing strings found and their replacements
1377      */
1378     function getReplacePreview($columnIndex, $find, $replaceWith, $charSet)
1379     {
1380         $column = $this->_columnNames[$columnIndex];
1381         $sql_query = "SELECT "
1382             . PMA_Util::backquote($column) . ","
1383             . " REPLACE("
1384             . PMA_Util::backquote($column) . ", '" . $find . "', '" . $replaceWith
1385             . "'),"
1386             . " COUNT(*)"
1387             . " FROM " . PMA_Util::backquote($this->_db)
1388             . "." . PMA_Util::backquote($this->_table)
1389             . " WHERE " . PMA_Util::backquote($column)
1390             . " LIKE '%" . $find . "%' COLLATE " . $charSet . "_bin"; // here we
1391             // change the collation of the 2nd operand to a case sensitive
1392             // binary collation to make sure that the comparison is case sensitive
1393         $sql_query .= " GROUP BY " . PMA_Util::backquote($column)
1394             . " ORDER BY " . PMA_Util::backquote($column) . " ASC";
1395
1396         $resultSet = $GLOBALS['dbi']->query(
1397             $sql_query, null, PMA_DatabaseInterface::QUERY_STORE
1398         );
1399
1400         $htmlOutput = '<form method="post" action="tbl_find_replace.php"'
1401             . ' name="previewForm" id="previewForm" class="ajax">';
1402         $htmlOutput .= PMA_URL_getHiddenInputs($this->_db, $this->_table);
1403         $htmlOutput .= '<input type="hidden" name="replace" value="true" />';
1404         $htmlOutput .= '<input type="hidden" name="columnIndex" value="'
1405             . $columnIndex . '" />';
1406         $htmlOutput .= '<input type="hidden" name="findString"'
1407             . ' value="' . $find . '" />';
1408         $htmlOutput .= '<input type="hidden" name="replaceWith"'
1409             . ' value="' . $replaceWith . '" />';
1410
1411         $htmlOutput .= '<fieldset id="fieldset_find_replace_preview">';
1412         $htmlOutput .= '<legend>' . __('Find and replace - preview') . '</legend>';
1413
1414         $htmlOutput .= '<table id="previewTable">'
1415             . '<thead><tr>'
1416             . '<th>' . __('Count') . '</th>'
1417             . '<th>' . __('Original string') . '</th>'
1418             . '<th>' . __('Replaced string') . '</th>'
1419             . '</tr></thead>';
1420
1421         $htmlOutput .= '<tbody>';
1422         $odd = true;
1423         while ($row = $GLOBALS['dbi']->fetchRow($resultSet)) {
1424             $val = $row[0];
1425             $replaced = $row[1];
1426             $count = $row[2];
1427
1428             $htmlOutput .= '<tr class="' . ($odd ? 'odd' : 'even') . '">';
1429             $htmlOutput .= '<td class="right">' . htmlspecialchars($count) . '</td>';
1430             $htmlOutput .= '<td>' . htmlspecialchars($val) . '</td>';
1431             $htmlOutput .= '<td>' . htmlspecialchars($replaced) . '</td>';
1432             $htmlOutput .= '</tr>';
1433
1434             $odd = ! $odd;
1435         }
1436         $htmlOutput .= '</tbody>';
1437         $htmlOutput .= '</table>';
1438         $htmlOutput .= '</fieldset>';
1439
1440         $htmlOutput .= '<fieldset class="tblFooters">';
1441         $htmlOutput .= '<input type="submit" name="replace"'
1442             . ' value="' . __('Replace') . '" />';
1443         $htmlOutput .= '</fieldset>';
1444
1445         $htmlOutput .= '</form>';
1446         return $htmlOutput;
1447     }
1448
1449     /**
1450      * Replaces a given string in a column with a give replacement
1451      *
1452      * @param int    $columnIndex index of the column
1453      * @param string $find        string to find in the column
1454      * @param string $replaceWith string to replace with
1455      * @param string $charSet     character set of the connection
1456      *
1457      * @return void
1458      */
1459     function replace($columnIndex, $find, $replaceWith, $charSet)
1460     {
1461         $column = $this->_columnNames[$columnIndex];
1462         $sql_query = "UPDATE " . PMA_Util::backquote($this->_db)
1463             . "." . PMA_Util::backquote($this->_table)
1464             . " SET " . PMA_Util::backquote($column) . " ="
1465             . " REPLACE("
1466             . PMA_Util::backquote($column) . ", '" . $find . "', '" . $replaceWith
1467             . "')"
1468             . " WHERE " . PMA_Util::backquote($column)
1469             . " LIKE '%" . $find . "%' COLLATE " . $charSet . "_bin"; // here we
1470             // change the collation of the 2nd operand to a case sensitive
1471             // binary collation to make sure that the comparison is case sensitive
1472         $GLOBALS['dbi']->query(
1473             $sql_query, null, PMA_DatabaseInterface::QUERY_STORE
1474         );
1475         $GLOBALS['sql_query'] = $sql_query;
1476     }
1477 }
1478 ?>