Merge remote-tracking branch 'origin/master' into drizzle
[phpmyadmin.git] / db_qbe.php
blob00d39f6433539b3e0e18c72f5405a277a0d4d7cf
1 <?php
2 /* vim: set expandtab sw=4 ts=4 sts=4: */
3 /**
4 * query by example the whole database
6 * @package phpMyAdmin
7 */
9 /**
10 * requirements
12 require_once './libraries/common.inc.php';
14 /**
15 * Gets the relation settings
17 $cfgRelation = PMA_getRelationsParam();
20 /**
21 * A query has been submitted -> (maybe) execute it
23 $message_to_display = false;
24 if (isset($_REQUEST['submit_sql']) && ! empty($sql_query)) {
25 if (! preg_match('@^SELECT@i', $sql_query)) {
26 $message_to_display = true;
27 } else {
28 $goto = 'db_sql.php';
29 require './sql.php';
30 exit;
34 $sub_part = '_qbe';
35 require './libraries/db_common.inc.php';
36 $url_query .= '&amp;goto=db_qbe.php';
37 $url_params['goto'] = 'db_qbe.php';
38 require './libraries/db_info.inc.php';
40 if ($message_to_display) {
41 PMA_Message::error(__('You have to choose at least one column to display'))->display();
43 unset($message_to_display);
45 /**
46 * Initialize some variables
48 $col_cnt = PMA_ifSetOr($_REQUEST['col_cnt'], 3, 'numeric');
49 $add_col = PMA_ifSetOr($_REQUEST['add_col'], 0, 'numeric');
50 $add_row = PMA_ifSetOr($_REQUEST['add_row'], 0, 'numeric');
52 $rows = PMA_ifSetOr($_REQUEST['rows'], 0, 'numeric');
53 $ins_col = PMA_ifSetOr($_REQUEST['ins_col'], null, 'array');
54 $del_col = PMA_ifSetOr($_REQUEST['del_col'], null, 'array');
56 $prev_criteria = isset($_REQUEST['prev_criteria'])
57 ? $_REQUEST['prev_criteria']
58 : array();
59 $criteria = isset($_REQUEST['criteria'])
60 ? $_REQUEST['criteria']
61 : array_fill(0, $col_cnt, '');
63 $ins_row = isset($_REQUEST['ins_row'])
64 ? $_REQUEST['ins_row']
65 : array_fill(0, $col_cnt, '');
66 $del_row = isset($_REQUEST['del_row'])
67 ? $_REQUEST['del_row']
68 : array_fill(0, $col_cnt, '');
69 $and_or_row = isset($_REQUEST['and_or_row'])
70 ? $_REQUEST['and_or_row']
71 : array_fill(0, $col_cnt, '');
72 $and_or_col = isset($_REQUEST['and_or_col'])
73 ? $_REQUEST['and_or_col']
74 : array_fill(0, $col_cnt, '');
76 // minimum width
77 $form_column_width = 12;
78 $col = max($col_cnt + $add_col, 0);
79 $row = max($rows + $add_row, 0);
82 // The tables list sent by a previously submitted form
83 if (PMA_isValid($_REQUEST['TableList'], 'array')) {
84 foreach ($_REQUEST['TableList'] as $each_table) {
85 $tbl_names[$each_table] = ' selected="selected"';
87 } // end if
90 // this was a work in progress, deactivated for now
91 //$columns = PMA_DBI_get_columns_full($GLOBALS['db']);
92 //$tables = PMA_DBI_get_columns_full($GLOBALS['db']);
95 /**
96 * Prepares the form
98 $tbl_result = PMA_DBI_query('SHOW TABLES FROM ' . PMA_backquote($db) . ';', null, PMA_DBI_QUERY_STORE);
99 $tbl_result_cnt = PMA_DBI_num_rows($tbl_result);
100 if (0 == $tbl_result_cnt) {
101 PMA_Message::error(__('No tables found in database.'))->display();
102 require './libraries/footer.inc.php';
103 exit;
106 // The tables list gets from MySQL
107 while (list($tbl) = PMA_DBI_fetch_row($tbl_result)) {
108 $fld_results = PMA_DBI_get_columns($db, $tbl, null, true);
110 if (empty($tbl_names[$tbl]) && !empty($_REQUEST['TableList'])) {
111 $tbl_names[$tbl] = '';
112 } else {
113 $tbl_names[$tbl] = ' selected="selected"';
114 } // end if
116 // The fields list per selected tables
117 if ($tbl_names[$tbl] == ' selected="selected"') {
118 $each_table = PMA_backquote($tbl);
119 $fld[] = $each_table . '.*';
120 foreach ($fld_results as $each_field) {
121 $each_field = $each_table . '.' . PMA_backquote($each_field['Field']);
122 $fld[] = $each_field;
124 // increase the width if necessary
125 $form_column_width = max(strlen($each_field), $form_column_width);
126 } // end foreach
127 } // end if
128 } // end while
129 PMA_DBI_free_result($tbl_result);
131 // largest width found
132 $realwidth = $form_column_width . 'ex';
136 * Displays the Query by example form
140 * Enter description here...
142 * @param array $columns
143 * @param integer $column_number
144 * @param string $selected
146 function showColumnSelectCell($columns, $column_number, $selected = '')
149 <td align="center">
150 <select name="Field[<?php echo $column_number; ?>]" size="1">
151 <option value="">&nbsp;</option>
152 <?php
153 foreach ($columns as $column) {
154 if ($column === $selected) {
155 $sel = ' selected="selected"';
156 } else {
157 $sel = '';
159 echo '<option value="' . htmlspecialchars($column) . '"' . $sel . '>'
160 . str_replace(' ', '&nbsp;', htmlspecialchars($column)) . '</option>' . "\n";
163 </select>
164 </td>
165 <?php
169 <?php if (!empty($tab_designer['link'])) {
170 $tab_designer['link'] = htmlentities($tab_designer['link']);
171 $tab_designer['link'] = $tab_designer['link'] . PMA_generate_common_url($url_params);
172 if (! empty($tab_designer['args'])) {
173 foreach ($tab_designer['args'] as $param => $value) {
174 $tab_designer['link'] .= PMA_get_arg_separator('html') . urlencode($param) . '='
175 . urlencode($value);
179 if (! empty($tab['fragment'])) {
180 $tab['link'] .= $tab['fragment'];
182 if (isset($tab_designer['link'])) {
184 <div id="visual_builder_anchor" class="notice hide">
185 <span id="footnote_1">
186 <?php echo __('Switch to') . ' <a href="' . $tab_designer['link'] . PMA_get_arg_separator('html') . 'query=1">' . __('visual builder') . '</a>'; ?>
187 </span>
188 </div>
189 <?php
192 <form action="db_qbe.php" method="post">
193 <fieldset>
194 <table class="data" style="width: 100%;">
195 <tr class="odd noclick">
196 <th><?php echo __('Column'); ?>:</th>
197 <?php
198 $z = 0;
199 for ($x = 0; $x < $col; $x++) {
200 if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {
201 showColumnSelectCell($fld, $z);
202 $z++;
205 if (! empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
206 continue;
209 $selected = '';
210 if (isset($Field[$x])) {
211 $selected = $Field[$x];
212 $curField[$z] = $Field[$x];
214 showColumnSelectCell($fld, $z, $selected);
215 $z++;
216 } // end for
218 </tr>
220 <!-- Sort row -->
221 <tr class="even noclick">
222 <th><?php echo __('Sort'); ?>:</th>
223 <?php
224 $z = 0;
225 for ($x = 0; $x < $col; $x++) {
226 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
228 <td align="center">
229 <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
230 <option value="">&nbsp;</option>
231 <option value="ASC"><?php echo __('Ascending'); ?></option>
232 <option value="DESC"><?php echo __('Descending'); ?></option>
233 </select>
234 </td>
235 <?php
236 $z++;
237 } // end if
238 echo "\n";
240 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
241 continue;
244 <td align="center">
245 <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
246 <option value="">&nbsp;</option>
247 <?php
248 echo "\n";
250 // If they have chosen all fields using the * selector,
251 // then sorting is not available
252 // Fix for Bug #570698
253 if (isset($Sort[$x]) && isset($Field[$x])
254 && substr($Field[$x], -2) == '.*') {
255 $Sort[$x] = '';
256 } //end if
258 if (isset($Sort[$x]) && $Sort[$x] == 'ASC') {
259 $curSort[$z] = $Sort[$x];
260 $sel = ' selected="selected"';
261 } else {
262 $sel = '';
263 } // end if
264 echo ' ';
265 echo '<option value="ASC"' . $sel . '>' . __('Ascending') . '</option>' . "\n";
266 if (isset($Sort[$x]) && $Sort[$x] == 'DESC') {
267 $curSort[$z] = $Sort[$x];
268 $sel = ' selected="selected"';
269 } else {
270 $sel = '';
271 } // end if
272 echo ' ';
273 echo '<option value="DESC"' . $sel . '>' . __('Descending') . '</option>' . "\n";
275 </select>
276 </td>
277 <?php
278 $z++;
279 echo "\n";
280 } // end for
282 </tr>
284 <!-- Show row -->
285 <tr class="odd noclick">
286 <th><?php echo __('Show'); ?>:</th>
287 <?php
288 $z = 0;
289 for ($x = 0; $x < $col; $x++) {
290 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
292 <td align="center">
293 <input type="checkbox" name="Show[<?php echo $z; ?>]" />
294 </td>
295 <?php
296 $z++;
297 } // end if
298 echo "\n";
300 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
301 continue;
303 if (isset($Show[$x])) {
304 $checked = ' checked="checked"';
305 $curShow[$z] = $Show[$x];
306 } else {
307 $checked = '';
310 <td align="center">
311 <input type="checkbox" name="Show[<?php echo $z; ?>]"<?php echo $checked; ?> />
312 </td>
313 <?php
314 $z++;
315 echo "\n";
316 } // end for
318 </tr>
320 <!-- Criteria row -->
321 <tr class="even noclick">
322 <th><?php echo __('Criteria'); ?>:</th>
323 <?php
324 $z = 0;
325 for ($x = 0; $x < $col; $x++) {
326 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
328 <td align="center">
329 <input type="text" name="criteria[<?php echo $z; ?>]" value="" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
330 </td>
331 <?php
332 $z++;
333 } // end if
334 echo "\n";
336 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
337 continue;
339 if (isset($criteria[$x])) {
340 $tmp_criteria = $criteria[$x];
342 if ((empty($prev_criteria) || ! isset($prev_criteria[$x]))
343 || $prev_criteria[$x] != htmlspecialchars($tmp_criteria)) {
344 $curCriteria[$z] = $tmp_criteria;
345 } else {
346 $curCriteria[$z] = $prev_criteria[$x];
349 <td align="center">
350 <input type="hidden" name="prev_criteria[<?php echo $z; ?>]" value="<?php echo htmlspecialchars($curCriteria[$z]); ?>" />
351 <input type="text" name="criteria[<?php echo $z; ?>]" value="<?php echo htmlspecialchars($tmp_criteria); ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
352 </td>
353 <?php
354 $z++;
355 echo "\n";
356 } // end for
358 </tr>
360 <!-- And/Or columns and rows -->
361 <?php
362 $w = 0;
363 $odd_row = true;
364 for ($y = 0; $y <= $row; $y++) {
365 if (isset($ins_row[$y]) && $ins_row[$y] == 'on') {
366 $chk['or'] = ' checked="checked"';
367 $chk['and'] = '';
369 <tr class="<?php echo $odd_row ? 'odd' : 'even'; ?> noclick">
370 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
371 <!-- Row controls -->
372 <table cellpadding="0" cellspacing="0" border="0">
373 <tr>
374 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
375 <small><?php echo __('Ins'); ?>:</small>
376 <input type="checkbox" name="ins_row[<?php echo $w; ?>]" />
377 </td>
378 <td align="<?php echo $cell_align_right; ?>">
379 <strong><?php echo __('And'); ?>:</strong>
380 </td>
381 <td>
382 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
383 &nbsp;
384 </td>
385 </tr>
386 <tr>
387 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
388 <small><?php echo __('Del'); ?>:</small>
389 <input type="checkbox" name="del_row[<?php echo $w; ?>]" />
390 </td>
391 <td align="<?php echo $cell_align_right; ?>">
392 <strong><?php echo __('Or'); ?>:</strong>
393 </td>
394 <td>
395 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
396 &nbsp;
397 </td>
398 </tr>
399 </table>
400 </td>
401 <?php
402 $z = 0;
403 for ($x = 0; $x < $col; $x++) {
404 if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {
405 echo "\n";
406 $or = 'Or' . $w . '[' . $z . ']';
408 <td align="center">
409 <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
410 </td>
411 <?php
412 $z++;
413 } // end if
414 if (isset($del_col[$x]) && $del_col[$x] == 'on') {
415 continue;
418 echo "\n";
419 $or = 'Or' . $w . '[' . $z . ']';
421 <td align="center">
422 <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
423 </td>
424 <?php
425 $z++;
426 } // end for
427 $w++;
428 echo "\n";
430 </tr>
431 <?php
432 $odd_row =! $odd_row;
433 } // end if
435 if (isset($del_row[$y]) && $del_row[$y] == 'on') {
436 continue;
439 if (isset($and_or_row[$y])) {
440 $curAndOrRow[$w] = $and_or_row[$y];
442 if (isset($and_or_row[$y]) && $and_or_row[$y] == 'and') {
443 $chk['and'] = ' checked="checked"';
444 $chk['or'] = '';
445 } else {
446 $chk['or'] = ' checked="checked"';
447 $chk['and'] = '';
449 echo "\n";
451 <tr class="<?php echo $odd_row ? 'odd' : 'even'; ?> noclick">
452 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
453 <!-- Row controls -->
454 <table border="0" cellpadding="0" cellspacing="0">
455 <tr>
456 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
457 <small><?php echo __('Ins'); ?>:</small>
458 <input type="checkbox" name="ins_row[<?php echo $w; ?>]" />
459 </td>
460 <td align="<?php echo $cell_align_right; ?>">
461 <strong><?php echo __('And'); ?>:</strong>
462 </td>
463 <td>
464 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
465 </td>
466 </tr>
467 <tr>
468 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
469 <small><?php echo __('Del'); ?>:</small>
470 <input type="checkbox" name="del_row[<?php echo $w; ?>]" />
471 </td>
472 <td align="<?php echo $cell_align_right; ?>">
473 <strong><?php echo __('Or'); ?>:</strong>
474 </td>
475 <td>
476 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
477 </td>
478 </tr>
479 </table>
480 </td>
481 <?php
482 $z = 0;
483 for ($x = 0; $x < $col; $x++) {
484 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
485 echo "\n";
486 $or = 'Or' . $w . '[' . $z . ']';
488 <td align="center">
489 <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
490 </td>
491 <?php
492 $z++;
493 } // end if
494 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
495 continue;
498 echo "\n";
499 $or = 'Or' . $y;
500 if (! isset(${$or})) {
501 ${$or} = '';
503 if (!empty(${$or}) && isset(${$or}[$x])) {
504 $tmp_or = ${$or}[$x];
505 } else {
506 $tmp_or = '';
509 <td align="center">
510 <input type="text" name="Or<?php echo $w . '[' . $z . ']'; ?>" value="<?php echo htmlspecialchars($tmp_or); ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
511 </td>
512 <?php
513 if (!empty(${$or}) && isset(${$or}[$x])) {
514 ${'cur' . $or}[$z] = ${$or}[$x];
516 $z++;
517 } // end for
518 $w++;
519 echo "\n";
521 </tr>
522 <?php
523 echo "\n";
524 $odd_row =! $odd_row;
525 } // end for
527 <!-- Modify columns -->
528 <tr class="even noclick">
529 <th><?php echo __('Modify'); ?>:</th>
530 <?php
531 $z = 0;
532 for ($x = 0; $x < $col; $x++) {
533 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
534 $curAndOrCol[$z] = $and_or_col[$y];
535 if ($and_or_col[$z] == 'or') {
536 $chk['or'] = ' checked="checked"';
537 $chk['and'] = '';
538 } else {
539 $chk['and'] = ' checked="checked"';
540 $chk['or'] = '';
543 <td align="center">
544 <strong><?php echo __('Or'); ?>:</strong>
545 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
546 &nbsp;&nbsp;<strong><?php echo __('And'); ?>:</strong>
547 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
548 <br />
549 <?php echo __('Ins') . "\n"; ?>
550 <input type="checkbox" name="ins_col[<?php echo $z; ?>]" />
551 &nbsp;&nbsp;<?php echo __('Del') . "\n"; ?>
552 <input type="checkbox" name="del_col[<?php echo $z; ?>]" />
553 </td>
554 <?php
555 $z++;
556 } // end if
557 echo "\n";
559 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
560 continue;
563 if (isset($and_or_col[$y])) {
564 $curAndOrCol[$z] = $and_or_col[$y];
566 if (isset($and_or_col[$z]) && $and_or_col[$z] == 'or') {
567 $chk['or'] = ' checked="checked"';
568 $chk['and'] = '';
569 } else {
570 $chk['and'] = ' checked="checked"';
571 $chk['or'] = '';
574 <td align="center">
575 <strong><?php echo __('Or'); ?>:</strong>
576 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
577 &nbsp;&nbsp;<strong><?php echo __('And'); ?>:</strong>
578 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
579 <br />
580 <?php echo __('Ins') . "\n"; ?>
581 <input type="checkbox" name="ins_col[<?php echo $z; ?>]" />
582 &nbsp;&nbsp;<?php echo __('Del') . "\n"; ?>
583 <input type="checkbox" name="del_col[<?php echo $z; ?>]" />
584 </td>
585 <?php
586 $z++;
587 echo "\n";
588 } // end for
590 </tr>
591 </table>
593 <!-- Other controls -->
594 <?php
595 $w--;
596 $url_params['db'] = $db;
597 $url_params['col_cnt'] = $z;
598 $url_params['rows'] = $w;
599 echo PMA_generate_common_hidden_inputs($url_params);
601 </fieldset>
602 <fieldset class="tblFooters">
603 <table border="0" cellpadding="2" cellspacing="1">
604 <tr>
605 <td nowrap="nowrap">
606 <?php echo __('Add/Delete criteria rows'); ?>:
607 <select size="1" name="add_row">
608 <option value="-3">-3</option>
609 <option value="-2">-2</option>
610 <option value="-1">-1</option>
611 <option value="0" selected="selected">0</option>
612 <option value="1">1</option>
613 <option value="2">2</option>
614 <option value="3">3</option>
615 </select>
616 </td>
617 <td width="10">&nbsp;</td>
618 <td nowrap="nowrap"><?php echo __('Add/Delete columns'); ?>:
619 <select size="1" name="add_col">
620 <option value="-3">-3</option>
621 <option value="-2">-2</option>
622 <option value="-1">-1</option>
623 <option value="0" selected="selected">0</option>
624 <option value="1">1</option>
625 <option value="2">2</option>
626 <option value="3">3</option>
627 </select>
628 </td>
629 <td width="10">&nbsp;</td>
630 <!-- Generates a query -->
631 <td><input type="submit" name="modify" value="<?php echo __('Update Query'); ?>" /></td>
632 </tr>
633 </table>
634 </fieldset>
636 <table>
637 <tr><td>
638 <fieldset>
639 <legend><?php echo __('Use Tables'); ?></legend>
640 <?php
641 $options = '';
642 $numTableListOptions = 0;
643 foreach ($tbl_names as $key => $val) {
644 $options .= ' ';
645 $options .= '<option value="' . htmlspecialchars($key) . '"' . $val . '>'
646 . str_replace(' ', '&nbsp;', htmlspecialchars($key)) . '</option>' . "\n";
647 $numTableListOptions++;
650 <select name="TableList[]" multiple="multiple" id="listTable"
651 size="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>">
652 <?php echo $options; ?>
653 </select>
654 </fieldset>
655 <fieldset class="tblFooters">
656 <input type="submit" name="modify" value="<?php echo __('Update Query'); ?>" />
657 </fieldset>
658 </td>
659 <td width="20">&nbsp;</td>
660 <td>
661 <fieldset>
662 <legend><?php echo sprintf(__('SQL query on database <b>%s</b>:'), PMA_getDbLink($db)); ?>
663 </legend>
664 <textarea cols="80" name="sql_query" id="textSqlquery"
665 rows="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>"
666 dir="<?php echo $text_dir; ?>">
667 <?php
668 // 1. SELECT
669 $last_select = 0;
670 if (! isset($qry_select)) {
671 $qry_select = '';
673 for ($x = 0; $x < $col; $x++) {
674 if (!empty($curField[$x]) && isset($curShow[$x]) && $curShow[$x] == 'on') {
675 if ($last_select) {
676 $qry_select .= ', ';
678 $qry_select .= $curField[$x];
679 $last_select = 1;
681 } // end for
682 if (!empty($qry_select)) {
683 echo 'SELECT ' . htmlspecialchars($qry_select) . "\n";
686 // 2. FROM
688 // Create LEFT JOINS out of Relations
689 // If we can use Relations we could make some left joins.
690 // First find out if relations are available in this database.
692 // First we need the really needed Tables - those in TableList might still be
693 // all Tables.
694 if (isset($Field) && count($Field) > 0) {
695 // Initialize some variables
696 $tab_all = array();
697 $col_all = array();
698 $tab_wher = array();
699 $tab_know = array();
700 $tab_left = array();
701 $col_where = array();
702 $fromclause = '';
704 // We only start this if we have fields, otherwise it would be dumb
705 foreach ($Field as $value) {
706 $parts = explode('.', $value);
707 if (!empty($parts[0]) && !empty($parts[1])) {
708 $tab_raw = $parts[0];
709 $tab = str_replace('`', '', $tab_raw);
710 $tab_all[$tab] = $tab;
712 $col_raw = $parts[1];
713 $col_all[] = $tab . '.' . str_replace('`', '', $col_raw);
715 } // end while
717 // Check 'where' clauses
718 if ($cfgRelation['relwork'] && count($tab_all) > 0) {
719 // Now we need all tables that we have in the where clause
720 $crit_cnt = count($criteria);
721 for ($x = 0; $x < $crit_cnt; $x++) {
722 $curr_tab = explode('.', $Field[$x]);
723 if (!empty($curr_tab[0]) && !empty($curr_tab[1])) {
724 $tab_raw = $curr_tab[0];
725 $tab = str_replace('`', '', $tab_raw);
727 $col_raw = $curr_tab[1];
728 $col1 = str_replace('`', '', $col_raw);
729 $col1 = $tab . '.' . $col1;
730 // Now we know that our array has the same numbers as $criteria
731 // we can check which of our columns has a where clause
732 if (!empty($criteria[$x])) {
733 if (substr($criteria[$x], 0, 1) == '=' || stristr($criteria[$x], 'is')) {
734 $col_where[$col] = $col1;
735 $tab_wher[$tab] = $tab;
737 } // end if
738 } // end if
739 } // end for
741 // Cleans temp vars w/o further use
742 unset($tab_raw);
743 unset($col_raw);
744 unset($col1);
746 if (count($tab_wher) == 1) {
747 // If there is exactly one column that has a decent where-clause
748 // we will just use this
749 $master = key($tab_wher);
750 } else {
751 // Now let's find out which of the tables has an index
752 // (When the control user is the same as the normal user
753 // because he is using one of his databases as pmadb,
754 // the last db selected is not always the one where we need to work)
755 PMA_DBI_select_db($db);
757 foreach ($tab_all as $tab) {
758 $ind_rs = PMA_DBI_query(PMA_DBI_get_table_indexes_sql($db, $tab));
759 while ($ind = PMA_DBI_fetch_assoc($ind_rs)) {
760 $col1 = $tab . '.' . $ind['Column_name'];
761 if (isset($col_all[$col1])) {
762 if ($ind['Non_unique'] == 0) {
763 if (isset($col_where[$col1])) {
764 $col_unique[$col1] = 'Y';
765 } else {
766 $col_unique[$col1] = 'N';
768 } else {
769 if (isset($col_where[$col1])) {
770 $col_index[$col1] = 'Y';
771 } else {
772 $col_index[$col1] = 'N';
776 } // end while (each col of tab)
777 } // end while (each tab)
778 // now we want to find the best.
779 if (isset($col_unique) && count($col_unique) > 0) {
780 $col_cand = $col_unique;
781 $needsort = 1;
782 } elseif (isset($col_index) && count($col_index) > 0) {
783 $col_cand = $col_index;
784 $needsort = 1;
785 } elseif (isset($col_where) && count($col_where) > 0) {
786 $col_cand = $tab_wher;
787 $needsort = 0;
788 } else {
789 $col_cand = $tab_all;
790 $needsort = 0;
793 // If we came up with $col_unique (very good) or $col_index (still
794 // good) as $col_cand we want to check if we have any 'Y' there
795 // (that would mean that they were also found in the whereclauses
796 // which would be great). if yes, we take only those
797 if ($needsort == 1) {
798 foreach ($col_cand as $col => $is_where) {
799 $tab = explode('.', $col);
800 $tab = $tab[0];
801 if ($is_where == 'Y') {
802 $vg[$col] = $tab;
803 } else {
804 $sg[$col] = $tab;
807 if (isset($vg)) {
808 $col_cand = $vg;
809 // Candidates restricted in index+where
810 } else {
811 $col_cand = $sg;
812 // None of the candidates where in a where-clause
816 // If our array of candidates has more than one member we'll just
817 // find the smallest table.
818 // Of course the actual query would be faster if we check for
819 // the Criteria which gives the smallest result set in its table,
820 // but it would take too much time to check this
821 if (count($col_cand) > 1) {
822 // Of course we only want to check each table once
823 $checked_tables = $col_cand;
824 foreach ($col_cand as $tab) {
825 if ($checked_tables[$tab] != 1) {
826 $tsize[$tab] = PMA_Table::countRecords($db, $tab, false);
827 $checked_tables[$tab] = 1;
829 $csize[$tab] = $tsize[$tab];
831 asort($csize);
832 reset($csize);
833 $master = key($csize); // Smallest
834 } else {
835 reset($col_cand);
836 $master = current($col_cand); // Only one single candidate
838 } // end if (exactly one where clause)
840 $tab_left = $tab_all;
841 unset($tab_left[$master]);
842 $tab_know[$master] = $master;
844 $run = 0;
845 $emerg = '';
846 while (count($tab_left) > 0) {
847 if ($run % 2 == 0) {
848 PMA_getRelatives('master');
849 } else {
850 PMA_getRelatives('foreign');
852 $run++;
853 if ($run > 5) {
855 foreach ($tab_left as $tab) {
856 $emerg .= ', ' . PMA_backquote($tab);
857 unset($tab_left[$tab]);
860 } // end while
861 $qry_from = PMA_backquote($master) . $emerg . $fromclause;
862 } // end if ($cfgRelation['relwork'] && count($tab_all) > 0)
864 } // end count($Field) > 0
866 // In case relations are not defined, just generate the FROM clause
867 // from the list of tables, however we don't generate any JOIN
869 if (empty($qry_from) && isset($tab_all)) {
870 $qry_from = implode(', ', $tab_all);
872 // Now let's see what we got
873 if (!empty($qry_from)) {
874 echo 'FROM ' . htmlspecialchars($qry_from) . "\n";
877 // 3. WHERE
878 $qry_where = '';
879 $criteria_cnt = 0;
880 for ($x = 0; $x < $col; $x++) {
881 if (!empty($curField[$x]) && !empty($curCriteria[$x]) && $x && isset($last_where) && isset($curAndOrCol)) {
882 $qry_where .= ' ' . strtoupper($curAndOrCol[$last_where]) . ' ';
884 if (!empty($curField[$x]) && !empty($curCriteria[$x])) {
885 $qry_where .= '(' . $curField[$x] . ' ' . $curCriteria[$x] . ')';
886 $last_where = $x;
887 $criteria_cnt++;
889 } // end for
890 if ($criteria_cnt > 1) {
891 $qry_where = '(' . $qry_where . ')';
893 // OR rows ${'cur' . $or}[$x]
894 if (! isset($curAndOrRow)) {
895 $curAndOrRow = array();
897 for ($y = 0; $y <= $row; $y++) {
898 $criteria_cnt = 0;
899 $qry_orwhere = '';
900 $last_orwhere = '';
901 for ($x = 0; $x < $col; $x++) {
902 if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x]) && $x) {
903 $qry_orwhere .= ' ' . strtoupper($curAndOrCol[$last_orwhere]) . ' ';
905 if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x])) {
906 $qry_orwhere .= '(' . $curField[$x]
907 . ' '
908 . ${'curOr' . $y}[$x]
909 . ')';
910 $last_orwhere = $x;
911 $criteria_cnt++;
913 } // end for
914 if ($criteria_cnt > 1) {
915 $qry_orwhere = '(' . $qry_orwhere . ')';
917 if (!empty($qry_orwhere)) {
918 $qry_where .= "\n"
919 . strtoupper(isset($curAndOrRow[$y]) ? $curAndOrRow[$y] . ' ' : '')
920 . $qry_orwhere;
921 } // end if
922 } // end for
924 if (!empty($qry_where) && $qry_where != '()') {
925 echo 'WHERE ' . htmlspecialchars($qry_where) . "\n";
926 } // end if
929 // 4. ORDER BY
930 $last_orderby = 0;
931 if (! isset($qry_orderby)) {
932 $qry_orderby = '';
934 for ($x = 0; $x < $col; $x++) {
935 if ($last_orderby && $x && !empty($curField[$x]) && !empty($curSort[$x])) {
936 $qry_orderby .= ', ';
938 if (!empty($curField[$x]) && !empty($curSort[$x])) {
939 // if they have chosen all fields using the * selector,
940 // then sorting is not available
941 // Fix for Bug #570698
942 if (substr($curField[$x], -2) != '.*') {
943 $qry_orderby .= $curField[$x] . ' ' . $curSort[$x];
944 $last_orderby = 1;
947 } // end for
948 if (!empty($qry_orderby)) {
949 echo 'ORDER BY ' . htmlspecialchars($qry_orderby) . "\n";
952 </textarea>
953 </fieldset>
954 <fieldset class="tblFooters">
955 <input type="submit" name="submit_sql" value="<?php echo __('Submit Query'); ?>" />
956 </fieldset>
957 </td>
958 </tr>
959 </table>
960 </form>
961 <?php
963 * Displays the footer
965 require './libraries/footer.inc.php';