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