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 alias
 133      *
 134      * @access private
 135      * @var array
 136      */
 137     private $_curAlias;
 138     /**
 139      * Current criteria Sort options
 140      *
 141      * @access private
 142      * @var array
 143      */
 144     private $_curSort;
 145     /**
 146      * Current criteria sort order
 147      *
 148      * @access private
 149      * @var array
 150      */
 151     private $_curSortOrder;
 152     /**
 153      * Current criteria Show options
 154      *
 155      * @access private
 156      * @var array
 157      */
 158     private $_curShow;
 159     /**
 160      * Current criteria values
 161      *
 162      * @access private
 163      * @var array
 164      */
 165     private $_curCriteria;
 166     /**
 167      * Current criteria AND/OR column relations
 168      *
 169      * @access private
 170      * @var array
 171      */
 172     private $_curAndOrCol;
 173     /**
 174      * Current criteria AND/OR row relations
 175      *
 176      * @access private
 177      * @var array
 178      */
 179     private $_curAndOrRow;
 180     /**
 181      * New column count in case of add/delete
 182      *
 183      * @access private
 184      * @var integer
 185      */
 186     private $_new_column_count;
 187     /**
 188      * New row count in case of add/delete
 189      *
 190      * @access private
 191      * @var integer
 192      */
 193     private $_new_row_count;
 194     /**
 195      * List of saved searches
 196      *
 197      * @access private
 198      * @var array
 199      */
 200     private $_savedSearchList = null;
 201     /**
 202      * Current search
 203      *
 204      * @access private
 205      * @var PMA_SavedSearches
 206      */
 207     private $_currentSearch = null;
 208
 209     /**
 210      * Initialize criterias
 211      *
 212      * @return static
 213      */
 214     private function _loadCriterias()
 215     {
 216         if (null === $this->_currentSearch
 217             || null === $this->_currentSearch->getCriterias()
 218         ) {
 219             return $this;
 220         }
 221
 222         $criterias = $this->_currentSearch->getCriterias();
 223         $_REQUEST = $criterias + $_REQUEST;
 224
 225         return $this;
 226     }
 227
 228     /**
 229      * Getter for current search
 230      *
 231      * @return PMA_SavedSearches
 232      */
 233     private function _getCurrentSearch()
 234     {
 235         return $this->_currentSearch;
 236     }
 237
 238     /**
 239      * Public Constructor
 240      *
 241      * @param string            $dbname          Database name
 242      * @param array             $savedSearchList List of saved searches
 243      * @param PMA_SavedSearches $currentSearch   Current search id
 244      */
 245     public function __construct(
 246         $dbname, $savedSearchList = array(), $currentSearch = null
 247     ) {
 248         $this->_db = $dbname;
 249         $this->_savedSearchList = $savedSearchList;
 250         $this->_currentSearch = $currentSearch;
 251         $this->_loadCriterias();
 252         // Sets criteria parameters
 253         $this->_setSearchParams();
 254         $this->_setCriteriaTablesAndColumns();
 255     }
 256
 257     /**
 258      * Sets search parameters
 259      *
 260      * @return void
 261      */
 262     private function _setSearchParams()
 263     {
 264         $criteriaColumnCount = $this->_initializeCriteriasCount();
 265
 266         $this->_criteriaColumnInsert = PMA_ifSetOr(
 267             $_REQUEST['criteriaColumnInsert'],
 268             null,
 269             'array'
 270         );
 271         $this->_criteriaColumnDelete = PMA_ifSetOr(
 272             $_REQUEST['criteriaColumnDelete'],
 273             null,
 274             'array'
 275         );
 276
 277         $this->_prev_criteria = isset($_REQUEST['prev_criteria'])
 278             ? $_REQUEST['prev_criteria']
 279             : array();
 280         $this->_criteria = isset($_REQUEST['criteria'])
 281             ? $_REQUEST['criteria']
 282             : array_fill(0, $criteriaColumnCount, '');
 283
 284         $this->_criteriaRowInsert = isset($_REQUEST['criteriaRowInsert'])
 285             ? $_REQUEST['criteriaRowInsert']
 286             : array_fill(0, $criteriaColumnCount, '');
 287         $this->_criteriaRowDelete = isset($_REQUEST['criteriaRowDelete'])
 288             ? $_REQUEST['criteriaRowDelete']
 289             : array_fill(0, $criteriaColumnCount, '');
 290         $this->_criteriaAndOrRow = isset($_REQUEST['criteriaAndOrRow'])
 291             ? $_REQUEST['criteriaAndOrRow']
 292             : array_fill(0, $criteriaColumnCount, '');
 293         $this->_criteriaAndOrColumn = isset($_REQUEST['criteriaAndOrColumn'])
 294             ? $_REQUEST['criteriaAndOrColumn']
 295             : array_fill(0, $criteriaColumnCount, '');
 296         // sets minimum width
 297         $this->_form_column_width = 12;
 298         $this->_curField = array();
 299         $this->_curSort = array();
 300         $this->_curShow = array();
 301         $this->_curCriteria = array();
 302         $this->_curAndOrRow = array();
 303         $this->_curAndOrCol = array();
 304     }
 305
 306     /**
 307      * Sets criteria tables and columns
 308      *
 309      * @return void
 310      */
 311     private function _setCriteriaTablesAndColumns()
 312     {
 313         // The tables list sent by a previously submitted form
 314         if (PMA_isValid($_REQUEST['TableList'], 'array')) {
 315             foreach ($_REQUEST['TableList'] as $each_table) {
 316                 $this->_criteriaTables[$each_table] = ' selected="selected"';
 317             }
 318         } // end if
 319         $all_tables = $GLOBALS['dbi']->query(
 320             'SHOW TABLES FROM ' . PMA_Util::backquote($this->_db) . ';',
 321             null,
 322             PMA_DatabaseInterface::QUERY_STORE
 323         );
 324         $all_tables_count = $GLOBALS['dbi']->numRows($all_tables);
 325         if (0 == $all_tables_count) {
 326             PMA_Message::error(__('No tables found in database.'))->display();
 327             exit;
 328         }
 329         // The tables list gets from MySQL
 330         while (list($table) = $GLOBALS['dbi']->fetchRow($all_tables)) {
 331             $columns = $GLOBALS['dbi']->getColumns($this->_db, $table);
 332
 333             if (empty($this->_criteriaTables[$table])
 334                 && ! empty($_REQUEST['TableList'])
 335             ) {
 336                 $this->_criteriaTables[$table] = '';
 337             } else {
 338                 $this->_criteriaTables[$table] = ' selected="selected"';
 339             } //  end if
 340
 341             // The fields list per selected tables
 342             if ($this->_criteriaTables[$table] == ' selected="selected"') {
 343                 $each_table = PMA_Util::backquote($table);
 344                 $this->_columnNames[]  = $each_table . '.*';
 345                 foreach ($columns as $each_column) {
 346                     $each_column = $each_table . '.'
 347                         . PMA_Util::backquote($each_column['Field']);
 348                     $this->_columnNames[] = $each_column;
 349                     // increase the width if necessary
 350                     $this->_form_column_width = max(
 351                         /*overload*/mb_strlen($each_column),
 352                         $this->_form_column_width
 353                     );
 354                 } // end foreach
 355             } // end if
 356         } // end while
 357         $GLOBALS['dbi']->freeResult($all_tables);
 358
 359         // sets the largest width found
 360         $this->_realwidth = $this->_form_column_width . 'ex';
 361     }
 362     /**
 363      * Provides select options list containing column names
 364      *
 365      * @param integer $column_number Column Number (0,1,2) or more
 366      * @param string  $selected      Selected criteria column name
 367      *
 368      * @return string HTML for select options
 369      */
 370     private function _showColumnSelectCell($column_number, $selected = '')
 371     {
 372         $html_output = '';
 373         $html_output .= '<td class="center">';
 374         $html_output .= '<select name="criteriaColumn[' . $column_number
 375             . ']" size="1">';
 376         $html_output .= '<option value="">&nbsp;</option>';
 377         foreach ($this->_columnNames as $column) {
 378             $html_output .= '<option value="' . htmlspecialchars($column) . '"'
 379                 . (($column === $selected) ? ' selected="selected"' : '') . '>'
 380                 . str_replace(' ', '&nbsp;', htmlspecialchars($column))
 381                 . '</option>';
 382         }
 383         $html_output .= '</select>';
 384         $html_output .= '</td>';
 385         return $html_output;
 386     }
 387
 388     /**
 389      * Provides select options list containing sort options (ASC/DESC)
 390      *
 391      * @param integer $column_number Column Number (0,1,2) or more
 392      * @param string  $asc_selected  Selected criteria 'Ascending'
 393      * @param string  $desc_selected Selected criteria 'Descending'
 394      *
 395      * @return string HTML for select options
 396      */
 397     private function _getSortSelectCell($column_number, $asc_selected = '',
 398         $desc_selected = ''
 399     ) {
 400         $html_output = '<td class="center">';
 401         $html_output .= '<select style="width: ' . $this->_realwidth
 402             . '" name="criteriaSort[' . $column_number . ']" size="1">';
 403         $html_output .= '<option value="">&nbsp;</option>';
 404         $html_output .= '<option value="ASC"' . $asc_selected . '>'
 405             . __('Ascending')
 406             . '</option>';
 407         $html_output .= '<option value="DESC"' . $desc_selected . '>'
 408             . __('Descending')
 409             . '</option>';
 410         $html_output .= '</select>';
 411         $html_output .= '</td>';
 412         return $html_output;
 413     }
 414
 415     /**
 416      * Provides select options list containing sort order
 417      *
 418      * @param integer $columnNumber Column Number (0,1,2) or more
 419      * @param integer $sortOrder    Sort order
 420      *
 421      * @return string HTML for select options
 422      */
 423     private function _getSortOrderSelectCell($columnNumber, $sortOrder)
 424     {
 425         $totalColumnCount = $this->_getNewColumnCount();
 426         $html_output  = '<td class="center">';
 427         $html_output .= '<select name="criteriaSortOrder[' . $columnNumber . ']">';
 428         $html_output .= '<option value="1000">'
 429             . '&nbsp;</option>';
 430         for ($a = 1; $a <= $totalColumnCount; $a++) {
 431             $html_output .= '<option value="' . $a . '"';
 432             if ($a == $sortOrder) {
 433                 $html_output .= ' selected="selected"';
 434             }
 435             $html_output .= '>' . $a . '</option>';
 436         }
 437         $html_output .= '</select>';
 438         $html_output .= '</td>';
 439         return $html_output;
 440     }
 441
 442     /**
 443      * Returns the new column count after adding and removing columns as instructed
 444      *
 445      * @return int new column count
 446      */
 447     private function _getNewColumnCount()
 448     {
 449         $totalColumnCount = $this->_criteria_column_count;
 450         if (! empty($this->_criteriaColumnInsert)) {
 451             $totalColumnCount += count($this->_criteriaColumnInsert);
 452         }
 453         if (! empty($this->_criteriaColumnDelete)) {
 454             $totalColumnCount -= count($this->_criteriaColumnDelete);
 455         }
 456         return $totalColumnCount;
 457     }
 458
 459     /**
 460      * Provides search form's row containing column select options
 461      *
 462      * @return string HTML for search table's row
 463      */
 464     private function _getColumnNamesRow()
 465     {
 466         $html_output = '<tr class="odd noclick">';
 467         $html_output .= '<th>' . __('Column:') . '</th>';
 468         $new_column_count = 0;
 469         for (
 470             $column_index = 0;
 471             $column_index < $this->_criteria_column_count;
 472             $column_index++
 473         ) {
 474             if (isset($this->_criteriaColumnInsert[$column_index])
 475                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 476             ) {
 477                 $html_output .= $this->_showColumnSelectCell(
 478                     $new_column_count
 479                 );
 480                 $new_column_count++;
 481             }
 482             if (! empty($this->_criteriaColumnDelete)
 483                 && isset($this->_criteriaColumnDelete[$column_index])
 484                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 485             ) {
 486                 continue;
 487             }
 488             $selected = '';
 489             if (isset($_REQUEST['criteriaColumn'][$column_index])) {
 490                 $selected = $_REQUEST['criteriaColumn'][$column_index];
 491                 $this->_curField[$new_column_count]
 492                     = $_REQUEST['criteriaColumn'][$column_index];
 493             }
 494             $html_output .= $this->_showColumnSelectCell(
 495                 $new_column_count,
 496                 $selected
 497             );
 498             $new_column_count++;
 499         } // end for
 500         $this->_new_column_count = $new_column_count;
 501         $html_output .= '</tr>';
 502         return $html_output;
 503     }
 504
 505     /**
 506      * Provides search form's row containing column aliases
 507      *
 508      * @return string HTML for search table's row
 509      */
 510     private function _getColumnAliasRow()
 511     {
 512         $html_output = '<tr class="even noclick">';
 513         $html_output .= '<th>' . __('Alias:') . '</th>';
 514         $new_column_count = 0;
 515
 516         for (
 517         $colInd = 0;
 518         $colInd < $this->_criteria_column_count;
 519         $colInd++
 520         ) {
 521             if (! empty($this->_criteriaColumnInsert)
 522                 && isset($this->_criteriaColumnInsert[$colInd])
 523                 && $this->_criteriaColumnInsert[$colInd] == 'on'
 524             ) {
 525                 $html_output .= '<td class="center">';
 526                 $html_output .= '<input type="text"'
 527                     . ' name="criteriaAlias[' . $new_column_count . ']"'
 528                     . ' value="" />';
 529                 $html_output .= '</td>';
 530                 $new_column_count++;
 531             } // end if
 532
 533             if (! empty($this->_criteriaColumnDelete)
 534                 && isset($this->_criteriaColumnDelete[$colInd])
 535                 && $this->_criteriaColumnDelete[$colInd] == 'on'
 536             ) {
 537                 continue;
 538             }
 539
 540             $tmp_alias = '';
 541             if (! empty($_REQUEST['criteriaAlias'][$colInd])) {
 542                 $tmp_alias
 543                     = $this->_curAlias[$new_column_count]
 544                         = $_REQUEST['criteriaAlias'][$colInd];
 545             }// end if
 546
 547             $html_output .= '<td class="center">';
 548             $html_output .= '<input type="text"'
 549                 . ' name="criteriaAlias[' . $new_column_count . ']"'
 550                 . ' value="' . htmlspecialchars($tmp_alias) . '" />';
 551             $html_output .= '</td>';
 552             $new_column_count++;
 553         } // end for
 554         $html_output .= '</tr>';
 555         return $html_output;
 556     }
 557
 558     /**
 559      * Provides search form's row containing sort(ASC/DESC) select options
 560      *
 561      * @return string HTML for search table's row
 562      */
 563     private function _getSortRow()
 564     {
 565         $html_output = '<tr class="even noclick">';
 566         $html_output .= '<th>' . __('Sort:') . '</th>';
 567         $new_column_count = 0;
 568
 569         for (
 570             $colInd = 0;
 571             $colInd < $this->_criteria_column_count;
 572             $colInd++
 573         ) {
 574             if (! empty($this->_criteriaColumnInsert)
 575                 && isset($this->_criteriaColumnInsert[$colInd])
 576                 && $this->_criteriaColumnInsert[$colInd] == 'on'
 577             ) {
 578                 $html_output .= $this->_getSortSelectCell($new_column_count);
 579                 $new_column_count++;
 580             } // end if
 581
 582             if (! empty($this->_criteriaColumnDelete)
 583                 && isset($this->_criteriaColumnDelete[$colInd])
 584                 && $this->_criteriaColumnDelete[$colInd] == 'on'
 585             ) {
 586                 continue;
 587             }
 588             // If they have chosen all fields using the * selector,
 589             // then sorting is not available, Fix for Bug #570698
 590             if (isset($_REQUEST['criteriaSort'][$colInd])
 591                 && isset($_REQUEST['criteriaColumn'][$colInd])
 592                 && /*overload*/mb_substr($_REQUEST['criteriaColumn'][$colInd], -2) == '.*'
 593             ) {
 594                 $_REQUEST['criteriaSort'][$colInd] = '';
 595             } //end if
 596
 597             $asc_selected = ''; $desc_selected = '';
 598             if (isset($_REQUEST['criteriaSort'][$colInd])) {
 599                 $this->_curSort[$new_column_count]
 600                     = $_REQUEST['criteriaSort'][$colInd];
 601                 // Set asc_selected
 602                 if ($_REQUEST['criteriaSort'][$colInd] == 'ASC') {
 603                     $asc_selected = ' selected="selected"';
 604                 } // end if
 605                 // Set desc selected
 606                 if ($_REQUEST['criteriaSort'][$colInd] == 'DESC') {
 607                     $desc_selected = ' selected="selected"';
 608                 } // end if
 609             } else {
 610                 $this->_curSort[$new_column_count] = '';
 611             }
 612
 613             $html_output .= $this->_getSortSelectCell(
 614                 $new_column_count, $asc_selected, $desc_selected
 615             );
 616             $new_column_count++;
 617         } // end for
 618         $html_output .= '</tr>';
 619         return $html_output;
 620     }
 621
 622     /**
 623      * Provides search form's row containing sort order
 624      *
 625      * @return string HTML for search table's row
 626      */
 627     private function _getSortOrder()
 628     {
 629         $html_output = '<tr class="even noclick">';
 630         $html_output .= '<th>' . __('Sort order:') . '</th>';
 631         $new_column_count = 0;
 632
 633         for (
 634         $colInd = 0;
 635         $colInd < $this->_criteria_column_count;
 636         $colInd++
 637         ) {
 638             if (! empty($this->_criteriaColumnInsert)
 639                 && isset($this->_criteriaColumnInsert[$colInd])
 640                 && $this->_criteriaColumnInsert[$colInd] == 'on'
 641             ) {
 642                 $html_output .= $this->_getSortOrderSelectCell(
 643                     $new_column_count, null
 644                 );
 645                 $new_column_count++;
 646             } // end if
 647
 648             if (! empty($this->_criteriaColumnDelete)
 649                 && isset($this->_criteriaColumnDelete[$colInd])
 650                 && $this->_criteriaColumnDelete[$colInd] == 'on'
 651             ) {
 652                 continue;
 653             }
 654
 655             $sortOrder = null;
 656             if (! empty($_REQUEST['criteriaSortOrder'][$colInd])) {
 657                 $sortOrder
 658                     = $this->_curSortOrder[$new_column_count]
 659                         = $_REQUEST['criteriaSortOrder'][$colInd];
 660             }
 661
 662             $html_output .= $this->_getSortOrderSelectCell(
 663                 $new_column_count, $sortOrder
 664             );
 665             $new_column_count++;
 666         } // end for
 667         $html_output .= '</tr>';
 668         return $html_output;
 669     }
 670
 671     /**
 672      * Provides search form's row containing SHOW checkboxes
 673      *
 674      * @return string HTML for search table's row
 675      */
 676     private function _getShowRow()
 677     {
 678         $html_output = '<tr class="odd noclick">';
 679         $html_output .= '<th>' . __('Show:') . '</th>';
 680         $new_column_count = 0;
 681         for (
 682             $column_index = 0;
 683             $column_index < $this->_criteria_column_count;
 684             $column_index++
 685         ) {
 686             if (! empty($this->_criteriaColumnInsert)
 687                 && isset($this->_criteriaColumnInsert[$column_index])
 688                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 689             ) {
 690                 $html_output .= '<td class="center">';
 691                 $html_output .= '<input type="checkbox"'
 692                     . ' name="criteriaShow[' . $new_column_count . ']" />';
 693                 $html_output .= '</td>';
 694                 $new_column_count++;
 695             } // end if
 696             if (! empty($this->_criteriaColumnDelete)
 697                 && isset($this->_criteriaColumnDelete[$column_index])
 698                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 699             ) {
 700                 continue;
 701             }
 702             if (isset($_REQUEST['criteriaShow'][$column_index])) {
 703                 $checked_options = ' checked="checked"';
 704                 $this->_curShow[$new_column_count]
 705                     = $_REQUEST['criteriaShow'][$column_index];
 706             } else {
 707                 $checked_options = '';
 708             }
 709             $html_output .= '<td class="center">';
 710             $html_output .= '<input type="checkbox"'
 711                 . ' name="criteriaShow[' . $new_column_count . ']"'
 712                 . $checked_options . ' />';
 713             $html_output .= '</td>';
 714             $new_column_count++;
 715         } // end for
 716         $html_output .= '</tr>';
 717         return $html_output;
 718     }
 719
 720     /**
 721      * Provides search form's row containing criteria Inputboxes
 722      *
 723      * @return string HTML for search table's row
 724      */
 725     private function _getCriteriaInputboxRow()
 726     {
 727         $html_output = '<tr class="even noclick">';
 728         $html_output .= '<th>' . __('Criteria:') . '</th>';
 729         $new_column_count = 0;
 730         for (
 731             $column_index = 0;
 732             $column_index < $this->_criteria_column_count;
 733             $column_index++
 734         ) {
 735             if (! empty($this->_criteriaColumnInsert)
 736                 && isset($this->_criteriaColumnInsert[$column_index])
 737                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 738             ) {
 739                 $html_output .= '<td class="center">';
 740                 $html_output .= '<input type="text"'
 741                     . ' name="criteria[' . $new_column_count . ']"'
 742                     . ' value=""'
 743                     . ' class="textfield"'
 744                     . ' style="width: ' . $this->_realwidth . '"'
 745                     . ' size="20" />';
 746                 $html_output .= '</td>';
 747                 $new_column_count++;
 748             } // end if
 749             if (! empty($this->_criteriaColumnDelete)
 750                 && isset($this->_criteriaColumnDelete[$column_index])
 751                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 752             ) {
 753                 continue;
 754             }
 755             if (isset($this->_criteria[$column_index])) {
 756                 $tmp_criteria = $this->_criteria[$column_index];
 757             }
 758             if ((empty($this->_prev_criteria)
 759                 || ! isset($this->_prev_criteria[$column_index]))
 760                 || $this->_prev_criteria[$column_index] != htmlspecialchars($tmp_criteria)
 761             ) {
 762                 $this->_curCriteria[$new_column_count] = $tmp_criteria;
 763             } else {
 764                 $this->_curCriteria[$new_column_count]
 765                     = $this->_prev_criteria[$column_index];
 766             }
 767             $html_output .= '<td class="center">';
 768             $html_output .= '<input type="hidden"'
 769                 . ' name="prev_criteria[' . $new_column_count . ']"'
 770                 . ' value="'
 771                 . htmlspecialchars($this->_curCriteria[$new_column_count])
 772                 . '" />';
 773             $html_output .= '<input type="text"'
 774                 . ' name="criteria[' . $new_column_count . ']"'
 775                 . ' value="' . htmlspecialchars($tmp_criteria) . '"'
 776                 . ' class="textfield"'
 777                 . ' style="width: ' . $this->_realwidth . '"'
 778                 . ' size="20" />';
 779             $html_output .= '</td>';
 780             $new_column_count++;
 781         } // end for
 782         $html_output .= '</tr>';
 783         return $html_output;
 784     }
 785
 786     /**
 787      * Provides footer options for adding/deleting row/columns
 788      *
 789      * @param string $type Whether row or column
 790      *
 791      * @return string HTML for footer options
 792      */
 793     private function _getFootersOptions($type)
 794     {
 795         $html_output = '<div class="floatleft">';
 796         $html_output .= (($type == 'row')
 797             ? __('Add/Delete criteria rows') : __('Add/Delete columns'));
 798         $html_output .= ':<select size="1" name="'
 799             . (($type == 'row') ? 'criteriaRowAdd' : 'criteriaColumnAdd') . '">';
 800         $html_output .= '<option value="-3">-3</option>';
 801         $html_output .= '<option value="-2">-2</option>';
 802         $html_output .= '<option value="-1">-1</option>';
 803         $html_output .= '<option value="0" selected="selected">0</option>';
 804         $html_output .= '<option value="1">1</option>';
 805         $html_output .= '<option value="2">2</option>';
 806         $html_output .= '<option value="3">3</option>';
 807         $html_output .= '</select>';
 808         $html_output .= '</div>';
 809         return $html_output;
 810     }
 811
 812     /**
 813      * Provides search form table's footer options
 814      *
 815      * @return string HTML for table footer
 816      */
 817     private function _getTableFooters()
 818     {
 819         $html_output = '<fieldset class="tblFooters">';
 820         $html_output .= $this->_getFootersOptions("row");
 821         $html_output .= $this->_getFootersOptions("column");
 822         $html_output .= '<div class="floatleft">';
 823         $html_output .= '<input type="submit" name="modify"'
 824             . 'value="' . __('Update Query') . '" />';
 825         $html_output .= '</div>';
 826         $html_output .= '</fieldset>';
 827         return $html_output;
 828     }
 829
 830     /**
 831      * Provides a select list of database tables
 832      *
 833      * @return string HTML for table select list
 834      */
 835     private function _getTablesList()
 836     {
 837         $html_output = '<div class="floatleft">';
 838         $html_output .= '<fieldset>';
 839         $html_output .= '<legend>' . __('Use Tables') . '</legend>';
 840         // Build the options list for each table name
 841         $options = '';
 842         $numTableListOptions = 0;
 843         foreach ($this->_criteriaTables as $key => $val) {
 844             $options .= '<option value="' . htmlspecialchars($key) . '"' . $val . '>'
 845                 . (str_replace(' ', '&nbsp;', htmlspecialchars($key))) . '</option>';
 846             $numTableListOptions++;
 847         }
 848         $html_output .= '<select name="TableList[]"'
 849             . ' multiple="multiple" id="listTable"'
 850             . ' size="' . (($numTableListOptions > 30) ? '15' : '7') . '">';
 851         $html_output .= $options;
 852         $html_output .= '</select>';
 853         $html_output .= '</fieldset>';
 854         $html_output .= '<fieldset class="tblFooters">';
 855         $html_output .= '<input type="submit" name="modify" value="'
 856             . __('Update Query') . '" />';
 857         $html_output .= '</fieldset>';
 858         $html_output .= '</div>';
 859         return $html_output;
 860     }
 861
 862     /**
 863      * Provides And/Or modification cell along with Insert/Delete options
 864      * (For modifying search form's table columns)
 865      *
 866      * @param integer $column_number Column Number (0,1,2) or more
 867      * @param array   $selected      Selected criteria column name
 868      * @param bool    $last_column   Whether this is the last column
 869      *
 870      * @return string HTML for modification cell
 871      */
 872     private function _getAndOrColCell(
 873         $column_number, $selected = null, $last_column = false
 874     ) {
 875         $html_output = '<td class="center">';
 876         if (! $last_column) {
 877             $html_output .= '<strong>' . __('Or:') . '</strong>';
 878             $html_output .= '<input type="radio"'
 879                 . ' name="criteriaAndOrColumn[' . $column_number . ']"'
 880                 . ' value="or"' . $selected['or'] . ' />';
 881             $html_output .= '&nbsp;&nbsp;<strong>' . __('And:') . '</strong>';
 882             $html_output .= '<input type="radio"'
 883                 . ' name="criteriaAndOrColumn[' . $column_number . ']"'
 884                 . ' value="and"' . $selected['and'] . ' />';
 885         }
 886         $html_output .= '<br />' . __('Ins');
 887         $html_output .= '<input type="checkbox"'
 888             . ' name="criteriaColumnInsert[' . $column_number . ']" />';
 889         $html_output .= '&nbsp;&nbsp;' . __('Del');
 890         $html_output .= '<input type="checkbox"'
 891             . ' name="criteriaColumnDelete[' . $column_number . ']" />';
 892         $html_output .= '</td>';
 893         return $html_output;
 894     }
 895
 896     /**
 897      * Provides search form's row containing column modifications options
 898      * (For modifying search form's table columns)
 899      *
 900      * @return string HTML for search table's row
 901      */
 902     private function _getModifyColumnsRow()
 903     {
 904         $html_output = '<tr class="even noclick">';
 905         $html_output .= '<th>' . __('Modify:') . '</th>';
 906         $new_column_count = 0;
 907         for (
 908         $column_index = 0;
 909         $column_index < $this->_criteria_column_count;
 910         $column_index++
 911         ) {
 912             if (! empty($this->_criteriaColumnInsert)
 913                 && isset($this->_criteriaColumnInsert[$column_index])
 914                 && $this->_criteriaColumnInsert[$column_index] == 'on'
 915             ) {
 916                 $html_output .= $this->_getAndOrColCell($new_column_count);
 917                 $new_column_count++;
 918             } // end if
 919
 920             if (! empty($this->_criteriaColumnDelete)
 921                 && isset($this->_criteriaColumnDelete[$column_index])
 922                 && $this->_criteriaColumnDelete[$column_index] == 'on'
 923             ) {
 924                 continue;
 925             }
 926
 927             if (isset($this->_criteriaAndOrColumn[$column_index])) {
 928                 $this->_curAndOrCol[$new_column_count]
 929                     = $this->_criteriaAndOrColumn[$column_index];
 930             }
 931             $checked_options = array();
 932             if (isset($this->_criteriaAndOrColumn[$column_index])
 933                 && $this->_criteriaAndOrColumn[$column_index] == 'or'
 934             ) {
 935                 $checked_options['or']  = ' checked="checked"';
 936                 $checked_options['and'] = '';
 937             } else {
 938                 $checked_options['and'] = ' checked="checked"';
 939                 $checked_options['or']  = '';
 940             }
 941             $html_output .= $this->_getAndOrColCell(
 942                 $new_column_count,
 943                 $checked_options,
 944                 ($column_index + 1 == $this->_criteria_column_count)
 945             );
 946             $new_column_count++;
 947         } // end for
 948         $html_output .= '</tr>';
 949         return $html_output;
 950     }
 951
 952     /**
 953      * Provides Insert/Delete options for criteria inputbox
 954      * with AND/OR relationship modification options
 955      *
 956      * @param integer $row_index       Number of criteria row
 957      * @param array   $checked_options If checked
 958      *
 959      * @return string HTML
 960      */
 961     private function _getInsDelAndOrCell($row_index, $checked_options)
 962     {
 963         $html_output = '<td class="' . $GLOBALS['cell_align_right'] . ' nowrap">';
 964         $html_output .= '<!-- Row controls -->';
 965         $html_output .= '<table class="nospacing nopadding">';
 966         $html_output .= '<tr>';
 967         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . ' nowrap">';
 968         $html_output .= '<small>' . __('Ins:') . '</small>';
 969         $html_output .= '<input type="checkbox"'
 970             . ' name="criteriaRowInsert[' . $row_index . ']" />';
 971         $html_output .= '</td>';
 972         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . '">';
 973         $html_output .= '<strong>' . __('And:') . '</strong>';
 974         $html_output .= '</td>';
 975         $html_output .= '<td>';
 976         $html_output .= '<input type="radio"'
 977             . ' name="criteriaAndOrRow[' . $row_index . ']" value="and"'
 978             . $checked_options['and'] . ' />';
 979         $html_output .= '</td>';
 980         $html_output .= '</tr>';
 981         $html_output .= '<tr>';
 982         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . ' nowrap">';
 983         $html_output .= '<small>' . __('Del:') . '</small>';
 984         $html_output .= '<input type="checkbox"'
 985             . ' name="criteriaRowDelete[' . $row_index . ']" />';
 986         $html_output .= '</td>';
 987         $html_output .= '<td class="' . $GLOBALS['cell_align_right'] . '">';
 988         $html_output .= '<strong>' . __('Or:') . '</strong>';
 989         $html_output .= '</td>';
 990         $html_output .= '<td>';
 991         $html_output .= '<input type="radio"'
 992             . ' name="criteriaAndOrRow[' . $row_index . ']"'
 993             . ' value="or"' . $checked_options['or'] . ' />';
 994         $html_output .= '</td>';
 995         $html_output .= '</tr>';
 996         $html_output .= '</table>';
 997         $html_output .= '</td>';
 998         return $html_output;
 999     }
