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