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      * Large 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                         /*overload*/mb_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
 458         for (
 459             $colInd = 0;
 460             $colInd < $this->_criteria_column_count;
 461             $colInd++
 462         ) {
 463             if (! empty($this->_criteriaColumnInsert)
 464                 && isset($this->_criteriaColumnInsert[$colInd])
 465                 && $this->_criteriaColumnInsert[$colInd] == 'on'
 466             ) {
 467                 $html_output .= $this->_getSortSelectCell($new_column_count);
 468                 $new_column_count++;
 469             } // end if
 470
 471             if (! empty($this->_criteriaColumnDelete)
 472                 && isset($this->_criteriaColumnDelete[$colInd])
 473                 && $this->_criteriaColumnDelete[$colInd] == 'on'
 474             ) {
 475                 continue;
 476             }
 477             // If they have chosen all fields using the * selector,
 478             // then sorting is not available, Fix for Bug #570698
 479             if (isset($_REQUEST['criteriaSort'][$colInd])
 480                 && isset($_REQUEST['criteriaColumn'][$colInd])
 481                 && /*overload*/mb_substr($_REQUEST['criteriaColumn'][$colInd], -2) == '.*'
 482             ) {
 483                 $_REQUEST['criteriaSort'][$colInd] = '';
 484             } //end if
 485             // Set asc_selected
 486             if (isset($_REQUEST['criteriaSort'][$colInd])
 487                 && $_REQUEST['criteriaSort'][$colInd] == 'ASC'
 488             ) {
 489                 $this->_curSort[$new_column_count]
 490                     = $_REQUEST['criteriaSort'][$colInd];
 491                 $asc_selected = ' selected="selected"';
 492             } else {
 493                 $asc_selected = '';
 494             } // end if
 495             // Set desc selected
 496             if (isset($_REQUEST['criteriaSort'][$colInd])
 497                 && $_REQUEST['criteriaSort'][$colInd] == 'DESC'
 498             ) {
 499                 $this->_curSort[$new_column_count]
 500                     = $_REQUEST['criteriaSort'][$colInd];
 501                 $desc_selected = ' selected="selected"';
 502             } else {
 503                 $desc_selected = '';
 504             } // end if
 505             $html_output .= $this->_getSortSelectCell(
 506                 $new_column_count, $asc_selected, $desc_selected
 507             );
 508             $new_column_count++;
 509         } // end for
 510         $html_output .= '</tr>';
 511         return $html_output;
 512     }
 513
 514     /**
 515      * Provides search form's row containing SHOW checkboxes
 516      *
 517      * @return string HTML for search table's row
 518      */
 519     private function _getShowRow()
 520     {
 521         $html_output = '<tr class="odd noclick">';
 522         $html_output .= '<th>' . __('Show:') . '</th>';
 523         $new_column_count = 0;
 524         for (
 525             $column_index = 0;
 526             $column_index < $this->_criteria_column_count;
 527             $column_index++
 528         ) {
 529             if (! empty($this->_criteriaColumnInsert)
 530                 && isset($this->_criteriaColumnInsert[$column_index])
 531                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 532             ) {
 533                 $html_output .= '<td class="center">';
 534                 $html_output .= '<input type="checkbox"'
 535                     . ' name="criteriaShow[' . $new_column_count . ']" />';
 536                 $html_output .= '</td>';
 537                 $new_column_count++;
 538             } // end if
 539             if (! empty($this->_criteriaColumnDelete)
 540                 && isset($this->_criteriaColumnDelete[$column_index])
 541                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 542             ) {
 543                 continue;
 544             }
 545             if (isset($_REQUEST['criteriaShow'][$column_index])) {
 546                 $checked_options = ' checked="checked"';
 547                 $this->_curShow[$new_column_count]
 548                     = $_REQUEST['criteriaShow'][$column_index];
 549             } else {
 550                 $checked_options = '';
 551             }
 552             $html_output .= '<td class="center">';
 553             $html_output .= '<input type="checkbox"'
 554                 . ' name="criteriaShow[' . $new_column_count . ']"'
 555                 . $checked_options . ' />';
 556             $html_output .= '</td>';
 557             $new_column_count++;
 558         } // end for
 559         $html_output .= '</tr>';
 560         return $html_output;
 561     }
 562
 563     /**
 564      * Provides search form's row containing criteria Inputboxes
 565      *
 566      * @return string HTML for search table's row
 567      */
 568     private function _getCriteriaInputboxRow()
 569     {
 570         $html_output = '<tr class="even noclick">';
 571         $html_output .= '<th>' . __('Criteria:') . '</th>';
 572         $new_column_count = 0;
 573         for (
 574             $column_index = 0;
 575             $column_index < $this->_criteria_column_count;
 576             $column_index++
 577         ) {
 578             if (! empty($this->_criteriaColumnInsert)
 579                 && isset($this->_criteriaColumnInsert[$column_index])
 580                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 581             ) {
 582                 $html_output .= '<td class="center">';
 583                 $html_output .= '<input type="text"'
 584                     . ' name="criteria[' . $new_column_count . ']"'
 585                     . ' value=""'
 586                     . ' class="textfield"'
 587                     . ' style="width: ' . $this->_realwidth . '"'
 588                     . ' size="20" />';
 589                 $html_output .= '</td>';
 590                 $new_column_count++;
 591             } // end if
 592             if (! empty($this->_criteriaColumnDelete)
 593                 && isset($this->_criteriaColumnDelete[$column_index])
 594                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 595             ) {
 596                 continue;
 597             }
 598             if (isset($this->_criteria[$column_index])) {
 599                 $tmp_criteria = $this->_criteria[$column_index];
 600             }
 601             if ((empty($this->_prev_criteria)
 602                 || ! isset($this->_prev_criteria[$column_index]))
 603                 || $this->_prev_criteria[$column_index] != htmlspecialchars($tmp_criteria)
 604             ) {
 605                 $this->_curCriteria[$new_column_count] = $tmp_criteria;
 606             } else {
 607                 $this->_curCriteria[$new_column_count]
 608                     = $this->_prev_criteria[$column_index];
 609             }
 610             $html_output .= '<td class="center">';
 611             $html_output .= '<input type="hidden"'
 612                 . ' name="prev_criteria[' . $new_column_count . ']"'
 613                 . ' value="'
 614                 . htmlspecialchars($this->_curCriteria[$new_column_count])
 615                 . '" />';
 616             $html_output .= '<input type="text"'
 617                 . ' name="criteria[' . $new_column_count . ']"'
 618                 . ' value="' . htmlspecialchars($tmp_criteria) . '"'
 619                 . ' class="textfield"'
 620                 . ' style="width: ' . $this->_realwidth . '"'
 621                 . ' size="20" />';
 622             $html_output .= '</td>';
 623             $new_column_count++;
 624         } // end for
 625         $html_output .= '</tr>';
 626         return $html_output;
 627     }
 628
 629     /**
 630      * Provides footer options for adding/deleting row/columns
 631      *
 632      * @param string $type Whether row or column
 633      *
 634      * @return string HTML for footer options
 635      */
 636     private function _getFootersOptions($type)
 637     {
 638         $html_output = '<div class="floatleft">';
 639         $html_output .= (($type == 'row')
 640             ? __('Add/Delete criteria rows') : __('Add/Delete columns'));
 641         $html_output .= ':<select size="1" name="'
 642             . (($type == 'row') ? 'criteriaRowAdd' : 'criteriaColumnAdd') . '">';
 643         $html_output .= '<option value="-3">-3</option>';
 644         $html_output .= '<option value="-2">-2</option>';
 645         $html_output .= '<option value="-1">-1</option>';
 646         $html_output .= '<option value="0" selected="selected">0</option>';
 647         $html_output .= '<option value="1">1</option>';
 648         $html_output .= '<option value="2">2</option>';
 649         $html_output .= '<option value="3">3</option>';
 650         $html_output .= '</select>';
 651         $html_output .= '</div>';
 652         return $html_output;
 653     }
 654
 655     /**
 656      * Provides search form table's footer options
 657      *
 658      * @return string HTML for table footer
 659      */
 660     private function _getTableFooters()
 661     {
 662         $html_output = '<fieldset class="tblFooters">';
 663         $html_output .= $this->_getFootersOptions("row");
 664         $html_output .= $this->_getFootersOptions("column");
 665         $html_output .= '<div class="floatleft">';
 666         $html_output .= '<input type="submit" name="modify"'
 667             . 'value="' . __('Update Query') . '" />';
 668         $html_output .= '</div>';
 669         $html_output .= '</fieldset>';
 670         return $html_output;
 671     }
 672
 673     /**
 674      * Provides a select list of database tables
 675      *
 676      * @return string HTML for table select list
 677      */
 678     private function _getTablesList()
 679     {
 680         $html_output = '<div class="floatleft">';
 681         $html_output .= '<fieldset>';
 682         $html_output .= '<legend>' . __('Use Tables') . '</legend>';
 683         // Build the options list for each table name
 684         $options = '';
 685         $numTableListOptions = 0;
 686         foreach ($this->_criteriaTables as $key => $val) {
 687             $options .= '<option value="' . htmlspecialchars($key) . '"' . $val . '>'
 688                 . (str_replace(' ', '&nbsp;', htmlspecialchars($key))) . '</option>';
 689             $numTableListOptions++;
 690         }
 691         $html_output .= '<select name="TableList[]"'
 692             . ' multiple="multiple" id="listTable"'
 693             . ' size="' . (($numTableListOptions > 30) ? '15' : '7') . '">';
 694         $html_output .= $options;
 695         $html_output .= '</select>';
 696         $html_output .= '</fieldset>';
 697         $html_output .= '<fieldset class="tblFooters">';
 698         $html_output .= '<input type="submit" name="modify" value="'
 699             . __('Update Query') . '" />';
 700         $html_output .= '</fieldset>';
 701         $html_output .= '</div>';
 702         return $html_output;
 703     }
 704
 705     /**
 706      * Provides And/Or modification cell along with Insert/Delete options
 707      * (For modifying search form's table columns)
 708      *
 709      * @param integer $column_number Column Number (0,1,2) or more
 710      * @param array   $selected      Selected criteria column name
 711      *
 712      * @return string HTML for modification cell
 713      */
 714     private function _getAndOrColCell($column_number, $selected = null)
 715     {
 716         $html_output = '<td class="center">';
 717         $html_output .= '<strong>' . __('Or:') . '</strong>';
 718         $html_output .= '<input type="radio"'
 719             . ' name="criteriaAndOrColumn[' . $column_number . ']"'
 720             . ' value="or"' . $selected['or'] . ' />';
 721         $html_output .= '&nbsp;&nbsp;<strong>' . __('And:') . '</strong>';
 722         $html_output .= '<input type="radio"'
 723             . ' name="criteriaAndOrColumn[' . $column_number . ']"'
 724             . ' value="and"' . $selected['and'] . ' />';
 725         $html_output .= '<br />' . __('Ins');
 726         $html_output .= '<input type="checkbox"'
 727             . ' name="criteriaColumnInsert[' . $column_number . ']" />';
 728         $html_output .= '&nbsp;&nbsp;' . __('Del');
 729         $html_output .= '<input type="checkbox"'
 730             . ' name="criteriaColumnDelete[' . $column_number . ']" />';
 731         $html_output .= '</td>';
 732         return $html_output;
 733     }
 734
 735     /**
 736      * Provides search form's row containing column modifications options
 737      * (For modifying search form's table columns)
 738      *
 739      * @return string HTML for search table's row
 740      */
 741     private function _getModifyColumnsRow()
 742     {
 743         $html_output = '<tr class="even noclick">';
 744         $html_output .= '<th>' . __('Modify:') . '</th>';
 745         $new_column_count = 0;
 746         for (
 747         $column_index = 0;
 748         $column_index < $this->_criteria_column_count;
 749         $column_index++
 750         ) {
 751             if (! empty($this->_criteriaColumnInsert)
 752                 && isset($this->_criteriaColumnInsert[$column_index])
 753                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 754             ) {
 755                 $html_output .= $this->_getAndOrColCell($new_column_count);
 756                 $new_column_count++;
 757             } // end if
 758
 759             if (! empty($this->_criteriaColumnDelete)
 760                 && isset($this->_criteriaColumnDelete[$column_index])
 761                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 762             ) {
 763                 continue;
 764             }
 765
 766             if (isset($this->_criteriaAndOrColumn[$column_index])) {
 767                 $this->_curAndOrCol[$new_column_count]
 768                     = $this->_criteriaAndOrColumn[$column_index];
 769             }
 770             $checked_options = array();
 771             if (isset($this->_criteriaAndOrColumn[$column_index])
 772                 && $this->_criteriaAndOrColumn[$column_index] == 'or'
 773             ) {
 774                 $checked_options['or']  = ' checked="checked"';
 775                 $checked_options['and'] = '';
 776             } else {
 777                 $checked_options['and'] = ' checked="checked"';
 778                 $checked_options['or']  = '';
 779             }
 780             $html_output .= $this->_getAndOrColCell(
 781                 $new_column_count,
 782                 $checked_options
 783             );
 784             $new_column_count++;
 785         } // end for
 786         $html_output .= '</tr>';
 787         return $html_output;
 788     }
 789
 790     /**
 791      * Provides Insert/Delete options for criteria inputbox
 792      * with AND/OR relationship modification options
 793      *
 794      * @param integer $row_index       Number of criteria row
 795      * @param array   $checked_options If checked
 796      *
 797      * @return string HTML
 798      */
 799     private function _getInsDelAndOrCell($row_index, $checked_options)
 800     {
 801         $html_output = '<td class="' . $GLOBALS['cell_align_right'] . ' nowrap">';
 802         $html_output .= '<!-- Row controls -->';
 803         $html_output .= '<table class="nospacing nopadding">';
 804         $html_output .= '<tr>';
 805         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . ' nowrap">';
 806         $html_output .= '<small>' . __('Ins:') . '</small>';
 807         $html_output .= '<input type="checkbox"'
 808             . ' name="criteriaRowInsert[' . $row_index . ']" />';
 809         $html_output .= '</td>';
 810         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . '">';
 811         $html_output .= '<strong>' . __('And:') . '</strong>';
 812         $html_output .= '</td>';
 813         $html_output .= '<td>';
 814         $html_output .= '<input type="radio"'
 815             . ' name="criteriaAndOrRow[' . $row_index . ']" value="and"'
 816             . $checked_options['and'] . ' />';
 817         $html_output .= '</td>';
 818         $html_output .= '</tr>';
 819         $html_output .= '<tr>';
 820         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . ' nowrap">';
 821         $html_output .= '<small>' . __('Del:') . '</small>';
 822         $html_output .= '<input type="checkbox"'
 823             . ' name="criteriaRowDelete[' . $row_index . ']" />';
 824         $html_output .= '</td>';
 825         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . '">';
 826         $html_output .= '<strong>' . __('Or:') . '</strong>';
 827         $html_output .= '</td>';
 828         $html_output .= '<td>';
 829         $html_output .= '<input type="radio"'
 830             . ' name="criteriaAndOrRow[' . $row_index . ']"'
 831             . ' value="or"' . $checked_options['or'] . ' />';
 832         $html_output .= '</td>';
 833         $html_output .= '</tr>';
 834         $html_output .= '</table>';
 835         $html_output .= '</td>';
 836         return $html_output;
 837     }
 838
 839     /**
 840      * Provides rows for criteria inputbox Insert/Delete options
 841      * with AND/OR relationship modification options
 842      *
 843      * @param integer $new_row_index New row index if rows are added/deleted
 844      *
 845      * @return string HTML table rows
 846      */
 847     private function _getInputboxRow($new_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
 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
 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                     . /*overload*/mb_strtoupper($this->_curAndOrCol[$last_where])
1013                     . ' ';
1014             }
1015             if (! empty($this->_curField[$column_index])
1016                 && ! empty($this->_curCriteria[$column_index])
1017             ) {
1018                 $where_clause .= '(' . $this->_curField[$column_index] . ' '
1019                     . $this->_curCriteria[$column_index] . ')';
1020                 $last_where = $column_index;
1021                 $criteria_cnt++;
1022             }
1023         } // end for
1024         if ($criteria_cnt > 1) {
1025             $where_clause = '(' . $where_clause . ')';
1026         }
1027         // OR rows ${'cur' . $or}[$column_index]
1028         if (! isset($this->_curAndOrRow)) {
1029             $this->_curAndOrRow = array();
1030         }
1031         for (
1032         $row_index = 0;
1033         $row_index <= $this->_criteria_row_count;
1034         $row_index++
1035         ) {
1036             $criteria_cnt = 0;
1037             $qry_orwhere = '';
1038             $last_orwhere = '';
1039             for (
1040             $column_index = 0;
1041             $column_index < $this->_criteria_column_count;
1042             $column_index++
1043             ) {
1044                 if (! empty($this->_curField[$column_index])
1045                     && ! empty($_REQUEST['Or' . $row_index][$column_index])
1046                     && $column_index
1047                 ) {
1048                     $qry_orwhere .= ' '
1049                         . /*overload*/mb_strtoupper(
1050                             $this->_curAndOrCol[$last_orwhere]
1051                         )
1052                         . ' ';
1053                 }
1054                 if (! empty($this->_curField[$column_index])
1055                     && ! empty($_REQUEST['Or' . $row_index][$column_index])
1056                 ) {
1057                     $qry_orwhere .= '(' . $this->_curField[$column_index]
1058                         .  ' '
1059                         .  $_REQUEST['Or' . $row_index][$column_index]
1060                         .  ')';
1061                     $last_orwhere = $column_index;
1062                     $criteria_cnt++;
1063                 }
1064             } // end for
1065             if ($criteria_cnt > 1) {
1066                 $qry_orwhere      = '(' . $qry_orwhere . ')';
1067             }
1068             if (! empty($qry_orwhere)) {
1069                 $where_clause .= "\n"
1070                     .  /*overload*/mb_strtoupper(
1071                         isset($this->_curAndOrRow[$row_index])
1072                         ? $this->_curAndOrRow[$row_index] . ' '
1073                         : ''
1074                     )
1075                     .  $qry_orwhere;
1076             } // end if
1077         } // end for
1078
1079         if (! empty($where_clause) && $where_clause != '()') {
1080             $where_clause = 'WHERE ' . htmlspecialchars($where_clause) . "\n";
1081         } // end if
1082         return $where_clause;
1083     }
1084
1085     /**
1086      * Provides ORDER BY clause for building SQL query
1087      *
1088      * @return string Order By clause
1089      */
1090     private function _getOrderByClause()
1091     {
1092         $orderby_clause = '';
1093         $orderby_clauses = array();
1094
1095         for (
1096             $column_index = 0;
1097             $column_index < $this->_criteria_column_count;
1098             $column_index++
1099         ) {
1100             // if all columns are chosen with * selector,
1101             // then sorting isn't available
1102             // Fix for Bug #570698
1103             if (empty($this->_curField[$column_index])
1104                 && empty($this->_curSort[$column_index])
1105             ) {
1106                 continue;
1107             }
1108
1109             if (/*overload*/mb_substr($this->_curField[$column_index], -2) == '.*') {
1110                 continue;
1111             }
1112
1113             if (! empty($this->_curSort[$column_index])) {
1114                 $orderby_clauses[] = $this->_curField[$column_index] . ' '
1115                     . $this->_curSort[$column_index];
1116             }
1117         } // end for
1118         if ($orderby_clauses) {
1119             $orderby_clause = 'ORDER BY '
1120                 . htmlspecialchars(implode(", ", $orderby_clauses)) . "\n";
1121         }
1122         return $orderby_clause;
1123     }
1124
1125     /**
1126      * Provides UNIQUE columns and INDEX columns present in criteria tables
1127      *
1128      * @param array $all_tables           Tables involved in the search
1129      * @param array $all_columns          Columns involved in the search
1130      * @param array $where_clause_columns Columns having criteria where clause
1131      *
1132      * @return array having UNIQUE and INDEX columns
1133      */
1134     private function _getIndexes($all_tables, $all_columns,
1135         $where_clause_columns
1136     ) {
1137         $unique_columns = array();
1138         $index_columns = array();
1139
1140         foreach ($all_tables as $table) {
1141             $indexes = $GLOBALS['dbi']->getTableIndexes($this->_db, $table);
1142             foreach ($indexes as $index) {
1143                 $column = $table . '.' . $index['Column_name'];
1144                 if (isset($all_columns[$column])) {
1145                     if ($index['Non_unique'] == 0) {
1146                         if (isset($where_clause_columns[$column])) {
1147                             $unique_columns[$column] = 'Y';
1148                         } else {
1149                             $unique_columns[$column] = 'N';
1150                         }
1151                     } else {
1152                         if (isset($where_clause_columns[$column])) {
1153                             $index_columns[$column] = 'Y';
1154                         } else {
1155                             $index_columns[$column] = 'N';
1156                         }
1157                     }
1158                 }
1159             } // end while (each index of a table)
1160         } // end while (each table)
1161
1162         return array(
1163             'unique' => $unique_columns,
1164             'index' => $index_columns
1165         );
1166     }
1167
1168     /**
1169      * Provides UNIQUE columns and INDEX columns present in criteria tables
1170      *
1171      * @param array $all_tables           Tables involved in the search
1172      * @param array $all_columns          Columns involved in the search
1173      * @param array $where_clause_columns Columns having criteria where clause
1174      *
1175      * @return array having UNIQUE and INDEX columns
1176      */
1177     private function _getLeftJoinColumnCandidates($all_tables, $all_columns,
1178         $where_clause_columns
1179     ) {
1180         $GLOBALS['dbi']->selectDb($this->_db);
1181
1182         // Get unique columns and index columns
1183         $indexes = $this->_getIndexes(
1184             $all_tables, $all_columns, $where_clause_columns
1185         );
1186         $unique_columns = $indexes['unique'];
1187         $index_columns = $indexes['index'];
1188
1189         list($candidate_columns, $needsort)
1190             = $this->_getLeftJoinColumnCandidatesBest(
1191                 $all_tables, $where_clause_columns, $unique_columns, $index_columns
1192             );
1193
1194         // If we came up with $unique_columns (very good) or $index_columns (still
1195         // good) as $candidate_columns we want to check if we have any 'Y' there
1196         // (that would mean that they were also found in the whereclauses
1197         // which would be great). if yes, we take only those
1198         if ($needsort != 1) {
1199             return $candidate_columns;
1200         }
1201
1202         $vg = array();
1203         $sg = array();
1204         foreach ($candidate_columns as $column => $is_where) {
1205             $table = explode('.', $column);
1206             $table = $table[0];
1207             if ($is_where == 'Y') {
1208                 $vg[$column] = $table;
1209             } else {
1210                 $sg[$column] = $table;
1211             }
1212         }
1213         if (count($vg) > 0) {
1214             $candidate_columns = $vg;
1215             // Candidates restricted in index+where
1216         } else {
1217             $candidate_columns = $sg;
1218             // None of the candidates where in a where-clause
1219         }
1220
1221         return $candidate_columns;
1222     }
1223
1224     /**
1225      * Provides the main table to form the LEFT JOIN clause
1226      *
1227      * @param array $all_tables           Tables involved in the search
1228      * @param array $all_columns          Columns involved in the search
1229      * @param array $where_clause_columns Columns having criteria where clause
1230      * @param array $where_clause_tables  Tables having criteria where clause
1231      *
1232      * @return string table name
1233      */
1234     private function _getMasterTable($all_tables, $all_columns,
1235         $where_clause_columns, $where_clause_tables
1236     ) {
1237         if (count($where_clause_tables) == 1) {
1238             // If there is exactly one column that has a decent where-clause
1239             // we will just use this
1240             $master = key($where_clause_tables);
1241             return $master;
1242         }
1243
1244         // Now let's find out which of the tables has an index
1245         // (When the control user is the same as the normal user
1246         // because he is using one of his databases as pmadb,
1247         // the last db selected is not always the one where we need to work)
1248         $candidate_columns = $this->_getLeftJoinColumnCandidates(
1249             $all_tables, $all_columns, $where_clause_columns
1250         );
1251
1252         // Generally, we need to display all the rows of foreign (referenced)
1253         // table, whether they have any matching row in child table or not.
1254         // So we select candidate tables which are foreign tables.
1255         $foreign_tables = array();
1256         foreach ($candidate_columns as $one_table) {
1257             $foreigners = PMA_getForeigners($this->_db, $one_table);
1258             foreach ($foreigners as $key => $foreigner) {
1259                 if ($key != 'foreign_keys_data') {
1260                     if (in_array($foreigner['foreign_table'], $candidate_columns)) {
1261                         $foreign_tables[$foreigner['foreign_table']]
1262                             = $foreigner['foreign_table'];
1263                     }
1264                     continue;
1265                 }
1266                 foreach ($foreigner as $one_key) {
1267                     if (in_array($one_key['ref_table_name'], $candidate_columns)) {
1268                         $foreign_tables[$one_key['ref_table_name']]
1269                             = $one_key['ref_table_name'];
1270                     }
1271                 }
1272             }
1273         }
1274         if (count($foreign_tables)) {
1275             $candidate_columns = $foreign_tables;
1276         }
1277
1278         // If our array of candidates has more than one member we'll just
1279         // find the smallest table.
1280         // Of course the actual query would be faster if we check for
1281         // the Criteria which gives the smallest result set in its table,
1282         // but it would take too much time to check this
1283         if (!(count($candidate_columns) > 1)) {
1284             reset($candidate_columns);
1285             $master = current($candidate_columns); // Only one single candidate
1286             return $master;
1287         }
1288
1289         // Of course we only want to check each table once
1290         $checked_tables = $candidate_columns;
1291         $tsize = array();
1292         $csize = array();
1293         foreach ($candidate_columns as $table) {
1294             if ($checked_tables[$table] != 1) {
1295                 $tsize[$table] = PMA_Table::countRecords(
1296                     $this->_db,
1297                     $table,
1298                     false
1299                 );
1300                 $checked_tables[$table] = 1;
1301             }
1302             $csize[$table] = $tsize[$table];
1303         }
1304         arsort($csize);
1305         reset($csize);
1306         $master = key($csize); // Largest
1307
1308         return $master;
1309     }
1310
1311     /**
1312      * Provides columns and tables that have valid where clause criteria
1313      *
1314      * @return array
1315      */
1316     private function _getWhereClauseTablesAndColumns()
1317     {
1318         $where_clause_columns = array();
1319         $where_clause_tables = array();
1320
1321         // Now we need all tables that we have in the where clause
1322         for (
1323             $column_index = 0, $nb = count($this->_criteria);
1324             $column_index < $nb;
1325             $column_index++
1326         ) {
1327             $current_table = explode('.', $_POST['criteriaColumn'][$column_index]);
1328             if (empty($current_table[0]) || empty($current_table[1])) {
1329                 continue;
1330             } // end if
1331             $table = str_replace('`', '', $current_table[0]);
1332             $column = str_replace('`', '', $current_table[1]);
1333             $column = $table . '.' . $column;
1334             // Now we know that our array has the same numbers as $criteria
1335             // we can check which of our columns has a where clause
1336             if (! empty($this->_criteria[$column_index])) {
1337                 if (/*overload*/mb_substr($this->_criteria[$column_index], 0, 1) == '='
1338                     || /*$pmaString->*/stristr($this->_criteria[$column_index], 'is')
1339                 ) {
1340                     $where_clause_columns[$column] = $column;
1341                     $where_clause_tables[$table]  = $table;
1342                 }
1343             } // end if
1344         } // end for
1345         return array(
1346             'where_clause_tables' => $where_clause_tables,
1347             'where_clause_columns' => $where_clause_columns
1348         );
1349     }
1350
1351     /**
1352      * Provides FROM clause for building SQL query
1353      *
1354      * @return string FROM clause
1355      */
1356     private function _getFromClause()
1357     {
1358         $from_clause = '';
1359         if (isset($_POST['criteriaColumn']) && count($_POST['criteriaColumn']) > 0) {
1360             // Initialize some variables
1361             $all_tables = $all_columns = array();
1362
1363             // We only start this if we have fields, otherwise it would be dumb
1364             foreach ($_POST['criteriaColumn'] as $value) {
1365                 $parts = explode('.', $value);
1366                 if (! empty($parts[0]) && ! empty($parts[1])) {
1367                     $table = str_replace('`', '', $parts[0]);
1368                     $all_tables[$table] = $table;
1369                     $all_columns[] = $table . '.' . str_replace('`', '', $parts[1]);
1370                 }
1371             } // end while
1372
1373             // Create LEFT JOINS out of Relations
1374             if (count($all_tables) > 0) {
1375                 // Get tables and columns with valid where clauses
1376                 $valid_where_clauses = $this->_getWhereClauseTablesAndColumns();
1377                 $where_clause_tables = $valid_where_clauses['where_clause_tables'];
1378                 $where_clause_columns = $valid_where_clauses['where_clause_columns'];
1379                 // Get master table
1380                 $master = $this->_getMasterTable(
1381                     $all_tables, $all_columns,
1382                     $where_clause_columns, $where_clause_tables
1383                 );
1384                 $from_clause = PMA_Util::backquote($master)
1385                     . PMA_getRelatives($all_tables, $master);
1386
1387             } // end if (count($all_tables) > 0)
1388         } // end count($_POST['criteriaColumn']) > 0
1389
1390         // In case relations are not defined, just generate the FROM clause
1391         // from the list of tables, however we don't generate any JOIN
1392         if (empty($from_clause) && isset($all_tables)) {
1393             $from_clause = implode(', ', $all_tables);
1394         }
1395         return $from_clause;
1396     }
1397
1398     /**
1399      * Provides the generated SQL query
1400      *
1401      * @return string SQL query
1402      */
1403     private function _getSQLQuery()
1404     {
1405         $sql_query = '';
1406         // get SELECT clause
1407         $sql_query .= $this->_getSelectClause();
1408         // get FROM clause
1409         $from_clause = $this->_getFromClause();
1410         if (! empty($from_clause)) {
1411             $sql_query .= 'FROM ' . htmlspecialchars($from_clause) . "\n";
1412         }
1413         // get WHERE clause
1414         $sql_query .= $this->_getWhereClause();
1415         // get ORDER BY clause
1416         $sql_query .= $this->_getOrderByClause();
1417         return $sql_query;
1418     }
1419
1420     /**
1421      * Provides the generated QBE form
1422      *
1423      * @return string QBE form
1424      */
1425     public function getSelectionForm()
1426     {
1427         $html_output = '<form action="db_qbe.php" method="post" id="formQBE">';
1428         $html_output .= '<fieldset>';
1429
1430         if ($GLOBALS['cfgRelation']['savedsearcheswork']) {
1431             $html_output .= $this->_getSavedSearchesField();
1432         }
1433
1434         $html_output .= '<table class="data" style="width: 100%;">';
1435         // Get table's <tr> elements
1436         $html_output .= $this->_getColumnNamesRow();
1437         $html_output .= $this->_getSortRow();
1438         $html_output .= $this->_getShowRow();
1439         $html_output .= $this->_getCriteriaInputboxRow();
1440         $html_output .= $this->_getInsDelAndOrCriteriaRows();
1441         $html_output .= $this->_getModifyColumnsRow();
1442         $html_output .= '</table>';
1443         $this->_new_row_count--;
1444         $url_params = array();
1445         $url_params['db'] = $this->_db;
1446         $url_params['criteriaColumnCount'] = $this->_new_column_count;
1447         $url_params['rows'] = $this->_new_row_count;
1448         $html_output .= PMA_URL_getHiddenInputs($url_params);
1449         $html_output .= '</fieldset>';
1450         // get footers
1451         $html_output .= $this->_getTableFooters();
1452         // get tables select list
1453         $html_output .= $this->_getTablesList();
1454         $html_output .= '</form>';
1455         $html_output .= '<form action="db_qbe.php" method="post">';
1456         $html_output .= PMA_URL_getHiddenInputs(array('db' => $this->_db));
1457         // get SQL query
1458         $html_output .= '<div class="floatleft">';
1459         $html_output .= '<fieldset>';
1460         $html_output .= '<legend>'
1461             . sprintf(
1462                 __('SQL query on database <b>%s</b>:'),
1463                 PMA_Util::getDbLink($this->_db)
1464             );
1465         $html_output .= '</legend>';
1466         $text_dir = 'ltr';
1467         $html_output .= '<textarea cols="80" name="sql_query" id="textSqlquery"'
1468             . ' rows="' . ((count($this->_criteriaTables) > 30) ? '15' : '7') . '"'
1469             . ' dir="' . $text_dir . '">';
1470         $html_output .= $this->_getSQLQuery();
1471         $html_output .= '</textarea>';
1472         $html_output .= '</fieldset>';
1473         // displays form's footers
1474         $html_output .= '<fieldset class="tblFooters">';
1475         $html_output .= '<input type="hidden" name="submit_sql" value="1" />';
1476         $html_output .= '<input type="submit" value="' . __('Submit Query') . '" />';
1477         $html_output .= '</fieldset>';
1478         $html_output .= '</div>';
1479         $html_output .= '</form>';
1480         return $html_output;
1481     }
1482
1483     /**
1484      * Get fields to display
1485      *
1486      * @return string
1487      */
1488     private function _getSavedSearchesField()
1489     {
1490         $html_output = __('Saved bookmarked search:');
1491         $html_output .= ' <select name="searchId" id="searchId">';
1492         $html_output .= '<option value="">' . __('New bookmark') . '</option>';
1493
1494         $currentSearch = $this->_getCurrentSearch();
1495         $currentSearchId = null;
1496         $currentSearchName = null;
1497         if (null != $currentSearch) {
1498             $currentSearchId = $currentSearch->getId();
1499             $currentSearchName = $currentSearch->getSearchName();
1500         }
1501
1502         foreach ($this->_savedSearchList as $id => $name) {
1503             $html_output .= '<option value="' . htmlspecialchars($id)
1504                 . '" ' . (
1505                 $id == $currentSearchId
1506                     ? 'selected="selected" '
1507                     : ''
1508                 )
1509                 . '>'
1510                 . htmlspecialchars($name)
1511                 . '</option>';
1512         }
1513         $html_output .= '</select>';
1514         $html_output .= '<input type="text" name="searchName" id="searchName" '
1515             . 'value="' . $currentSearchName . '" />';
1516         $html_output .= '<input type="hidden" name="action" id="action" value="" />';
1517         $html_output .= '<input type="submit" name="saveSearch" id="saveSearch" '
1518             . 'value="' . __('Create bookmark') . '" />';
1519         if (null !== $currentSearchId) {
1520             $html_output .= '<input type="submit" name="updateSearch" '
1521                 . 'id="updateSearch" value="' . __('Update bookmark') . '" />';
1522             $html_output .= '<input type="submit" name="deleteSearch" '
1523                 . 'id="deleteSearch" value="' . __('Delete bookmark') . '" />';
1524         }
1525
1526         return $html_output;
1527     }
1528
1529     /**
1530      * Initialize _criteria_column_count
1531      *
1532      * @return int Previous number of columns
1533      */
1534     private function _initializeCriteriasCount()
1535     {
1536         // sets column count
1537         $criteriaColumnCount = PMA_ifSetOr(
1538             $_REQUEST['criteriaColumnCount'],
1539             3,
1540             'numeric'
1541         );
1542         $criteriaColumnAdd = PMA_ifSetOr(
1543             $_REQUEST['criteriaColumnAdd'],
1544             0,
1545             'numeric'
1546         );
1547         $this->_criteria_column_count = max(
1548             $criteriaColumnCount + $criteriaColumnAdd,
1549             0
1550         );
1551
1552         // sets row count
1553         $rows = PMA_ifSetOr($_REQUEST['rows'], 0, 'numeric');
1554         $criteriaRowAdd = PMA_ifSetOr($_REQUEST['criteriaRowAdd'], 0, 'numeric');
1555         $this->_criteria_row_count = max($rows + $criteriaRowAdd, 0);
1556
1557         return $criteriaColumnCount;
1558     }
1559
1560     /**
1561      * Get best
1562      *
1563      * @param array $all_tables           All tables
1564      * @param array $where_clause_columns Columns with where clause
1565      * @param array $unique_columns       Unique columns
1566      * @param array $index_columns        Indexed columns
1567      *
1568      * @return array
1569      */
1570     private function _getLeftJoinColumnCandidatesBest(
1571         $all_tables, $where_clause_columns, $unique_columns, $index_columns
1572     ) {
1573         // now we want to find the best.
1574         if (isset($unique_columns) && count($unique_columns) > 0) {
1575             $candidate_columns = $unique_columns;
1576             $needsort = 1;
1577             return array($candidate_columns, $needsort);
1578         } elseif (isset($index_columns) && count($index_columns) > 0) {
1579             $candidate_columns = $index_columns;
1580             $needsort = 1;
1581             return array($candidate_columns, $needsort);
1582         } elseif (isset($where_clause_columns) && count($where_clause_columns) > 0) {
1583             $candidate_columns = $where_clause_columns;
1584             $needsort = 0;
1585             return array($candidate_columns, $needsort);
1586         } else {
1587             $candidate_columns = $all_tables;
1588             $needsort = 0;
1589             return array($candidate_columns, $needsort);
1590         }
1591     }
1592 }
1593 ?>