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(' ', '&nbsp;', 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 .= '&nbsp;&nbsp;<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 .= '&nbsp;&nbsp;' . __('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