phpmyadmin/libraries/DBQbe.class.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * Handles DB QBE search
   5  *
   6  * @package PhpMyAdmin
   7  */
   8 if (! defined('PHPMYADMIN')) {
   9     exit;
  10 }
  11
  12 /**
  13  * Class to handle database QBE search
  14  *
  15  * @package PhpMyAdmin
  16  */
  17 class PMA_DbQbe
  18 {
  19     /**
  20      * Database name
  21      *
  22      * @access private
  23      * @var string
  24      */
  25     private $_db;
  26     /**
  27      * Table Names (selected/non-selected)
  28      *
  29      * @access private
  30      * @var array
  31      */
  32     private $_criteriaTables;
  33     /**
  34      * Column Names
  35      *
  36      * @access private
  37      * @var array
  38      */
  39     private $_columnNames;
  40     /**
  41      * Number of columns
  42      *
  43      * @access private
  44      * @var integer
  45      */
  46     private $_criteria_column_count;
  47     /**
  48      * Number of Rows
  49      *
  50      * @access private
  51      * @var integer
  52      */
  53     private $_criteria_row_count;
  54     /**
  55      * Whether to insert a new column
  56      *
  57      * @access private
  58      * @var array
  59      */
  60     private $_criteriaColumnInsert;
  61     /**
  62      * Whether to delete a column
  63      *
  64      * @access private
  65      * @var array
  66      */
  67     private $_criteriaColumnDelete;
  68     /**
  69      * Whether to insert a new row
  70      *
  71      * @access private
  72      * @var array
  73      */
  74     private $_criteriaRowInsert;
  75     /**
  76      * Whether to delete a row
  77      *
  78      * @access private
  79      * @var array
  80      */
  81     private $_criteriaRowDelete;
  82     /**
  83      * Already set criteria values
  84      *
  85      * @access private
  86      * @var array
  87      */
  88     private $_criteria;
  89     /**
  90      * Previously set criteria values
  91      *
  92      * @access private
  93      * @var array
  94      */
  95     private $_prev_criteria;
  96     /**
  97      * AND/OR relation b/w criteria columns
  98      *
  99      * @access private
 100      * @var array
 101      */
 102     private $_criteriaAndOrColumn;
 103     /**
 104      * AND/OR relation b/w criteria rows
 105      *
 106      * @access private
 107      * @var array
 108      */
 109     private $_criteriaAndOrRow;
 110     /**
 111      * Larget width of a column
 112      *
 113      * @access private
 114      * @var string
 115      */
 116     private $_realwidth;
 117     /**
 118      * Minimum width of a column
 119      *
 120      * @access private
 121      * @var int
 122      */
 123     private $_form_column_width;
 124     /**
 125      * Current criteria field
 126      *
 127      * @access private
 128      * @var array
 129      */
 130     private $_curField;
 131     /**
 132      * Current criteria Sort options
 133      *
 134      * @access private
 135      * @var array
 136      */
 137     private $_curSort;
 138     /**
 139      * Current criteria Show options
 140      *
 141      * @access private
 142      * @var array
 143      */
 144     private $_curShow;
 145     /**
 146      * Current criteria values
 147      *
 148      * @access private
 149      * @var array
 150      */
 151     private $_curCriteria;
 152     /**
 153      * Current criteria AND/OR column relations
 154      *
 155      * @access private
 156      * @var array
 157      */
 158     private $_curAndOrCol;
 159     /**
 160      * Current criteria AND/OR row relations
 161      *
 162      * @access private
 163      * @var array
 164      */
 165     private $_curAndOrRow;
 166     /**
 167      * New column count in case of add/delete
 168      *
 169      * @access private
 170      * @var integer
 171      */
 172     private $_new_column_count;
 173     /**
 174      * New row count in case of add/delete
 175      *
 176      * @access private
 177      * @var integer
 178      */
 179     private $_new_row_count;
 180     /**
 181      * List of saved searches
 182      *
 183      * @access private
 184      * @var array
 185      */
 186     private $_savedSearchList = null;
 187     /**
 188      * Current search
 189      *
 190      * @access private
 191      * @var PMA_SavedSearches
 192      */
 193     private $_currentSearch = null;
 194
 195     /**
 196      * Initialize criterias
 197      *
 198      * @return static
 199      */
 200     private function _loadCriterias()
 201     {
 202         if (null === $this->_currentSearch
 203             || null === $this->_currentSearch->getCriterias()
 204         ) {
 205             return $this;
 206         }
 207
 208         $criterias = $this->_currentSearch->getCriterias();
 209         $_REQUEST = $criterias + $_REQUEST;
 210
 211         return $this;
 212     }
 213
 214     /**
 215      * Getter for current search
 216      *
 217      * @return PMA_SavedSearches
 218      */
 219     private function _getCurrentSearch()
 220     {
 221         return $this->_currentSearch;
 222     }
 223
 224     /**
 225      * Public Constructor
 226      *
 227      * @param string            $dbname          Database name
 228      * @param array             $savedSearchList List of saved searches
 229      * @param PMA_SavedSearches $currentSearch   Current search id
 230      */
 231     public function __construct(
 232         $dbname, $savedSearchList = array(), $currentSearch = null
 233     ) {
 234         $this->_db = $dbname;
 235         $this->_savedSearchList = $savedSearchList;
 236         $this->_currentSearch = $currentSearch;
 237         $this->_loadCriterias();
 238         // Sets criteria parameters
 239         $this->_setSearchParams();
 240         $this->_setCriteriaTablesAndColumns();
 241     }
 242
 243     /**
 244      * Sets search parameters
 245      *
 246      * @return void
 247      */
 248     private function _setSearchParams()
 249     {
 250         $criteriaColumnCount = $this->_initializeCriteriasCount();
 251
 252         $this->_criteriaColumnInsert = PMA_ifSetOr(
 253             $_REQUEST['criteriaColumnInsert'],
 254             null,
 255             'array'
 256         );
 257         $this->_criteriaColumnDelete = PMA_ifSetOr(
 258             $_REQUEST['criteriaColumnDelete'],
 259             null,
 260             'array'
 261         );
 262
 263         $this->_prev_criteria = isset($_REQUEST['prev_criteria'])
 264             ? $_REQUEST['prev_criteria']
 265             : array();
 266         $this->_criteria = isset($_REQUEST['criteria'])
 267             ? $_REQUEST['criteria']
 268             : array_fill(0, $criteriaColumnCount, '');
 269
 270         $this->_criteriaRowInsert = isset($_REQUEST['criteriaRowInsert'])
 271             ? $_REQUEST['criteriaRowInsert']
 272             : array_fill(0, $criteriaColumnCount, '');
 273         $this->_criteriaRowDelete = isset($_REQUEST['criteriaRowDelete'])
 274             ? $_REQUEST['criteriaRowDelete']
 275             : array_fill(0, $criteriaColumnCount, '');
 276         $this->_criteriaAndOrRow = isset($_REQUEST['criteriaAndOrRow'])
 277             ? $_REQUEST['criteriaAndOrRow']
 278             : array_fill(0, $criteriaColumnCount, '');
 279         $this->_criteriaAndOrColumn = isset($_REQUEST['criteriaAndOrColumn'])
 280             ? $_REQUEST['criteriaAndOrColumn']
 281             : array_fill(0, $criteriaColumnCount, '');
 282         // sets minimum width
 283         $this->_form_column_width = 12;
 284         $this->_curField = array();
 285         $this->_curSort = array();
 286         $this->_curShow = array();
 287         $this->_curCriteria = array();
 288         $this->_curAndOrRow = array();
 289         $this->_curAndOrCol = array();
 290     }
 291
 292     /**
 293      * Sets criteria tables and columns
 294      *
 295      * @return void
 296      */
 297     private function _setCriteriaTablesAndColumns()
 298     {
 299         // The tables list sent by a previously submitted form
 300         if (PMA_isValid($_REQUEST['TableList'], 'array')) {
 301             foreach ($_REQUEST['TableList'] as $each_table) {
 302                 $this->_criteriaTables[$each_table] = ' selected="selected"';
 303             }
 304         } // end if
 305         $all_tables = $GLOBALS['dbi']->query(
 306             'SHOW TABLES FROM ' . PMA_Util::backquote($this->_db) . ';',
 307             null,
 308             PMA_DatabaseInterface::QUERY_STORE
 309         );
 310         $all_tables_count = $GLOBALS['dbi']->numRows($all_tables);
 311         if (0 == $all_tables_count) {
 312             PMA_Message::error(__('No tables found in database.'))->display();
 313             exit;
 314         }
 315         // The tables list gets from MySQL
 316         while (list($table) = $GLOBALS['dbi']->fetchRow($all_tables)) {
 317             $columns = $GLOBALS['dbi']->getColumns($this->_db, $table);
 318
 319             if (empty($this->_criteriaTables[$table])
 320                 && ! empty($_REQUEST['TableList'])
 321             ) {
 322                 $this->_criteriaTables[$table] = '';
 323             } else {
 324                 $this->_criteriaTables[$table] = ' selected="selected"';
 325             } //  end if
 326
 327             // The fields list per selected tables
 328             if ($this->_criteriaTables[$table] == ' selected="selected"') {
 329                 $each_table = PMA_Util::backquote($table);
 330                 $this->_columnNames[]  = $each_table . '.*';
 331                 foreach ($columns as $each_column) {
 332                     $each_column = $each_table . '.'
 333                         . PMA_Util::backquote($each_column['Field']);
 334                     $this->_columnNames[] = $each_column;
 335                     // increase the width if necessary
 336                     $this->_form_column_width = max(
 337                         strlen($each_column),
 338                         $this->_form_column_width
 339                     );
 340                 } // end foreach
 341             } // end if
 342         } // end while
 343         $GLOBALS['dbi']->freeResult($all_tables);
 344
 345         // sets the largest width found
 346         $this->_realwidth = $this->_form_column_width . 'ex';
 347     }
 348     /**
 349      * Provides select options list containing column names
 350      *
 351      * @param integer $column_number Column Number (0,1,2) or more
 352      * @param string  $selected      Selected criteria column name
 353      *
 354      * @return string HTML for select options
 355      */
 356     private function _showColumnSelectCell($column_number, $selected = '')
 357     {
 358         $html_output = '';
 359         $html_output .= '<td class="center">';
 360         $html_output .= '<select name="criteriaColumn[' . $column_number
 361             . ']" size="1">';
 362         $html_output .= '<option value="">&nbsp;</option>';
 363         foreach ($this->_columnNames as $column) {
 364             $html_output .= '<option value="' . htmlspecialchars($column) . '"'
 365                 . (($column === $selected) ? ' selected="selected"' : '') . '>'
 366                 . str_replace(' ', '&nbsp;', htmlspecialchars($column))
 367                 . '</option>';
 368         }
 369         $html_output .= '</select>';
 370         $html_output .= '</td>';
 371         return $html_output;
 372     }
 373
 374     /**
 375      * Provides select options list containing sort options (ASC/DESC)
 376      *
 377      * @param integer $column_number Column Number (0,1,2) or more
 378      * @param string  $asc_selected  Selected criteria 'Ascending'
 379      * @param string  $desc_selected Selected criteria 'Descending'
 380      *
 381      * @return string HTML for select options
 382      */
 383     private function _getSortSelectCell($column_number, $asc_selected = '',
 384         $desc_selected = ''
 385     ) {
 386         $html_output = '<td class="center">';
 387         $html_output .= '<select style="width: ' . $this->_realwidth
 388             . '" name="criteriaSort[' . $column_number . ']" size="1">';
 389         $html_output .= '<option value="">&nbsp;</option>';
 390         $html_output .= '<option value="ASC"' . $asc_selected . '>'
 391             . __('Ascending')
 392             . '</option>';
 393         $html_output .= '<option value="DESC"' . $desc_selected . '>'
 394             . __('Descending')
 395             . '</option>';
 396         $html_output .= '</select>';
 397         $html_output .= '</td>';
 398         return $html_output;
 399     }
 400
 401     /**
 402      * Provides search form's row containing column select options
 403      *
 404      * @return string HTML for search table's row
 405      */
 406     private function _getColumnNamesRow()
 407     {
 408         $html_output = '<tr class="odd noclick">';
 409         $html_output .= '<th>' . __('Column:') . '</th>';
 410         $new_column_count = 0;
 411         for (
 412             $column_index = 0;
 413             $column_index < $this->_criteria_column_count;
 414             $column_index++
 415         ) {
 416             if (isset($this->_criteriaColumnInsert[$column_index])
 417                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 418             ) {
 419                 $html_output .= $this->_showColumnSelectCell(
 420                     $new_column_count
 421                 );
 422                 $new_column_count++;
 423             }
 424             if (! empty($this->_criteriaColumnDelete)
 425                 && isset($this->_criteriaColumnDelete[$column_index])
 426                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 427             ) {
 428                 continue;
 429             }
 430             $selected = '';
 431             if (isset($_REQUEST['criteriaColumn'][$column_index])) {
 432                 $selected = $_REQUEST['criteriaColumn'][$column_index];
 433                 $this->_curField[$new_column_count]
 434                     = $_REQUEST['criteriaColumn'][$column_index];
 435             }
 436             $html_output .= $this->_showColumnSelectCell(
 437                 $new_column_count,
 438                 $selected
 439             );
 440             $new_column_count++;
 441         } // end for
 442         $this->_new_column_count = $new_column_count;
 443         $html_output .= '</tr>';
 444         return $html_output;
 445     }
 446
 447     /**
 448      * Provides search form's row containing sort(ASC/DESC) select options
 449      *
 450      * @return string HTML for search table's row
 451      */
 452     private function _getSortRow()
 453     {
 454         $html_output = '<tr class="even noclick">';
 455         $html_output .= '<th>' . __('Sort:') . '</th>';
 456         $new_column_count = 0;
 457         for (
 458             $column_index = 0;
 459             $column_index < $this->_criteria_column_count;
 460             $column_index++
 461         ) {
 462             if (! empty($this->_criteriaColumnInsert)
 463                 && isset($this->_criteriaColumnInsert[$column_index])
 464                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 465             ) {
 466                 $html_output .= $this->_getSortSelectCell($new_column_count);
 467                 $new_column_count++;
 468             } // end if
 469
 470             if (! empty($this->_criteriaColumnDelete)
 471                 && isset($this->_criteriaColumnDelete[$column_index])
 472                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 473             ) {
 474                 continue;
 475             }
 476             // If they have chosen all fields using the * selector,
 477             // then sorting is not available, Fix for Bug #570698
 478             if (isset($_REQUEST['criteriaSort'][$column_index])
 479                 && isset($_REQUEST['criteriaColumn'][$column_index])
 480                 && substr($_REQUEST['criteriaColumn'][$column_index], -2) == '.*'
 481             ) {
 482                 $_REQUEST['criteriaSort'][$column_index] = '';
 483             } //end if
 484             // Set asc_selected
 485             if (isset($_REQUEST['criteriaSort'][$column_index])
 486                 && $_REQUEST['criteriaSort'][$column_index] == 'ASC'
 487             ) {
 488                 $this->_curSort[$new_column_count]
 489                     = $_REQUEST['criteriaSort'][$column_index];
 490                 $asc_selected = ' selected="selected"';
 491             } else {
 492                 $asc_selected = '';
 493             } // end if
 494             // Set desc selected
 495             if (isset($_REQUEST['criteriaSort'][$column_index])
 496                 && $_REQUEST['criteriaSort'][$column_index] == 'DESC'
 497             ) {
 498                 $this->_curSort[$new_column_count]
 499                     = $_REQUEST['criteriaSort'][$column_index];
 500                 $desc_selected = ' selected="selected"';
 501             } else {
 502                 $desc_selected = '';
 503             } // end if
 504             $html_output .= $this->_getSortSelectCell(
 505                 $new_column_count, $asc_selected, $desc_selected
 506             );
 507             $new_column_count++;
 508         } // end for
 509         $html_output .= '</tr>';
 510         return $html_output;
 511     }
 512
 513     /**
 514      * Provides search form's row containing SHOW checkboxes
 515      *
 516      * @return string HTML for search table's row
 517      */
 518     private function _getShowRow()
 519     {
 520         $html_output = '<tr class="odd noclick">';
 521         $html_output .= '<th>' . __('Show:') . '</th>';
 522         $new_column_count = 0;
 523         for (
 524             $column_index = 0;
 525             $column_index < $this->_criteria_column_count;
 526             $column_index++
 527         ) {
 528             if (! empty($this->_criteriaColumnInsert)
 529                 && isset($this->_criteriaColumnInsert[$column_index])
 530                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 531             ) {
 532                 $html_output .= '<td class="center">';
 533                 $html_output .= '<input type="checkbox"'
 534                     . ' name="criteriaShow[' . $new_column_count . ']" />';
 535                 $html_output .= '</td>';
 536                 $new_column_count++;
 537             } // end if
 538             if (! empty($this->_criteriaColumnDelete)
 539                 && isset($this->_criteriaColumnDelete[$column_index])
 540                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 541             ) {
 542                 continue;
 543             }
 544             if (isset($_REQUEST['criteriaShow'][$column_index])) {
 545                 $checked_options = ' checked="checked"';
 546                 $this->_curShow[$new_column_count]
 547                     = $_REQUEST['criteriaShow'][$column_index];
 548             } else {
 549                 $checked_options = '';
 550             }
 551             $html_output .= '<td class="center">';
 552             $html_output .= '<input type="checkbox"'
 553                 . ' name="criteriaShow[' . $new_column_count . ']"'
 554                 . $checked_options . ' />';
 555             $html_output .= '</td>';
 556             $new_column_count++;
 557         } // end for
 558         $html_output .= '</tr>';
 559         return $html_output;
 560     }
 561
 562     /**
 563      * Provides search form's row containing criteria Inputboxes
 564      *
 565      * @return string HTML for search table's row
 566      */
 567     private function _getCriteriaInputboxRow()
 568     {
 569         $html_output = '<tr class="even noclick">';
 570         $html_output .= '<th>' . __('Criteria:') . '</th>';
 571         $new_column_count = 0;
 572         for (
 573             $column_index = 0;
 574             $column_index < $this->_criteria_column_count;
 575             $column_index++
 576         ) {
 577             if (! empty($this->_criteriaColumnInsert)
 578                 && isset($this->_criteriaColumnInsert[$column_index])
 579                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 580             ) {
 581                 $html_output .= '<td class="center">';
 582                 $html_output .= '<input type="text"'
 583                     . ' name="criteria[' . $new_column_count . ']"'
 584                     . ' value=""'
 585                     . ' class="textfield"'
 586                     . ' style="width: ' . $this->_realwidth . '"'
 587                     . ' size="20" />';
 588                 $html_output .= '</td>';
 589                 $new_column_count++;
 590             } // end if
 591             if (! empty($this->_criteriaColumnDelete)
 592                 && isset($this->_criteriaColumnDelete[$column_index])
 593                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 594             ) {
 595                 continue;
 596             }
 597             if (isset($this->_criteria[$column_index])) {
 598                 $tmp_criteria = $this->_criteria[$column_index];
 599             }
 600             if ((empty($this->_prev_criteria)
 601                 || ! isset($this->_prev_criteria[$column_index]))
 602                 || $this->_prev_criteria[$column_index] != htmlspecialchars($tmp_criteria)
 603             ) {
 604                 $this->_curCriteria[$new_column_count] = $tmp_criteria;
 605             } else {
 606                 $this->_curCriteria[$new_column_count]
 607                     = $this->_prev_criteria[$column_index];
 608             }
 609             $html_output .= '<td class="center">';
 610             $html_output .= '<input type="hidden"'
 611                 . ' name="prev_criteria[' . $new_column_count . ']"'
 612                 . ' value="'
 613                 . htmlspecialchars($this->_curCriteria[$new_column_count])
 614                 . '" />';
 615             $html_output .= '<input type="text"'
 616                 . ' name="criteria[' . $new_column_count . ']"'
 617                 . ' value="' . htmlspecialchars($tmp_criteria) . '"'
 618                 . ' class="textfield"'
 619                 . ' style="width: ' . $this->_realwidth . '"'
 620                 . ' size="20" />';
 621             $html_output .= '</td>';
 622             $new_column_count++;
 623         } // end for
 624         $html_output .= '</tr>';
 625         return $html_output;
 626     }
 627
 628     /**
 629      * Provides footer options for adding/deleting row/columns
 630      *
 631      * @param string $type Whether row or column
 632      *
 633      * @return string HTML for footer options
 634      */
 635     private function _getFootersOptions($type)
 636     {
 637         $html_output = '<div class="floatleft">';
 638         $html_output .= (($type == 'row')
 639             ? __('Add/Delete criteria rows') : __('Add/Delete columns'));
 640         $html_output .= ':<select size="1" name="'
 641             . (($type == 'row') ? 'criteriaRowAdd' : 'criteriaColumnAdd') . '">';
 642         $html_output .= '<option value="-3">-3</option>';
 643         $html_output .= '<option value="-2">-2</option>';
 644         $html_output .= '<option value="-1">-1</option>';
 645         $html_output .= '<option value="0" selected="selected">0</option>';
 646         $html_output .= '<option value="1">1</option>';
 647         $html_output .= '<option value="2">2</option>';
 648         $html_output .= '<option value="3">3</option>';
 649         $html_output .= '</select>';
 650         $html_output .= '</div>';
 651         return $html_output;
 652     }
 653
 654     /**
 655      * Provides search form table's footer options
 656      *
 657      * @return string HTML for table footer
 658      */
 659     private function _getTableFooters()
 660     {
 661         $html_output = '<fieldset class="tblFooters">';
 662         $html_output .= $this->_getFootersOptions("row");
 663         $html_output .= $this->_getFootersOptions("column");
 664         $html_output .= '<div class="floatleft">';
 665         $html_output .= '<input type="submit" name="modify"'
 666             . 'value="' . __('Update Query') . '" />';
 667         $html_output .= '</div>';
 668         $html_output .= '</fieldset>';
 669         return $html_output;
 670     }
 671
 672     /**
 673      * Provides a select list of database tables
 674      *
 675      * @return string HTML for table select list
 676      */
 677     private function _getTablesList()
 678     {
 679         $html_output = '<div class="floatleft">';
 680         $html_output .= '<fieldset>';
 681         $html_output .= '<legend>' . __('Use Tables') . '</legend>';
 682         // Build the options list for each table name
 683         $options = '';
 684         $numTableListOptions = 0;
 685         foreach ($this->_criteriaTables as $key => $val) {
 686             $options .= '<option value="' . htmlspecialchars($key) . '"' . $val . '>'
 687                 . (str_replace(' ', '&nbsp;', htmlspecialchars($key))) . '</option>';
 688             $numTableListOptions++;
 689         }
 690         $html_output .= '<select name="TableList[]"'
 691             . ' multiple="multiple" id="listTable"'
 692             . ' size="' . (($numTableListOptions > 30) ? '15' : '7') . '">';
 693         $html_output .= $options;
 694         $html_output .= '</select>';
 695         $html_output .= '</fieldset>';
 696         $html_output .= '<fieldset class="tblFooters">';
 697         $html_output .= '<input type="submit" name="modify" value="'
 698             . __('Update Query') . '" />';
 699         $html_output .= '</fieldset>';
 700         $html_output .= '</div>';
 701         return $html_output;
 702     }
 703
 704     /**
 705      * Provides And/Or modification cell along with Insert/Delete options
 706      * (For modifying search form's table columns)
 707      *
 708      * @param integer $column_number Column Number (0,1,2) or more
 709      * @param array   $selected      Selected criteria column name
 710      *
 711      * @return string HTML for modification cell
 712      */
 713     private function _getAndOrColCell($column_number, $selected = null)
 714     {
 715         $html_output = '<td class="center">';
 716         $html_output .= '<strong>' . __('Or:') . '</strong>';
 717         $html_output .= '<input type="radio"'
 718             . ' name="criteriaAndOrColumn[' . $column_number . ']"'
 719             . ' value="or"' . $selected['or'] . ' />';
 720         $html_output .= '&nbsp;&nbsp;<strong>' . __('And:') . '</strong>';
 721         $html_output .= '<input type="radio"'
 722             . ' name="criteriaAndOrColumn[' . $column_number . ']"'
 723             . ' value="and"' . $selected['and'] . ' />';
 724         $html_output .= '<br />' . __('Ins');
 725         $html_output .= '<input type="checkbox"'
 726             . ' name="criteriaColumnInsert[' . $column_number . ']" />';
 727         $html_output .= '&nbsp;&nbsp;' . __('Del');
 728         $html_output .= '<input type="checkbox"'
 729             . ' name="criteriaColumnDelete[' . $column_number . ']" />';
 730         $html_output .= '</td>';
 731         return $html_output;
 732     }
 733
 734     /**
 735      * Provides search form's row containing column modifications options
 736      * (For modifying search form's table columns)
 737      *
 738      * @return string HTML for search table's row
 739      */
 740     private function _getModifyColumnsRow()
 741     {
 742         $html_output = '<tr class="even noclick">';
 743         $html_output .= '<th>' . __('Modify:') . '</th>';
 744         $new_column_count = 0;
 745         for (
 746         $column_index = 0;
 747         $column_index < $this->_criteria_column_count;
 748         $column_index++
 749         ) {
 750             if (! empty($this->_criteriaColumnInsert)
 751                 && isset($this->_criteriaColumnInsert[$column_index])
 752                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 753             ) {
 754                 $html_output .= $this->_getAndOrColCell($new_column_count);
 755                 $new_column_count++;
 756             } // end if
 757
 758             if (! empty($this->_criteriaColumnDelete)
 759                 && isset($this->_criteriaColumnDelete[$column_index])
 760                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 761             ) {
 762                 continue;
 763             }
 764
 765             if (isset($this->_criteriaAndOrColumn[$column_index])) {
 766                 $this->_curAndOrCol[$new_column_count]
 767                     = $this->_criteriaAndOrColumn[$column_index];
 768             }
 769             $checked_options = array();
 770             if (isset($this->_criteriaAndOrColumn[$column_index])
 771                 && $this->_criteriaAndOrColumn[$column_index] == 'or'
 772             ) {
 773                 $checked_options['or']  = ' checked="checked"';
 774                 $checked_options['and'] = '';
 775             } else {
 776                 $checked_options['and'] = ' checked="checked"';
 777                 $checked_options['or']  = '';
 778             }
 779             $html_output .= $this->_getAndOrColCell(
 780                 $new_column_count,
 781                 $checked_options
 782             );
 783             $new_column_count++;
 784         } // end for
 785         $html_output .= '</tr>';
 786         return $html_output;
 787     }
 788
 789     /**
 790      * Provides Insert/Delete options for criteria inputbox
 791      * with AND/OR relationship modification options
 792      *
 793      * @param integer $row_index       Number of criteria row
 794      * @param array   $checked_options If checked
 795      *
 796      * @return string HTML
 797      */
 798     private function _getInsDelAndOrCell($row_index, $checked_options)
 799     {
 800         $html_output = '<td class="' . $GLOBALS['cell_align_right'] . ' nowrap">';
 801         $html_output .= '<!-- Row controls -->';
 802         $html_output .= '<table class="nospacing nopadding">';
 803         $html_output .= '<tr>';
 804         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . ' nowrap">';
 805         $html_output .= '<small>' . __('Ins:') . '</small>';
 806         $html_output .= '<input type="checkbox"'
 807             . ' name="criteriaRowInsert[' . $row_index . ']" />';
 808         $html_output .= '</td>';
 809         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . '">';
 810         $html_output .= '<strong>' . __('And:') . '</strong>';
 811         $html_output .= '</td>';
 812         $html_output .= '<td>';
 813         $html_output .= '<input type="radio"'
 814             . ' name="criteriaAndOrRow[' . $row_index . ']" value="and"'
 815             . $checked_options['and'] . ' />';
 816         $html_output .= '</td>';
 817         $html_output .= '</tr>';
 818         $html_output .= '<tr>';
 819         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . ' nowrap">';
 820         $html_output .= '<small>' . __('Del:') . '</small>';
 821         $html_output .= '<input type="checkbox"'
 822             . ' name="criteriaRowDelete[' . $row_index . ']" />';
 823         $html_output .= '</td>';
 824         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . '">';
 825         $html_output .= '<strong>' . __('Or:') . '</strong>';
 826         $html_output .= '</td>';
 827         $html_output .= '<td>';
 828         $html_output .= '<input type="radio"'
 829             . ' name="criteriaAndOrRow[' . $row_index . ']"'
 830             . ' value="or"' . $checked_options['or'] . ' />';
 831         $html_output .= '</td>';
 832         $html_output .= '</tr>';
 833         $html_output .= '</table>';
 834         $html_output .= '</td>';
 835         return $html_output;
 836     }
 837
 838     /**
 839      * Provides rows for criteria inputbox Insert/Delete options
 840      * with AND/OR relationship modification options
 841      *
 842      * @param integer $new_row_index New row index if rows are added/deleted
 843      * @param integer $row_index     Row index
 844      *
 845      * @return string HTML table rows
 846      */
 847     private function _getInputboxRow($new_row_index, $row_index)
 848     {
 849         $html_output = '';
 850         $new_column_count = 0;
 851         for (
 852             $column_index = 0;
 853             $column_index < $this->_criteria_column_count;
 854             $column_index++
 855         ) {
 856             if (!empty($this->_criteriaColumnInsert)
 857                 && isset($this->_criteriaColumnInsert[$column_index])
 858                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 859             ) {
 860                 $orFieldName = 'Or' . $new_row_index . '[' . $new_column_count . ']';
 861                 $html_output .= '<td class="center">';
 862                 $html_output .= '<input type="text"'
 863                     . ' name="Or' . $orFieldName . '" class="textfield"'
 864                     . ' style="width: ' . $this->_realwidth . '" size="20" />';
 865                 $html_output .= '</td>';
 866                 $new_column_count++;
 867             } // end if
 868             if (!empty($this->_criteriaColumnDelete)
 869                 && isset($this->_criteriaColumnDelete[$column_index])
 870                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 871             ) {
 872                 continue;
 873             }
 874             $or = 'Or' . $new_row_index;
 875             if (! empty($_REQUEST[$or]) && isset($_REQUEST[$or][$column_index])) {
 876                 $tmp_or = $_REQUEST[$or][$column_index];
 877             } else {
 878                 $tmp_or     = '';
 879             }
 880             $html_output .= '<td class="center">';
 881             $html_output .= '<input type="text"'
 882                 . ' name="Or' . $new_row_index . '[' . $new_column_count . ']' . '"'
 883                 . ' value="' . htmlspecialchars($tmp_or) . '" class="textfield"'
 884                 . ' style="width: ' . $this->_realwidth . '" size="20" />';
 885             $html_output .= '</td>';
 886             if (!empty(${$or}) && isset(${$or}[$column_index])) {
 887                 $GLOBALS[${'cur' . $or}][$new_column_count]
 888                     = ${$or}[$column_index];
 889             }
 890             $new_column_count++;
 891         } // end for
 892         return $html_output;
 893     }
 894
 895     /**
 896      * Provides rows for criteria inputbox Insert/Delete options
 897      * with AND/OR relationship modification options
 898      *
 899      * @return string HTML table rows
 900      */
 901     private function _getInsDelAndOrCriteriaRows()
 902     {
 903         $html_output = '';
 904         $new_row_count = 0;
 905         $odd_row = true;
 906         $checked_options = array();
 907         for (
 908         $row_index = 0;
 909         $row_index <= $this->_criteria_row_count;
 910         $row_index++
 911         ) {
 912             if (isset($this->_criteriaRowInsert[$row_index])
 913                 && $this->_criteriaRowInsert[$row_index] == 'on'
 914             ) {
 915                 $checked_options['or']  = ' checked="checked"';
 916                 $checked_options['and'] = '';
 917                 $html_output .= '<tr class="' . ($odd_row ? 'odd' : 'even')
 918                     . ' noclick">';
 919                 $html_output .= $this->_getInsDelAndOrCell(
 920                     $new_row_count, $checked_options
 921                 );
 922                 $html_output .= $this->_getInputboxRow(
 923                     $new_row_count, $row_index
 924                 );
 925                 $new_row_count++;
 926                 $html_output .= '</tr>';
 927                 $odd_row =! $odd_row;
 928             } // end if
 929             if (isset($this->_criteriaRowDelete[$row_index])
 930                 && $this->_criteriaRowDelete[$row_index] == 'on'
 931             ) {
 932                 continue;
 933             }
 934             if (isset($this->_criteriaAndOrRow[$row_index])) {
 935                 $this->_curAndOrRow[$new_row_count]
 936                     = $this->_criteriaAndOrRow[$row_index];
 937             }
 938             if (isset($this->_criteriaAndOrRow[$row_index])
 939                 && $this->_criteriaAndOrRow[$row_index] == 'and'
 940             ) {
 941                 $checked_options['and'] =  ' checked="checked"';
 942                 $checked_options['or']  =  '';
 943             } else {
 944                 $checked_options['or']  =  ' checked="checked"';
 945                 $checked_options['and'] =  '';
 946             }
 947             $html_output .= '<tr class="' . ($odd_row ? 'odd' : 'even')
 948                 . ' noclick">';
 949             $html_output .= $this->_getInsDelAndOrCell(
 950                 $new_row_count, $checked_options
 951             );
 952             $html_output .= $this->_getInputboxRow(
 953                 $new_row_count, $row_index
 954             );
 955             $new_row_count++;
 956             $html_output .= '</tr>';
 957             $odd_row =! $odd_row;
 958         } // end for
 959         $this->_new_row_count = $new_row_count;
 960         return $html_output;
 961     }
 962
 963     /**
 964      * Provides SELECT clause for building SQL query
 965      *
 966      * @return string Select clause
 967      */
 968     private function _getSelectClause()
 969     {
 970         $select_clause = '';
 971         $select_clauses = array();
 972         for (
 973             $column_index = 0;
 974             $column_index < $this->_criteria_column_count;
 975             $column_index++
 976         ) {
 977             if (! empty($this->_curField[$column_index])
 978                 && isset($this->_curShow[$column_index])
 979                 && $this->_curShow[$column_index] == 'on'
 980             ) {
 981                 $select_clauses[] = $this->_curField[$column_index];
 982             }
 983         } // end for
 984         if ($select_clauses) {
 985             $select_clause = 'SELECT '
 986                 . htmlspecialchars(implode(", ", $select_clauses)) . "\n";
 987         }
 988         return $select_clause;
 989     }
 990
 991     /**
 992      * Provides WHERE clause for building SQL query
 993      *
 994      * @return string Where clause
 995      */
 996     private function _getWhereClause()
 997     {
 998         $where_clause = '';
 999         $criteria_cnt = 0;
1000         for (
1001         $column_index = 0;
1002         $column_index < $this->_criteria_column_count;
1003         $column_index++
1004         ) {
1005             if (! empty($this->_curField[$column_index])
1006                 && ! empty($this->_curCriteria[$column_index])
1007                 && $column_index
1008                 && isset($last_where)
1009                 && isset($this->_curAndOrCol)
1010             ) {
1011                 $where_clause .= ' '
1012                     . strtoupper($this->_curAndOrCol[$last_where]) . ' ';
1013             }
1014             if (! empty($this->_curField[$column_index])
1015                 && ! empty($this->_curCriteria[$column_index])
1016             ) {
1017                 $where_clause .= '(' . $this->_curField[$column_index] . ' '
1018                     . $this->_curCriteria[$column_index] . ')';
1019                 $last_where = $column_index;
1020                 $criteria_cnt++;
1021             }
1022         } // end for
1023         if ($criteria_cnt > 1) {
1024             $where_clause = '(' . $where_clause . ')';
1025         }
1026         // OR rows ${'cur' . $or}[$column_index]
1027         if (! isset($this->_curAndOrRow)) {
1028             $this->_curAndOrRow = array();
1029         }
1030         for (
1031         $row_index = 0;
1032         $row_index <= $this->_criteria_row_count;
1033         $row_index++
1034         ) {
1035             $criteria_cnt = 0;
1036             $qry_orwhere = '';
1037             $last_orwhere = '';
1038             for (
1039             $column_index = 0;
1040             $column_index < $this->_criteria_column_count;
1041             $column_index++
1042             ) {
1043                 if (! empty($this->_curField[$column_index])
1044                     && ! empty($_REQUEST['Or' . $row_index][$column_index])
1045                     && $column_index
1046                 ) {
1047                     $qry_orwhere .= ' '
1048                         . strtoupper($this->_curAndOrCol[$last_orwhere]) . ' ';
1049                 }
1050                 if (! empty($this->_curField[$column_index])
1051                     && ! empty($_REQUEST['Or' . $row_index][$column_index])
1052                 ) {
1053                     $qry_orwhere .= '(' . $this->_curField[$column_index]
1054                         .  ' '
1055                         .  $_REQUEST['Or' . $row_index][$column_index]
1056                         .  ')';
1057                     $last_orwhere = $column_index;
1058                     $criteria_cnt++;
1059                 }
1060             } // end for
1061             if ($criteria_cnt > 1) {
1062                 $qry_orwhere      = '(' . $qry_orwhere . ')';
1063             }
1064             if (! empty($qry_orwhere)) {
1065                 $where_clause .= "\n"
1066                     .  strtoupper(
1067                         isset($this->_curAndOrRow[$row_index])
1068                         ? $this->_curAndOrRow[$row_index] . ' '
1069                         : ''
1070                     )
1071                     .  $qry_orwhere;
1072             } // end if
1073         } // end for
1074
1075         if (! empty($where_clause) && $where_clause != '()') {
1076             $where_clause = 'WHERE ' . htmlspecialchars($where_clause) . "\n";
1077         } // end if
1078         return $where_clause;
1079     }
1080
1081     /**
1082      * Provides ORDER BY clause for building SQL query
1083      *
1084      * @return string Order By clause
1085      */
1086     private function _getOrderByClause()
1087     {
1088         $orderby_clause = '';
1089         $orderby_clauses = array();
1090         for (
1091             $column_index = 0;
1092             $column_index < $this->_criteria_column_count;
1093             $column_index++
1094         ) {
1095             // if all columns are chosen with * selector,
1096             // then sorting isn't available
1097             // Fix for Bug #570698
1098             if (! empty($this->_curField[$column_index])
1099                 && ! empty($this->_curSort[$column_index])
1100             ) {
1101                 if (substr($this->_curField[$column_index], -2) == '.*') {
1102                     continue;
1103                 }
1104                 $orderby_clauses[] = $this->_curField[$column_index] . ' '
1105                     . $this->_curSort[$column_index];
1106             }
1107         } // end for
1108         if ($orderby_clauses) {
1109             $orderby_clause = 'ORDER BY '
1110                 . htmlspecialchars(implode(", ", $orderby_clauses)) . "\n";
1111         }
1112         return $orderby_clause;
1113     }
1114
1115     /**
1116      * Provides UNIQUE columns and INDEX columns present in criteria tables
1117      *
1118      * @param array $all_tables           Tables involved in the search
1119      * @param array $all_columns          Columns involved in the search
1120      * @param array $where_clause_columns Columns having criteria where clause
1121      *
1122      * @return array having UNIQUE and INDEX columns
1123      */
1124     private function _getIndexes($all_tables, $all_columns,
1125         $where_clause_columns
1126     ) {
1127         $unique_columns = array();
1128         $index_columns = array();
1129
1130         foreach ($all_tables as $table) {
1131             $indexes = $GLOBALS['dbi']->getTableIndexes($this->_db, $table);
1132             foreach ($indexes as $index) {
1133                 $column = $table . '.' . $index['Column_name'];
1134                 if (isset($all_columns[$column])) {
1135                     if ($index['Non_unique'] == 0) {
1136                         if (isset($where_clause_columns[$column])) {
1137                             $unique_columns[$column] = 'Y';
1138                         } else {
1139                             $unique_columns[$column] = 'N';
1140                         }
1141                     } else {
1142                         if (isset($where_clause_columns[$column])) {
1143                             $index_columns[$column] = 'Y';
1144                         } else {
1145                             $index_columns[$column] = 'N';
1146                         }
1147                     }
1148                 }
1149             } // end while (each index of a table)
1150         } // end while (each table)
1151
1152         return array(
1153             'unique' => $unique_columns,
1154             'index' => $index_columns
1155         );
1156     }
1157
1158     /**
1159      * Provides UNIQUE columns and INDEX columns present in criteria tables
1160      *
1161      * @param array $all_tables           Tables involved in the search
1162      * @param array $all_columns          Columns involved in the search
1163      * @param array $where_clause_columns Columns having criteria where clause
1164      *
1165      * @return array having UNIQUE and INDEX columns
1166      */
1167     private function _getLeftJoinColumnCandidates($all_tables, $all_columns,
1168         $where_clause_columns
1169     ) {
1170         $GLOBALS['dbi']->selectDb($this->_db);
1171
1172         // Get unique columns and index columns
1173         $indexes = $this->_getIndexes(
1174             $all_tables, $all_columns, $where_clause_columns
1175         );
1176         $unique_columns = $indexes['unique'];
1177         $index_columns = $indexes['index'];
1178
1179         list($candidate_columns, $needsort)
1180             = $this->_getLeftJoinColumnCandidatesBest(
1181                 $all_tables, $where_clause_columns, $unique_columns, $index_columns
1182             );
1183
1184         // If we came up with $unique_columns (very good) or $index_columns (still
1185         // good) as $candidate_columns we want to check if we have any 'Y' there
1186         // (that would mean that they were also found in the whereclauses
1187         // which would be great). if yes, we take only those
1188         if ($needsort != 1) {
1189             return $candidate_columns;
1190         }
1191
1192         $vg = array();
1193         $sg = array();
1194         foreach ($candidate_columns as $column => $is_where) {
1195             $table = explode('.', $column);
1196             $table = $table[0];
1197             if ($is_where == 'Y') {
1198                 $vg[$column] = $table;
1199             } else {
1200                 $sg[$column] = $table;
1201             }
1202         }
1203         if (count($vg) > 0) {
1204             $candidate_columns = $vg;
1205             // Candidates restricted in index+where
1206         } else {
1207             $candidate_columns = $sg;
1208             // None of the candidates where in a where-clause
1209         }
1210
1211         return $candidate_columns;
1212     }
1213
1214     /**
1215      * Provides the main table to form the LEFT JOIN clause
1216      *
1217      * @param array $all_tables           Tables involved in the search
1218      * @param array $all_columns          Columns involved in the search
1219      * @param array $where_clause_columns Columns having criteria where clause
1220      * @param array $where_clause_tables  Tables having criteria where clause
1221      *
1222      * @return string table name
1223      */
1224     private function _getMasterTable($all_tables, $all_columns,
1225         $where_clause_columns, $where_clause_tables
1226     ) {
1227         if (count($where_clause_tables) == 1) {
1228             // If there is exactly one column that has a decent where-clause
1229             // we will just use this
1230             $master = key($where_clause_tables);
1231             return $master;
1232         }
1233
1234         // Now let's find out which of the tables has an index
1235         // (When the control user is the same as the normal user
1236         // because he is using one of his databases as pmadb,
1237         // the last db selected is not always the one where we need to work)
1238         $candidate_columns = $this->_getLeftJoinColumnCandidates(
1239             $all_tables, $all_columns, $where_clause_columns
1240         );
1241         // If our array of candidates has more than one member we'll just
1242         // find the smallest table.
1243         // Of course the actual query would be faster if we check for
1244         // the Criteria which gives the smallest result set in its table,
1245         // but it would take too much time to check this
1246         if (!(count($candidate_columns) > 1)) {
1247             reset($candidate_columns);
1248             $master = current($candidate_columns); // Only one single candidate
1249             return $master;
1250         }
1251
1252         // Of course we only want to check each table once
1253         $checked_tables = $candidate_columns;
1254         $tsize = array();
1255         $csize = array();
1256         foreach ($candidate_columns as $table) {
1257             if ($checked_tables[$table] != 1) {
1258                 $tsize[$table] = PMA_Table::countRecords(
1259                     $this->_db,
1260                     $table,
1261                     false
1262                 );
1263                 $checked_tables[$table] = 1;
1264             }
1265             $csize[$table] = $tsize[$table];
1266         }
1267         asort($csize);
1268         reset($csize);
1269         $master = key($csize); // Smallest
1270
1271         return $master;
1272     }
1273
1274     /**
1275      * Provides columns and tables that have valid where clause criteria
1276      *
1277      * @return array
1278      */
1279     private function _getWhereClauseTablesAndColumns()
1280     {
1281         $where_clause_columns = array();
1282         $where_clause_tables = array();
1283         // Now we need all tables that we have in the where clause
1284         for (
1285             $column_index = 0, $nb = count($this->_criteria);
1286             $column_index < $nb;
1287             $column_index++
1288         ) {
1289             $current_table = explode('.', $_POST['criteriaColumn'][$column_index]);
1290             if (empty($current_table[0]) || empty($current_table[1])) {
1291                 continue;
1292             } // end if
1293             $table = str_replace('`', '', $current_table[0]);
1294             $column = str_replace('`', '', $current_table[1]);
1295             $column = $table . '.' . $column;
1296             // Now we know that our array has the same numbers as $criteria
1297             // we can check which of our columns has a where clause
1298             if (! empty($this->_criteria[$column_index])) {
1299                 if (substr($this->_criteria[$column_index], 0, 1) == '='
1300                     || stristr($this->_criteria[$column_index], 'is')
1301                 ) {
1302                     $where_clause_columns[$column] = $column;
1303                     $where_clause_tables[$table]  = $table;
1304                 }
1305             } // end if
1306         } // end for
1307         return array(
1308             'where_clause_tables' => $where_clause_tables,
1309             'where_clause_columns' => $where_clause_columns
1310         );
1311     }
1312
1313     /**
1314      * Provides FROM clause for building SQL query
1315      *
1316      * @param array $cfgRelation Relation Settings
1317      *
1318      * @return string FROM clause
1319      */
1320     private function _getFromClause($cfgRelation)
1321     {
1322         $from_clause = '';
1323         if (isset($_POST['criteriaColumn']) && count($_POST['criteriaColumn']) > 0) {
1324             // Initialize some variables
1325             $all_tables = $all_columns = array();
1326
1327             // We only start this if we have fields, otherwise it would be dumb
1328             foreach ($_POST['criteriaColumn'] as $value) {
1329                 $parts = explode('.', $value);
1330                 if (! empty($parts[0]) && ! empty($parts[1])) {
1331                     $table = str_replace('`', '', $parts[0]);
1332                     $all_tables[$table] = $table;
1333                     $all_columns[] = $table . '.' . str_replace('`', '', $parts[1]);
1334                 }
1335             } // end while
1336
1337             // Create LEFT JOINS out of Relations
1338             if ($cfgRelation['relwork'] && count($all_tables) > 0) {
1339                 // Get tables and columns with valid where clauses
1340                 $valid_where_clauses = $this->_getWhereClauseTablesAndColumns();
1341                 $where_clause_tables = $valid_where_clauses['where_clause_tables'];
1342                 $where_clause_columns = $valid_where_clauses['where_clause_columns'];
1343                 // Get master table
1344                 $master = $this->_getMasterTable(
1345                     $all_tables, $all_columns,
1346                     $where_clause_columns, $where_clause_tables
1347                 );
1348                 $from_clause = PMA_Util::backquote($master)
1349                     . PMA_getRelatives($all_tables, $master);
1350
1351             } // end if ($cfgRelation['relwork'] && count($all_tables) > 0)
1352         } // end count($_POST['criteriaColumn']) > 0
1353
1354         // In case relations are not defined, just generate the FROM clause
1355         // from the list of tables, however we don't generate any JOIN
1356         if (empty($from_clause) && isset($all_tables)) {
1357             $from_clause = implode(', ', $all_tables);
1358         }
1359         return $from_clause;
1360     }
1361
1362     /**
1363      * Provides the generated SQL query
1364      *
1365      * @param array $cfgRelation Relation Settings
1366      *
1367      * @return string SQL query
1368      */
1369     private function _getSQLQuery($cfgRelation)
1370     {
1371         $sql_query = '';
1372         // get SELECT clause
1373         $sql_query .= $this->_getSelectClause();
1374         // get FROM clause
1375         $from_clause = $this->_getFromClause($cfgRelation);
1376         if (! empty($from_clause)) {
1377             $sql_query .= 'FROM ' . htmlspecialchars($from_clause) . "\n";
1378         }
1379         // get WHERE clause
1380         $sql_query .= $this->_getWhereClause();
1381         // get ORDER BY clause
1382         $sql_query .= $this->_getOrderByClause();
1383         return $sql_query;
1384     }
1385
1386     /**
1387      * Provides the generated QBE form
1388      *
1389      * @param array $cfgRelation Relation Settings
1390      *
1391      * @return string QBE form
1392      */
1393     public function getSelectionForm($cfgRelation)
1394     {
1395         $html_output = '<form action="db_qbe.php" method="post" id="formQBE">';
1396         $html_output .= '<fieldset>';
1397
1398         if ($GLOBALS['cfgRelation']['savedsearcheswork']) {
1399             $html_output .= $this->_getSavedSearchesField();
1400         }
1401
1402         $html_output .= '<table class="data" style="width: 100%;">';
1403         // Get table's <tr> elements
1404         $html_output .= $this->_getColumnNamesRow();
1405         $html_output .= $this->_getSortRow();
1406         $html_output .= $this->_getShowRow();
1407         $html_output .= $this->_getCriteriaInputboxRow();
1408         $html_output .= $this->_getInsDelAndOrCriteriaRows();
1409         $html_output .= $this->_getModifyColumnsRow();
1410         $html_output .= '</table>';
1411         $this->_new_row_count--;
1412         $url_params = array();
1413         $url_params['db'] = $this->_db;
1414         $url_params['criteriaColumnCount'] = $this->_new_column_count;
1415         $url_params['rows'] = $this->_new_row_count;
1416         $html_output .= PMA_URL_getHiddenInputs($url_params);
1417         $html_output .= '</fieldset>';
1418         // get footers
1419         $html_output .= $this->_getTableFooters();
1420         // get tables select list
1421         $html_output .= $this->_getTablesList();
1422         $html_output .= '</form>';
1423         $html_output .= '<form action="db_qbe.php" method="post">';
1424         $html_output .= PMA_URL_getHiddenInputs(array('db' => $this->_db));
1425         // get SQL query
1426         $html_output .= '<div class="floatleft">';
1427         $html_output .= '<fieldset>';
1428         $html_output .= '<legend>'
1429             . sprintf(
1430                 __('SQL query on database <b>%s</b>:'),
1431                 PMA_Util::getDbLink($this->_db)
1432             );
1433         $html_output .= '</legend>';
1434         $text_dir = 'ltr';
1435         $html_output .= '<textarea cols="80" name="sql_query" id="textSqlquery"'
1436             . ' rows="' . ((count($this->_criteriaTables) > 30) ? '15' : '7') . '"'
1437             . ' dir="' . $text_dir . '">';
1438         $html_output .= $this->_getSQLQuery($cfgRelation);
1439         $html_output .= '</textarea>';
1440         $html_output .= '</fieldset>';
1441         // displays form's footers
1442         $html_output .= '<fieldset class="tblFooters">';
1443         $html_output .= '<input type="hidden" name="submit_sql" value="1" />';
1444         $html_output .= '<input type="submit" value="' . __('Submit Query') . '" />';
1445         $html_output .= '</fieldset>';
1446         $html_output .= '</div>';
1447         $html_output .= '</form>';
1448         return $html_output;
1449     }
1450
1451     /**
1452      * Get fields to display
1453      *
1454      * @return string
1455      */
1456     private function _getSavedSearchesField()
1457     {
1458         $html_output = __('Saved bookmarked search:');
1459         $html_output .= ' <select name="searchId" id="searchId">';
1460         $html_output .= '<option value="">' . __('New bookmark') . '</option>';
1461
1462         $currentSearch = $this->_getCurrentSearch();
1463         $currentSearchId = null;
1464         $currentSearchName = null;
1465         if (null != $currentSearch) {
1466             $currentSearchId = $currentSearch->getId();
1467             $currentSearchName = $currentSearch->getSearchName();
1468         }
1469
1470         foreach ($this->_savedSearchList as $id => $name) {
1471             $html_output .= '<option value="' . htmlspecialchars($id)
1472                 . '" ' . (
1473                 $id == $currentSearchId
1474                     ? 'selected="selected" '
1475                     : ''
1476                 )
1477                 . '>'
1478                 . htmlspecialchars($name)
1479                 . '</option>';
1480         }
1481         $html_output .= '</select>';
1482         $html_output .= '<input type="text" name="searchName" id="searchName" '
1483             . 'value="' . $currentSearchName . '" />';
1484         $html_output .= '<input type="hidden" name="action" id="action" value="" />';
1485         $html_output .= '<input type="submit" name="saveSearch" id="saveSearch" '
1486             . 'value="' . __('Create bookmark') . '" />';
1487         if (null !== $currentSearchId) {
1488             $html_output .= '<input type="submit" name="updateSearch" '
1489                 . 'id="updateSearch" value="' . __('Update bookmark') . '" />';
1490             $html_output .= '<input type="submit" name="deleteSearch" '
1491                 . 'id="deleteSearch" value="' . __('Delete bookmark') . '" />';
1492         }
1493
1494         return $html_output;
1495     }
1496
1497     /**
1498      * Initialize _criteria_column_count
1499      *
1500      * @return int Previous number of columns
1501      */
1502     private function _initializeCriteriasCount()
1503     {
1504         // sets column count
1505         $criteriaColumnCount = PMA_ifSetOr(
1506             $_REQUEST['criteriaColumnCount'],
1507             3,
1508             'numeric'
1509         );
1510         $criteriaColumnAdd = PMA_ifSetOr(
1511             $_REQUEST['criteriaColumnAdd'],
1512             0,
1513             'numeric'
1514         );
1515         $this->_criteria_column_count = max(
1516             $criteriaColumnCount + $criteriaColumnAdd,
1517             0
1518         );
1519
1520         // sets row count
1521         $rows = PMA_ifSetOr($_REQUEST['rows'], 0, 'numeric');
1522         $criteriaRowAdd = PMA_ifSetOr($_REQUEST['criteriaRowAdd'], 0, 'numeric');
1523         $this->_criteria_row_count = max($rows + $criteriaRowAdd, 0);
1524
1525         return $criteriaColumnCount;
1526     }
1527
1528     /**
1529      * Get best
1530      *
1531      * @param array $all_tables           All tables
1532      * @param array $where_clause_columns Columns with where clause
1533      * @param array $unique_columns       Unique columns
1534      * @param array $index_columns        Indexed columns
1535      *
1536      * @return array
1537      */
1538     private function _getLeftJoinColumnCandidatesBest(
1539         $all_tables, $where_clause_columns, $unique_columns, $index_columns
1540     ) {
1541         // now we want to find the best.
1542         if (isset($unique_columns) && count($unique_columns) > 0) {
1543             $candidate_columns = $unique_columns;
1544             $needsort = 1;
1545             return array($candidate_columns, $needsort);
1546         } elseif (isset($index_columns) && count($index_columns) > 0) {
1547             $candidate_columns = $index_columns;
1548             $needsort = 1;
1549             return array($candidate_columns, $needsort);
1550         } elseif (isset($where_clause_columns) && count($where_clause_columns) > 0) {
1551             $candidate_columns = $where_clause_columns;
1552             $needsort = 0;
1553             return array($candidate_columns, $needsort);
1554         } else {
1555             $candidate_columns = $all_tables;
1556             $needsort = 0;
1557             return array($candidate_columns, $needsort);
1558         }
1559     }
1560 }
1561 ?>