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