1000
1001     /**
1002      * Provides rows for criteria inputbox Insert/Delete options
1003      * with AND/OR relationship modification options
1004      *
1005      * @param integer $new_row_index New row index if rows are added/deleted
1006      *
1007      * @return string HTML table rows
1008      */
1009     private function _getInputboxRow($new_row_index)
1010     {
1011         $html_output = '';
1012         $new_column_count = 0;
1013         for (
1014             $column_index = 0;
1015             $column_index < $this->_criteria_column_count;
1016             $column_index++
1017         ) {
1018             if (!empty($this->_criteriaColumnInsert)
1019                 && isset($this->_criteriaColumnInsert[$column_index])
1020                 && $this->_criteriaColumnInsert[$column_index] == 'on'
1021             ) {
1022                 $orFieldName = 'Or' . $new_row_index . '[' . $new_column_count . ']';
1023                 $html_output .= '<td class="center">';
1024                 $html_output .= '<input type="text"'
1025                     . ' name="Or' . $orFieldName . '" class="textfield"'
1026                     . ' style="width: ' . $this->_realwidth . '" size="20" />';
1027                 $html_output .= '</td>';
1028                 $new_column_count++;
1029             } // end if
1030             if (!empty($this->_criteriaColumnDelete)
1031                 && isset($this->_criteriaColumnDelete[$column_index])
1032                 && $this->_criteriaColumnDelete[$column_index] == 'on'
1033             ) {
1034                 continue;
1035             }
1036             $or = 'Or' . $new_row_index;
1037             if (! empty($_REQUEST[$or]) && isset($_REQUEST[$or][$column_index])) {
1038                 $tmp_or = $_REQUEST[$or][$column_index];
1039             } else {
1040                 $tmp_or     = '';
1041             }
1042             $html_output .= '<td class="center">';
1043             $html_output .= '<input type="text"'
1044                 . ' name="Or' . $new_row_index . '[' . $new_column_count . ']' . '"'
1045                 . ' value="' . htmlspecialchars($tmp_or) . '" class="textfield"'
1046                 . ' style="width: ' . $this->_realwidth . '" size="20" />';
1047             $html_output .= '</td>';
1048             if (!empty(${$or}) && isset(${$or}[$column_index])) {
1049                 $GLOBALS[${'cur' . $or}][$new_column_count]
1050                     = ${$or}[$column_index];
1051             }
1052             $new_column_count++;
1053         } // end for
1054         return $html_output;
1055     }
1056
1057     /**
1058      * Provides rows for criteria inputbox Insert/Delete options
1059      * with AND/OR relationship modification options
1060      *
1061      * @return string HTML table rows
1062      */
1063     private function _getInsDelAndOrCriteriaRows()
1064     {
1065         $html_output = '';
1066         $new_row_count = 0;
1067         $odd_row = true;
1068         $checked_options = array();
1069         for (
1070         $row_index = 0;
1071         $row_index <= $this->_criteria_row_count;
1072         $row_index++
1073         ) {
1074             if (isset($this->_criteriaRowInsert[$row_index])
1075                 && $this->_criteriaRowInsert[$row_index] == 'on'
1076             ) {
1077                 $checked_options['or']  = ' checked="checked"';
1078                 $checked_options['and'] = '';
1079                 $html_output .= '<tr class="' . ($odd_row ? 'odd' : 'even')
1080                     . ' noclick">';
1081                 $html_output .= $this->_getInsDelAndOrCell(
1082                     $new_row_count, $checked_options
1083                 );
1084                 $html_output .= $this->_getInputboxRow(
1085                     $new_row_count
1086                 );
1087                 $new_row_count++;
1088                 $html_output .= '</tr>';
1089                 $odd_row =! $odd_row;
1090             } // end if
1091             if (isset($this->_criteriaRowDelete[$row_index])
1092                 && $this->_criteriaRowDelete[$row_index] == 'on'
1093             ) {
1094                 continue;
1095             }
1096             if (isset($this->_criteriaAndOrRow[$row_index])) {
1097                 $this->_curAndOrRow[$new_row_count]
1098                     = $this->_criteriaAndOrRow[$row_index];
1099             }
1100             if (isset($this->_criteriaAndOrRow[$row_index])
1101                 && $this->_criteriaAndOrRow[$row_index] == 'and'
1102             ) {
1103                 $checked_options['and'] =  ' checked="checked"';
1104                 $checked_options['or']  =  '';
1105             } else {
1106                 $checked_options['or']  =  ' checked="checked"';
1107                 $checked_options['and'] =  '';
1108             }
1109             $html_output .= '<tr class="' . ($odd_row ? 'odd' : 'even')
1110                 . ' noclick">';
1111             $html_output .= $this->_getInsDelAndOrCell(
1112                 $new_row_count, $checked_options
1113             );
1114             $html_output .= $this->_getInputboxRow(
1115                 $new_row_count
1116             );
1117             $new_row_count++;
1118             $html_output .= '</tr>';
1119             $odd_row =! $odd_row;
1120         } // end for
1121         $this->_new_row_count = $new_row_count;
1122         return $html_output;
1123     }
1124
1125     /**
1126      * Provides SELECT clause for building SQL query
1127      *
1128      * @return string Select clause
1129      */
1130     private function _getSelectClause()
1131     {
1132         $select_clause = '';
1133         $select_clauses = array();
1134         for (
1135             $column_index = 0;
1136             $column_index < $this->_criteria_column_count;
1137             $column_index++
1138         ) {
1139             if (! empty($this->_curField[$column_index])
1140                 && isset($this->_curShow[$column_index])
1141                 && $this->_curShow[$column_index] == 'on'
1142             ) {
1143                 $select = $this->_curField[$column_index];
1144                 if (! empty($this->_curAlias[$column_index])) {
1145                     $select .= " AS "
1146                         . PMA_Util::backquote($this->_curAlias[$column_index]);
1147                 }
1148                 $select_clauses[] = $select;
1149             }
1150         } // end for
1151         if ($select_clauses) {
1152             $select_clause = 'SELECT '
1153                 . htmlspecialchars(implode(", ", $select_clauses)) . "\n";
1154         }
1155         return $select_clause;
1156     }
1157
1158     /**
1159      * Provides WHERE clause for building SQL query
1160      *
1161      * @return string Where clause
1162      */
1163     private function _getWhereClause()
1164     {
1165         $where_clause = '';
1166         $criteria_cnt = 0;
1167         for (
1168         $column_index = 0;
1169         $column_index < $this->_criteria_column_count;
1170         $column_index++
1171         ) {
1172             if (! empty($this->_curField[$column_index])
1173                 && ! empty($this->_curCriteria[$column_index])
1174                 && $column_index
1175                 && isset($last_where)
1176                 && isset($this->_curAndOrCol)
1177             ) {
1178                 $where_clause .= ' '
1179                     . /*overload*/mb_strtoupper($this->_curAndOrCol[$last_where])
1180                     . ' ';
1181             }
1182             if (! empty($this->_curField[$column_index])
1183                 && ! empty($this->_curCriteria[$column_index])
1184             ) {
1185                 $where_clause .= '(' . $this->_curField[$column_index] . ' '
1186                     . $this->_curCriteria[$column_index] . ')';
1187                 $last_where = $column_index;
1188                 $criteria_cnt++;
1189             }
1190         } // end for
1191         if ($criteria_cnt > 1) {
1192             $where_clause = '(' . $where_clause . ')';
1193         }
1194         // OR rows ${'cur' . $or}[$column_index]
1195         if (! isset($this->_curAndOrRow)) {
1196             $this->_curAndOrRow = array();
1197         }
1198         for (
1199         $row_index = 0;
1200         $row_index <= $this->_criteria_row_count;
1201         $row_index++
1202         ) {
1203             $criteria_cnt = 0;
1204             $qry_orwhere = '';
1205             $last_orwhere = '';
1206             for (
1207             $column_index = 0;
1208             $column_index < $this->_criteria_column_count;
1209             $column_index++
1210             ) {
1211                 if (! empty($this->_curField[$column_index])
1212                     && ! empty($_REQUEST['Or' . $row_index][$column_index])
1213                     && $column_index
1214                 ) {
1215                     $qry_orwhere .= ' '
1216                         . /*overload*/mb_strtoupper(
1217                             $this->_curAndOrCol[$last_orwhere]
1218                         )
1219                         . ' ';
1220                 }
1221                 if (! empty($this->_curField[$column_index])
1222                     && ! empty($_REQUEST['Or' . $row_index][$column_index])
1223                 ) {
1224                     $qry_orwhere .= '(' . $this->_curField[$column_index]
1225                         .  ' '
1226                         .  $_REQUEST['Or' . $row_index][$column_index]
1227                         .  ')';
1228                     $last_orwhere = $column_index;
1229                     $criteria_cnt++;
1230                 }
1231             } // end for
1232             if ($criteria_cnt > 1) {
1233                 $qry_orwhere      = '(' . $qry_orwhere . ')';
1234             }
1235             if (! empty($qry_orwhere)) {
1236                 $where_clause .= "\n"
1237                     .  /*overload*/mb_strtoupper(
1238                         isset($this->_curAndOrRow[$row_index])
1239                         ? $this->_curAndOrRow[$row_index] . ' '
1240                         : ''
1241                     )
1242                     .  $qry_orwhere;
1243             } // end if
1244         } // end for
1245
1246         if (! empty($where_clause) && $where_clause != '()') {
1247             $where_clause = 'WHERE ' . htmlspecialchars($where_clause) . "\n";
1248         } // end if
1249         return $where_clause;
1250     }
1251
1252     /**
1253      * Provides ORDER BY clause for building SQL query
1254      *
1255      * @return string Order By clause
1256      */
1257     private function _getOrderByClause()
1258     {
1259         $orderby_clause = '';
1260         $orderby_clauses = array();
1261
1262         // Create copy of instance variables
1263         $field = $this->_curField;
1264         $sort = $this->_curSort;
1265         $sortOrder = $this->_curSortOrder;
1266         if ($sortOrder
1267             && count($sortOrder) == count($sort)
1268             && count($sortOrder) == count($field)
1269         ) {
1270             // Sort all three arrays based on sort order
1271             array_multisort($sortOrder, $sort, $field);
1272         }
1273
1274         for (
1275             $column_index = 0;
1276             $column_index < $this->_criteria_column_count;
1277             $column_index++
1278         ) {
1279             // if all columns are chosen with * selector,
1280             // then sorting isn't available
1281             // Fix for Bug #570698
1282             if (empty($field[$column_index])
1283                 && empty($sort[$column_index])
1284             ) {
1285                 continue;
1286             }
1287
1288             if (/*overload*/mb_substr($field[$column_index], -2) == '.*') {
1289                 continue;
1290             }
1291
1292             if (! empty($sort[$column_index])) {
1293                 $orderby_clauses[] = $field[$column_index] . ' '
1294                     . $sort[$column_index];
1295             }
1296         } // end for
1297         if ($orderby_clauses) {
1298             $orderby_clause = 'ORDER BY '
1299                 . htmlspecialchars(implode(", ", $orderby_clauses)) . "\n";
1300         }
1301         return $orderby_clause;
1302     }
1303
1304     /**
1305      * Provides UNIQUE columns and INDEX columns present in criteria tables
1306      *
1307      * @param array $search_tables        Tables involved in the search
1308      * @param array $search_columns       Columns involved in the search
1309      * @param array $where_clause_columns Columns having criteria where clause
1310      *
1311      * @return array having UNIQUE and INDEX columns
1312      */
1313     private function _getIndexes($search_tables, $search_columns,
1314         $where_clause_columns
1315     ) {
1316         $unique_columns = array();
1317         $index_columns = array();
1318
1319         foreach ($search_tables as $table) {
1320             $indexes = $GLOBALS['dbi']->getTableIndexes($this->_db, $table);
1321             foreach ($indexes as $index) {
1322                 $column = $table . '.' . $index['Column_name'];
1323                 if (isset($search_columns[$column])) {
1324                     if ($index['Non_unique'] == 0) {
1325                         if (isset($where_clause_columns[$column])) {
1326                             $unique_columns[$column] = 'Y';
1327                         } else {
1328                             $unique_columns[$column] = 'N';
1329                         }
1330                     } else {
1331                         if (isset($where_clause_columns[$column])) {
1332                             $index_columns[$column] = 'Y';
1333                         } else {
1334                             $index_columns[$column] = 'N';
1335                         }
1336                     }
1337                 }
1338             } // end while (each index of a table)
1339         } // end while (each table)
1340
1341         return array(
1342             'unique' => $unique_columns,
1343             'index' => $index_columns
1344         );
1345     }
1346
1347     /**
1348      * Provides UNIQUE columns and INDEX columns present in criteria tables
1349      *
1350      * @param array $search_tables        Tables involved in the search
1351      * @param array $search_columns       Columns involved in the search
1352      * @param array $where_clause_columns Columns having criteria where clause
1353      *
1354      * @return array having UNIQUE and INDEX columns
1355      */
1356     private function _getLeftJoinColumnCandidates($search_tables, $search_columns,
1357         $where_clause_columns
1358     ) {
1359         $GLOBALS['dbi']->selectDb($this->_db);
1360
1361         // Get unique columns and index columns
1362         $indexes = $this->_getIndexes(
1363             $search_tables, $search_columns, $where_clause_columns
1364         );
1365         $unique_columns = $indexes['unique'];
1366         $index_columns = $indexes['index'];
1367
1368         list($candidate_columns, $needsort)
1369             = $this->_getLeftJoinColumnCandidatesBest(
1370                 $search_tables, $where_clause_columns, $unique_columns, $index_columns
1371             );
1372
1373         // If we came up with $unique_columns (very good) or $index_columns (still
1374         // good) as $candidate_columns we want to check if we have any 'Y' there
1375         // (that would mean that they were also found in the whereclauses
1376         // which would be great). if yes, we take only those
1377         if ($needsort != 1) {
1378             return $candidate_columns;
1379         }
1380
1381         $very_good = array();
1382         $still_good = array();
1383         foreach ($candidate_columns as $column => $is_where) {
1384             $table = explode('.', $column);
1385             $table = $table[0];
1386             if ($is_where == 'Y') {
1387                 $very_good[$column] = $table;
1388             } else {
1389                 $still_good[$column] = $table;
1390             }
1391         }
1392         if (count($very_good) > 0) {
1393             $candidate_columns = $very_good;
1394             // Candidates restricted in index+where
1395         } else {
1396             $candidate_columns = $still_good;
1397             // None of the candidates where in a where-clause
1398         }
1399
1400         return $candidate_columns;
1401     }
1402
1403     /**
1404      * Provides the main table to form the LEFT JOIN clause
1405      *
1406      * @param array $search_tables        Tables involved in the search
1407      * @param array $search_columns       Columns involved in the search
1408      * @param array $where_clause_columns Columns having criteria where clause
1409      * @param array $where_clause_tables  Tables having criteria where clause
1410      *
1411      * @return string table name
1412      */
1413     private function _getMasterTable($search_tables, $search_columns,
1414         $where_clause_columns, $where_clause_tables
1415     ) {
1416         if (count($where_clause_tables) == 1) {
1417             // If there is exactly one column that has a decent where-clause
1418             // we will just use this
1419             $master = key($where_clause_tables);
1420             return $master;
1421         }
1422
1423         // Now let's find out which of the tables has an index
1424         // (When the control user is the same as the normal user
1425         // because he is using one of his databases as pmadb,
1426         // the last db selected is not always the one where we need to work)
1427         $candidate_columns = $this->_getLeftJoinColumnCandidates(
1428             $search_tables, $search_columns, $where_clause_columns
1429         );
1430
1431         // Generally, we need to display all the rows of foreign (referenced)
1432         // table, whether they have any matching row in child table or not.
1433         // So we select candidate tables which are foreign tables.
1434         $foreign_tables = array();
1435         foreach ($candidate_columns as $one_table) {
1436             $foreigners = PMA_getForeigners($this->_db, $one_table);
1437             foreach ($foreigners as $key => $foreigner) {
1438                 if ($key != 'foreign_keys_data') {
1439                     if (in_array($foreigner['foreign_table'], $candidate_columns)) {
1440                         $foreign_tables[$foreigner['foreign_table']]
1441                             = $foreigner['foreign_table'];
1442                     }
1443                     continue;
1444                 }
1445                 foreach ($foreigner as $one_key) {
1446                     if (in_array($one_key['ref_table_name'], $candidate_columns)) {
1447                         $foreign_tables[$one_key['ref_table_name']]
1448                             = $one_key['ref_table_name'];
1449                     }
1450                 }
1451             }
1452         }
1453         if (count($foreign_tables)) {
1454             $candidate_columns = $foreign_tables;
1455         }
1456
1457         // If our array of candidates has more than one member we'll just
1458         // find the smallest table.
1459         // Of course the actual query would be faster if we check for
1460         // the Criteria which gives the smallest result set in its table,
1461         // but it would take too much time to check this
1462         if (!(count($candidate_columns) > 1)) {
1463             reset($candidate_columns);
1464             $master = current($candidate_columns); // Only one single candidate
1465             return $master;
1466         }
1467
1468         // Of course we only want to check each table once
1469         $checked_tables = $candidate_columns;
1470         $tsize = array();
1471         $csize = array();
1472         foreach ($candidate_columns as $table) {
1473             if ($checked_tables[$table] != 1) {
1474                 $tsize[$table] = PMA_Table::countRecords(
1475                     $this->_db,
1476                     $table,
1477                     false
1478                 );
1479                 $checked_tables[$table] = 1;
1480             }
1481             $csize[$table] = $tsize[$table];
1482         }
1483         arsort($csize);
1484         reset($csize);
1485         $master = key($csize); // Largest
1486
1487         return $master;
1488     }
1489
1490     /**
1491      * Provides columns and tables that have valid where clause criteria
1492      *
1493      * @return array
1494      */
1495     private function _getWhereClauseTablesAndColumns()
1496     {
1497         $where_clause_columns = array();
1498         $where_clause_tables = array();
1499
1500         // Now we need all tables that we have in the where clause
1501         for (
1502             $column_index = 0, $nb = count($this->_criteria);
1503             $column_index < $nb;
1504             $column_index++
1505         ) {
1506             $current_table = explode('.', $_POST['criteriaColumn'][$column_index]);
1507             if (empty($current_table[0]) || empty($current_table[1])) {
1508                 continue;
1509             } // end if
1510             $table = str_replace('`', '', $current_table[0]);
1511             $column = str_replace('`', '', $current_table[1]);
1512             $column = $table . '.' . $column;
1513             // Now we know that our array has the same numbers as $criteria
1514             // we can check which of our columns has a where clause
1515             if (! empty($this->_criteria[$column_index])) {
1516                 if (/*overload*/mb_substr($this->_criteria[$column_index], 0, 1) == '='
1517                     || /*$pmaString->*/stristr($this->_criteria[$column_index], 'is')
1518                 ) {
1519                     $where_clause_columns[$column] = $column;
1520                     $where_clause_tables[$table]  = $table;
1521                 }
1522             } // end if
1523         } // end for
1524         return array(
1525             'where_clause_tables' => $where_clause_tables,
1526             'where_clause_columns' => $where_clause_columns
1527         );
1528     }
1529
1530     /**
1531      * Provides FROM clause for building SQL query
1532      *
1533      * @return string FROM clause
1534      */
1535     private function _getFromClause()
1536     {
1537         $from_clause = '';
1538         if (isset($_POST['criteriaColumn']) && count($_POST['criteriaColumn']) > 0) {
1539             // Initialize some variables
1540             $search_tables = $search_columns = array();
1541
1542             // We only start this if we have fields, otherwise it would be dumb
1543             foreach ($_POST['criteriaColumn'] as $value) {
1544                 $parts = explode('.', $value);
1545                 if (! empty($parts[0]) && ! empty($parts[1])) {
1546                     $table = str_replace('`', '', $parts[0]);
1547                     $search_tables[$table] = $table;
1548                     $search_columns[] = $table . '.' . str_replace('`', '', $parts[1]);
1549                 }
1550             } // end while
1551
1552             // Create LEFT JOINS out of Relations
1553             $from_clause = $this->_getJoinForFromClause($search_tables, $search_columns);
1554
1555             // In case relations are not defined, just generate the FROM clause
1556             // from the list of tables, however we don't generate any JOIN
1557             if (empty($from_clause)) {
1558                 // Create cartesian product
1559                 $from_clause = implode(
1560                     ", ", array_map('PMA_Util::backquote', $search_tables)
1561                 );
1562             }
1563         } // end count($_POST['criteriaColumn']) > 0
1564
1565         return $from_clause;
1566     }
1567
1568     /**
1569      * Formulates the WHERE clause by JOINing tables
1570      *
1571      * @param array $searchTables  Tables involved in the search
1572      * @param array $searchColumns Columns involved in the search
1573      *
1574      * @return string table name
1575      */
1576     private function _getJoinForFromClause($searchTables, $searchColumns)
1577     {
1578         // $relations[master_table][foreign_table] => clause
1579         $relations = array();
1580
1581         // Fill $relations with inter table relationship data
1582         foreach ($searchTables as $oneTable) {
1583             $this->_loadRelationsForTable($relations, $oneTable);
1584         }
1585
1586         // Get tables and columns with valid where clauses
1587         $validWhereClauses = $this->_getWhereClauseTablesAndColumns();
1588         $whereClauseTables = $validWhereClauses['where_clause_tables'];
1589         $whereClauseColumns = $validWhereClauses['where_clause_columns'];
1590
1591         // Get master table
1592         $master = $this->_getMasterTable(
1593             $searchTables, $searchColumns,
1594             $whereClauseColumns, $whereClauseTables
1595         );
1596
1597         // Will incldue master tables and all tables that can be combined into
1598         // a cluster by their relation
1599         $finalized = array();
1600         if (mb_strlen($master) > 0) {
1601             // Add master tables
1602             $finalized[$master] = '';
1603         }
1604         // Fill the $finalized array with JOIN clauses for each table
1605         $this->_fillJoinClauses($finalized, $relations, $searchTables);
1606
1607         // JOIN clause
1608         $join = '';
1609
1610         // Tables that can not be combined with the table cluster
1611         // which includes master table
1612         $unfinalized = array_diff($searchTables, array_keys($finalized));
1613         if (count($unfinalized) > 0) {
1614
1615             // We need to look for intermediary tables to JOIN unfinalized tables
1616             // Heuristic to chose intermediary tables is to look for tables
1617             // having relationships with unfinalized tables
1618             foreach ($unfinalized as $oneTable) {
1619
1620                 $references = PMA_getChildReferences($this->_db, $oneTable);
1621                 foreach ($references as $column => $columnReferences) {
1622                     foreach ($columnReferences as $reference) {
1623
1624                         // Only from this schema
1625                         if ($reference['table_schema'] == $this->_db) {
1626                             $table = $reference['table_name'];
1627
1628                             $this->_loadRelationsForTable($relations, $table);
1629
1630                             // Make copies
1631                             $tempFinalized = $finalized;
1632                             $tempSearchTables = $searchTables;
1633                             $tempSearchTables[] = $table;
1634
1635                             // Try joining with the added table
1636                             $this->_fillJoinClauses(
1637                                 $tempFinalized, $relations, $tempSearchTables
1638                             );
1639
1640                             $tempUnfinalized = array_diff(
1641                                 $tempSearchTables, array_keys($tempFinalized)
1642                             );
1643                             // Take greedy approach.
1644                             // If the unfinalized count drops we keep the new table
1645                             // and switch temporary varibles with the original ones
1646                             if (count($tempUnfinalized) < count($unfinalized)) {
1647                                 $finalized = $tempFinalized;
1648                                 $searchTables = $tempSearchTables;
1649                             }
1650
1651                             // We are done if no unfinalized tables anymore
1652                             if (count($tempUnfinalized) == 0) {
1653                                 break 3;
1654                             }
1655                         }
1656                     }
1657                 }
1658             }
1659
1660             $unfinalized = array_diff($searchTables, array_keys($finalized));
1661             // If there are still unfinalized tables
1662             if (count($unfinalized) > 0) {
1663                 // Add these tables as cartesian product before joined tables
1664                 $join .= implode(
1665                     ', ', array_map('PMA_Util::backquote', $unfinalized)
1666                 );
1667             }
1668         }
1669
1670         $first = true;
1671         // Add joined tables
1672         foreach ($finalized as $table => $clause) {
1673             if ($first) {
1674                 if (! empty($join)) {
1675                     $join .= ", ";
1676                 }
1677                 $join .= PMA_Util::backquote($table);
1678                 $first = false;
1679             } else {
1680                 $join .= "\n    LEFT JOIN " . PMA_Util::backquote(
1681                     $table
1682                 ) . " ON " . $clause;
1683             }
1684         }
1685
1686         return $join;
1687     }
1688
1689     /**
1690      * Loads relations for a given table into the $relations array
1691      *
1692      * @param array  &$relations array of relations
1693      * @param string $oneTable   the table
1694      *
1695      * @return void
1696      */
1697     private function _loadRelationsForTable(&$relations, $oneTable)
1698     {
1699         $relations[$oneTable] = array();
1700
1701         $foreigners = PMA_getForeigners($GLOBALS['db'], $oneTable);
1702         foreach ($foreigners as $field => $foreigner) {
1703             // Foreign keys data
1704             if ($field == 'foreign_keys_data') {
1705                 foreach ($foreigner as $oneKey) {
1706                     $clauses = array();
1707                     // There may be multiple column relations
1708                     foreach ($oneKey['index_list'] as $index => $oneField) {
1709                         $clauses[]
1710                             = PMA_Util::backquote($oneTable) . "."
1711                             . PMA_Util::backquote($oneField) . " = "
1712                             . PMA_Util::backquote($oneKey['ref_table_name']) . "."
1713                             . PMA_Util::backquote($oneKey['ref_index_list'][$index]);
1714                     }
1715                     // Combine multiple column relations with AND
1716                     $relations[$oneTable][$oneKey['ref_table_name']]
1717                         = implode(" AND ", $clauses);
1718                 }
1719             } else { // Internal relations
1720                 $relations[$oneTable][$foreigner['foreign_table']]
1721                     = PMA_Util::backquote($oneTable) . "."
1722                     . PMA_Util::backquote($field) . " = "
1723                     . PMA_Util::backquote($foreigner['foreign_table']) . "."
1724                     . PMA_Util::backquote($foreigner['foreign_field']);
1725             }
1726         }
1727     }
1728
1729     /**
1730      * Fills the $finalized arrays with JOIN clauses for each of the tables
1731      *
1732      * @param array &$finalized   JOIN clauses for each table
1733      * @param array $relations    Relations among tables
1734      * @param array $searchTables Tables involved in the search
1735      *
1736      * @return void
1737      */
1738     private function _fillJoinClauses(&$finalized, $relations, $searchTables)
1739     {
1740         while (true) {
1741             $added = false;
1742             foreach ($searchTables as $masterTable) {
1743                 $foreignData = $relations[$masterTable];
1744                 foreach ($foreignData as $foreignTable => $clause) {
1745                     if (! isset($finalized[$masterTable])
1746                         && isset($finalized[$foreignTable])
1747                     ) {
1748                         $finalized[$masterTable] = $clause;
1749                         $added = true;
1750                     } elseif (! isset($finalized[$foreignTable])
1751                         && isset($finalized[$masterTable])
1752                         && in_array($foreignTable, $searchTables)
1753                     ) {
1754                         $finalized[$foreignTable] = $clause;
1755                         $added = true;
1756                     }
1757                     if ($added) {
1758                         // We are done if all tables are in $finalized
1759                         if (count($finalized) == count($searchTables)) {
1760                             return;
1761                         }
1762                     }
1763                 }
1764             }
1765             // If no new tables were added during this iteration, break;
1766             if (! $added) {
1767                 return;
1768             }
1769         }
1770     }
1771
1772     /**
1773      * Provides the generated SQL query
1774      *
1775      * @return string SQL query
1776      */
1777     private function _getSQLQuery()
1778     {
1779         $sql_query = '';
1780         // get SELECT clause
1781         $sql_query .= $this->_getSelectClause();
1782         // get FROM clause
1783         $from_clause = $this->_getFromClause();
1784         if (! empty($from_clause)) {
1785             $sql_query .= 'FROM ' . htmlspecialchars($from_clause) . "\n";
1786         }
1787         // get WHERE clause
1788         $sql_query .= $this->_getWhereClause();
1789         // get ORDER BY clause
1790         $sql_query .= $this->_getOrderByClause();
1791         return $sql_query;
1792     }
1793
1794     /**
1795      * Provides the generated QBE form
1796      *
1797      * @return string QBE form
1798      */
1799     public function getSelectionForm()
1800     {
1801         $html_output = '<form action="db_qbe.php" method="post" id="formQBE" class="lock-page">';
1802         $html_output .= '<fieldset>';
1803
1804         if ($GLOBALS['cfgRelation']['savedsearcheswork']) {
1805             $html_output .= $this->_getSavedSearchesField();
1806         }
1807
1808         $html_output .= '<table class="data" style="width: 100%;">';
1809         // Get table's <tr> elements
1810         $html_output .= $this->_getColumnNamesRow();
1811         $html_output .= $this->_getColumnAliasRow();
1812         $html_output .= $this->_getShowRow();
1813         $html_output .= $this->_getSortRow();
1814         $html_output .= $this->_getSortOrder();
1815         $html_output .= $this->_getCriteriaInputboxRow();
1816         $html_output .= $this->_getInsDelAndOrCriteriaRows();
1817         $html_output .= $this->_getModifyColumnsRow();
1818         $html_output .= '</table>';
1819         $this->_new_row_count--;
1820         $url_params = array();
1821         $url_params['db'] = $this->_db;
1822         $url_params['criteriaColumnCount'] = $this->_new_column_count;
1823         $url_params['rows'] = $this->_new_row_count;
1824         $html_output .= PMA_URL_getHiddenInputs($url_params);
1825         $html_output .= '</fieldset>';
1826         // get footers
1827         $html_output .= $this->_getTableFooters();
1828         // get tables select list
1829         $html_output .= $this->_getTablesList();
1830         $html_output .= '</form>';
1831         $html_output .= '<form action="db_qbe.php" method="post" class="lock-page">';
1832         $html_output .= PMA_URL_getHiddenInputs(array('db' => $this->_db));
1833         // get SQL query
1834         $html_output .= '<div class="floatleft" style="width:50%">';
1835         $html_output .= '<fieldset>';
1836         $html_output .= '<legend>'
1837             . sprintf(
1838                 __('SQL query on database <b>%s</b>:'),
1839                 PMA_Util::getDbLink($this->_db)
1840             );
1841         $html_output .= '</legend>';
1842         $text_dir = 'ltr';
1843         $html_output .= '<textarea cols="80" name="sql_query" id="textSqlquery"'
1844             . ' rows="' . ((count($this->_criteriaTables) > 30) ? '15' : '7') . '"'
1845             . ' dir="' . $text_dir . '">';
1846         $html_output .= $this->_getSQLQuery();
1847         $html_output .= '</textarea>';
1848         $html_output .= '</fieldset>';
1849         // displays form's footers
1850         $html_output .= '<fieldset class="tblFooters">';
1851         $html_output .= '<input type="hidden" name="submit_sql" value="1" />';
1852         $html_output .= '<input type="submit" value="' . __('Submit Query') . '" />';
1853         $html_output .= '</fieldset>';
1854         $html_output .= '</div>';
1855         $html_output .= '</form>';
1856         return $html_output;
1857     }
1858
1859     /**
1860      * Get fields to display
1861      *
1862      * @return string
1863      */
1864     private function _getSavedSearchesField()
1865     {
1866         $html_output = __('Saved bookmarked search:');
1867         $html_output .= ' <select name="searchId" id="searchId">';
1868         $html_output .= '<option value="">' . __('New bookmark') . '</option>';
1869
1870         $currentSearch = $this->_getCurrentSearch();
1871         $currentSearchId = null;
1872         $currentSearchName = null;
1873         if (null != $currentSearch) {
1874             $currentSearchId = $currentSearch->getId();
1875             $currentSearchName = $currentSearch->getSearchName();
1876         }
1877
1878         foreach ($this->_savedSearchList as $id => $name) {
1879             $html_output .= '<option value="' . htmlspecialchars($id)
1880                 . '" ' . (
1881                 $id == $currentSearchId
1882                     ? 'selected="selected" '
1883                     : ''
1884                 )
1885                 . '>'
1886                 . htmlspecialchars($name)
1887                 . '</option>';
1888         }
1889         $html_output .= '</select>';
1890         $html_output .= '<input type="text" name="searchName" id="searchName" '
1891             . 'value="' . $currentSearchName . '" />';
1892         $html_output .= '<input type="hidden" name="action" id="action" value="" />';
1893         $html_output .= '<input type="submit" name="saveSearch" id="saveSearch" '
1894             . 'value="' . __('Create bookmark') . '" />';
1895         if (null !== $currentSearchId) {
1896             $html_output .= '<input type="submit" name="updateSearch" '
1897                 . 'id="updateSearch" value="' . __('Update bookmark') . '" />';
1898             $html_output .= '<input type="submit" name="deleteSearch" '
1899                 . 'id="deleteSearch" value="' . __('Delete bookmark') . '" />';
1900         }
1901
1902         return $html_output;
1903     }
1904
1905     /**
1906      * Initialize _criteria_column_count
1907      *
1908      * @return int Previous number of columns
1909      */
1910     private function _initializeCriteriasCount()
1911     {
1912         // sets column count
1913         $criteriaColumnCount = PMA_ifSetOr(
1914             $_REQUEST['criteriaColumnCount'],
1915             3,
1916             'numeric'
1917         );
1918         $criteriaColumnAdd = PMA_ifSetOr(
1919             $_REQUEST['criteriaColumnAdd'],
1920             0,
1921             'numeric'
1922         );
1923         $this->_criteria_column_count = max(
1924             $criteriaColumnCount + $criteriaColumnAdd,
1925             0
1926         );
1927
1928         // sets row count
1929         $rows = PMA_ifSetOr($_REQUEST['rows'], 0, 'numeric');
1930         $criteriaRowAdd = PMA_ifSetOr($_REQUEST['criteriaRowAdd'], 0, 'numeric');
1931         $this->_criteria_row_count = max($rows + $criteriaRowAdd, 0);
1932
1933         return $criteriaColumnCount;
1934     }
1935
1936     /**
1937      * Get best
1938      *
1939      * @param array $search_tables        Tables involved in the search
1940      * @param array $where_clause_columns Columns with where clause
1941      * @param array $unique_columns       Unique columns
1942      * @param array $index_columns        Indexed columns
1943      *
1944      * @return array
1945      */
1946     private function _getLeftJoinColumnCandidatesBest(
1947         $search_tables, $where_clause_columns, $unique_columns, $index_columns
1948     ) {
1949         // now we want to find the best.
1950         if (isset($unique_columns) && count($unique_columns) > 0) {
1951             $candidate_columns = $unique_columns;
1952             $needsort = 1;
1953             return array($candidate_columns, $needsort);
1954         } elseif (isset($index_columns) && count($index_columns) > 0) {
1955             $candidate_columns = $index_columns;
1956             $needsort = 1;
1957             return array($candidate_columns, $needsort);
1958         } elseif (isset($where_clause_columns) && count($where_clause_columns) > 0) {
1959             $candidate_columns = $where_clause_columns;
1960             $needsort = 0;
1961             return array($candidate_columns, $needsort);
1962         } else {
1963             $candidate_columns = $search_tables;
1964             $needsort = 0;
1965             return array($candidate_columns, $needsort);
1966         }
1967     }
1968 }