Merge branch 'QA_3_3'
[phpmyadmin-regexreplace.git] / db_qbe.php
blob29b32f467cb791ce7b735eade93a7ff9c309fc6d
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 -> execute it, else display the headers
23 if (isset($_REQUEST['submit_sql']) && ! empty($sql_query)) {
24 $goto = 'db_sql.php';
25 $message_to_show = htmlspecialchars(__('Your SQL query has been executed successfully'));
26 require './sql.php';
27 exit;
28 } else {
29 $sub_part = '_qbe';
30 require './libraries/db_common.inc.php';
31 $url_query .= '&amp;goto=db_qbe.php';
32 $url_params['goto'] = 'db_qbe.php';
33 require './libraries/db_info.inc.php';
36 if (isset($_REQUEST['submit_sql'])
37 && ! preg_match('@^SELECT@i', $sql_query)) {
38 PMA_Message::warning(__('You have to choose at least one column to display'))->display();
42 /**
43 * Initialize some variables
45 $col_cnt = PMA_ifSetOr($_REQUEST['col_cnt'], 3, 'numeric');
46 $add_col = PMA_ifSetOr($_REQUEST['add_col'], 0, 'numeric');
47 $add_row = PMA_ifSetOr($_REQUEST['add_row'], 0, 'numeric');
49 $rows = PMA_ifSetOr($_REQUEST['rows'], 0, 'numeric');
50 $ins_col = PMA_ifSetOr($_REQUEST['ins_col'], null, 'array');
51 $del_col = PMA_ifSetOr($_REQUEST['del_col'], null, 'array');
53 $prev_criteria = isset($_REQUEST['prev_criteria'])
54 ? $_REQUEST['prev_criteria']
55 : array();
56 $criteria = isset($_REQUEST['criteria'])
57 ? $_REQUEST['criteria']
58 : array_fill(0, $col_cnt, '');
60 $ins_row = isset($_REQUEST['ins_row'])
61 ? $_REQUEST['ins_row']
62 : array_fill(0, $col_cnt, '');
63 $del_row = isset($_REQUEST['del_row'])
64 ? $_REQUEST['del_row']
65 : array_fill(0, $col_cnt, '');
66 $and_or_row = isset($_REQUEST['and_or_row'])
67 ? $_REQUEST['and_or_row']
68 : array_fill(0, $col_cnt, '');
69 $and_or_col = isset($_REQUEST['and_or_col'])
70 ? $_REQUEST['and_or_col']
71 : array_fill(0, $col_cnt, '');
73 // minimum width
74 $form_column_width = 12;
75 $col = max($col_cnt + $add_col, 0);
76 $row = max($rows + $add_row, 0);
79 // The tables list sent by a previously submitted form
80 if (PMA_isValid($_REQUEST['TableList'], 'array')) {
81 foreach ($_REQUEST['TableList'] as $each_table) {
82 $tbl_names[$each_table] = ' selected="selected"';
84 } // end if
87 // this was a work in progress, deactivated for now
88 //$columns = PMA_DBI_get_columns_full($GLOBALS['db']);
89 //$tables = PMA_DBI_get_columns_full($GLOBALS['db']);
92 /**
93 * Prepares the form
95 $tbl_result = PMA_DBI_query('SHOW TABLES FROM ' . PMA_backquote($db) . ';', null, PMA_DBI_QUERY_STORE);
96 $tbl_result_cnt = PMA_DBI_num_rows($tbl_result);
97 if (0 == $tbl_result_cnt) {
98 PMA_Message::error(__('No tables found in database.'))->display();
99 require './libraries/footer.inc.php';
100 exit;
103 // The tables list gets from MySQL
104 while (list($tbl) = PMA_DBI_fetch_row($tbl_result)) {
105 $fld_results = PMA_DBI_get_fields($db, $tbl);
107 if (empty($tbl_names[$tbl]) && !empty($_REQUEST['TableList'])) {
108 $tbl_names[$tbl] = '';
109 } else {
110 $tbl_names[$tbl] = ' selected="selected"';
111 } // end if
113 // The fields list per selected tables
114 if ($tbl_names[$tbl] == ' selected="selected"') {
115 $each_table = PMA_backquote($tbl);
116 $fld[] = $each_table . '.*';
117 foreach ($fld_results as $each_field) {
118 $each_field = $each_table . '.' . PMA_backquote($each_field['Field']);
119 $fld[] = $each_field;
121 // increase the width if necessary
122 $form_column_width = max(strlen($each_field), $form_column_width);
123 } // end foreach
124 } // end if
125 } // end while
126 PMA_DBI_free_result($tbl_result);
128 // largest width found
129 $realwidth = $form_column_width . 'ex';
133 * Displays the Query by example form
137 * Enter description here...
139 * @param array $columns
140 * @param integer $column_number
141 * @param string $selected
143 function showColumnSelectCell($columns, $column_number, $selected = '')
146 <td align="center">
147 <select name="Field[<?php echo $column_number; ?>]" size="1">
148 <option value="">&nbsp;</option>
149 <?php
150 foreach ($columns as $column) {
151 if ($column === $selected) {
152 $sel = ' selected="selected"';
153 } else {
154 $sel = '';
156 echo '<option value="' . htmlspecialchars($column) . '"' . $sel . '>'
157 . str_replace(' ', '&nbsp;', htmlspecialchars($column)) . '</option>' . "\n";
160 </select>
161 </td>
162 <?php
166 <?php if (!empty($tab_designer['link'])) {
167 $tab_designer['link'] = htmlentities($tab_designer['link']);
168 $tab_designer['link'] = $tab_designer['link'] . PMA_generate_common_url($url_params);
169 if (! empty($tab_designer['args'])) {
170 foreach ($tab_designer['args'] as $param => $value) {
171 $tab_designer['link'] .= PMA_get_arg_separator('html') . urlencode($param) . '='
172 . urlencode($value);
176 if (! empty($tab['fragment'])) {
177 $tab['link'] .= $tab['fragment'];
179 if (isset($tab_designer['link'])) {
181 <div id="visual_builder_anchor" class="notice hide">
182 <span id="footnote_1">
183 <?php echo __('Switch to') . ' <a href="' . $tab_designer['link'] . PMA_get_arg_separator('html') . 'query=1">' . __('visual builder') . '</a>'; ?>
184 </span>
185 </div>
186 <?php
189 <form action="db_qbe.php" method="post">
190 <fieldset>
191 <table class="data" style="width: 100%;">
192 <tr class="odd noclick">
193 <th><?php echo __('Column'); ?>:</th>
194 <?php
195 $z = 0;
196 for ($x = 0; $x < $col; $x++) {
197 if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {
198 showColumnSelectCell($fld, $z);
199 $z++;
202 if (! empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
203 continue;
206 $selected = '';
207 if (isset($Field[$x])) {
208 $selected = $Field[$x];
209 $curField[$z] = $Field[$x];
211 showColumnSelectCell($fld, $z, $selected);
212 $z++;
213 } // end for
215 </tr>
217 <!-- Sort row -->
218 <tr class="even noclick">
219 <th><?php echo __('Sort'); ?>:</th>
220 <?php
221 $z = 0;
222 for ($x = 0; $x < $col; $x++) {
223 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
225 <td align="center">
226 <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
227 <option value="">&nbsp;</option>
228 <option value="ASC"><?php echo __('Ascending'); ?></option>
229 <option value="DESC"><?php echo __('Descending'); ?></option>
230 </select>
231 </td>
232 <?php
233 $z++;
234 } // end if
235 echo "\n";
237 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
238 continue;
241 <td align="center">
242 <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
243 <option value="">&nbsp;</option>
244 <?php
245 echo "\n";
247 // If they have chosen all fields using the * selector,
248 // then sorting is not available
249 // Fix for Bug #570698
250 if (isset($Sort[$x]) && isset($Field[$x])
251 && substr($Field[$x], -2) == '.*') {
252 $Sort[$x] = '';
253 } //end if
255 if (isset($Sort[$x]) && $Sort[$x] == 'ASC') {
256 $curSort[$z] = $Sort[$x];
257 $sel = ' selected="selected"';
258 } else {
259 $sel = '';
260 } // end if
261 echo ' ';
262 echo '<option value="ASC"' . $sel . '>' . __('Ascending') . '</option>' . "\n";
263 if (isset($Sort[$x]) && $Sort[$x] == 'DESC') {
264 $curSort[$z] = $Sort[$x];
265 $sel = ' selected="selected"';
266 } else {
267 $sel = '';
268 } // end if
269 echo ' ';
270 echo '<option value="DESC"' . $sel . '>' . __('Descending') . '</option>' . "\n";
272 </select>
273 </td>
274 <?php
275 $z++;
276 echo "\n";
277 } // end for
279 </tr>
281 <!-- Show row -->
282 <tr class="odd noclick">
283 <th><?php echo __('Show'); ?>:</th>
284 <?php
285 $z = 0;
286 for ($x = 0; $x < $col; $x++) {
287 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
289 <td align="center">
290 <input type="checkbox" name="Show[<?php echo $z; ?>]" />
291 </td>
292 <?php
293 $z++;
294 } // end if
295 echo "\n";
297 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
298 continue;
300 if (isset($Show[$x])) {
301 $checked = ' checked="checked"';
302 $curShow[$z] = $Show[$x];
303 } else {
304 $checked = '';
307 <td align="center">
308 <input type="checkbox" name="Show[<?php echo $z; ?>]"<?php echo $checked; ?> />
309 </td>
310 <?php
311 $z++;
312 echo "\n";
313 } // end for
315 </tr>
317 <!-- Criteria row -->
318 <tr class="even noclick">
319 <th><?php echo __('Criteria'); ?>:</th>
320 <?php
321 $z = 0;
322 for ($x = 0; $x < $col; $x++) {
323 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
325 <td align="center">
326 <input type="text" name="criteria[<?php echo $z; ?>]" value="" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
327 </td>
328 <?php
329 $z++;
330 } // end if
331 echo "\n";
333 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
334 continue;
336 if (isset($criteria[$x])) {
337 $tmp_criteria = $criteria[$x];
339 if ((empty($prev_criteria) || !isset($prev_criteria[$x]))
340 || $prev_criteria[$x] != htmlspecialchars($tmp_criteria)) {
341 $curCriteria[$z] = $tmp_criteria;
342 } else {
343 $curCriteria[$z] = $prev_criteria[$x];
346 <td align="center">
347 <input type="hidden" name="prev_criteria[<?php echo $z; ?>]" value="<?php echo htmlspecialchars($curCriteria[$z]); ?>" />
348 <input type="text" name="criteria[<?php echo $z; ?>]" value="<?php echo htmlspecialchars($tmp_criteria); ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
349 </td>
350 <?php
351 $z++;
352 echo "\n";
353 } // end for
355 </tr>
357 <!-- And/Or columns and rows -->
358 <?php
359 $w = 0;
360 $odd_row = true;
361 for ($y = 0; $y <= $row; $y++) {
362 if (isset($ins_row[$y]) && $ins_row[$y] == 'on') {
363 $chk['or'] = ' checked="checked"';
364 $chk['and'] = '';
366 <tr class="<?php echo $odd_row ? 'odd' : 'even'; ?> noclick">
367 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
368 <!-- Row controls -->
369 <table cellpadding="0" cellspacing="0" border="0">
370 <tr>
371 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
372 <small><?php echo __('Ins'); ?>:</small>
373 <input type="checkbox" name="ins_row[<?php echo $w; ?>]" />
374 </td>
375 <td align="<?php echo $cell_align_right; ?>">
376 <strong><?php echo __('And'); ?>:</strong>
377 </td>
378 <td>
379 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
380 &nbsp;
381 </td>
382 </tr>
383 <tr>
384 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
385 <small><?php echo __('Del'); ?>:</small>
386 <input type="checkbox" name="del_row[<?php echo $w; ?>]" />
387 </td>
388 <td align="<?php echo $cell_align_right; ?>">
389 <strong><?php echo __('Or'); ?>:</strong>
390 </td>
391 <td>
392 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
393 &nbsp;
394 </td>
395 </tr>
396 </table>
397 </td>
398 <?php
399 $z = 0;
400 for ($x = 0; $x < $col; $x++) {
401 if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {
402 echo "\n";
403 $or = 'Or' . $w . '[' . $z . ']';
405 <td align="center">
406 <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
407 </td>
408 <?php
409 $z++;
410 } // end if
411 if (isset($del_col[$x]) && $del_col[$x] == 'on') {
412 continue;
415 echo "\n";
416 $or = 'Or' . $w . '[' . $z . ']';
418 <td align="center">
419 <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
420 </td>
421 <?php
422 $z++;
423 } // end for
424 $w++;
425 echo "\n";
427 </tr>
428 <?php
429 $odd_row =! $odd_row;
430 } // end if
432 if (isset($del_row[$y]) && $del_row[$y] == 'on') {
433 continue;
436 if (isset($and_or_row[$y])) {
437 $curAndOrRow[$w] = $and_or_row[$y];
439 if (isset($and_or_row[$y]) && $and_or_row[$y] == 'and') {
440 $chk['and'] = ' checked="checked"';
441 $chk['or'] = '';
442 } else {
443 $chk['or'] = ' checked="checked"';
444 $chk['and'] = '';
446 echo "\n";
448 <tr class="<?php echo $odd_row ? 'odd' : 'even'; ?> noclick">
449 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
450 <!-- Row controls -->
451 <table border="0" cellpadding="0" cellspacing="0">
452 <tr>
453 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
454 <small><?php echo __('Ins'); ?>:</small>
455 <input type="checkbox" name="ins_row[<?php echo $w; ?>]" />
456 </td>
457 <td align="<?php echo $cell_align_right; ?>">
458 <strong><?php echo __('And'); ?>:</strong>
459 </td>
460 <td>
461 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
462 </td>
463 </tr>
464 <tr>
465 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
466 <small><?php echo __('Del'); ?>:</small>
467 <input type="checkbox" name="del_row[<?php echo $w; ?>]" />
468 </td>
469 <td align="<?php echo $cell_align_right; ?>">
470 <strong><?php echo __('Or'); ?>:</strong>
471 </td>
472 <td>
473 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
474 </td>
475 </tr>
476 </table>
477 </td>
478 <?php
479 $z = 0;
480 for ($x = 0; $x < $col; $x++) {
481 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
482 echo "\n";
483 $or = 'Or' . $w . '[' . $z . ']';
485 <td align="center">
486 <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
487 </td>
488 <?php
489 $z++;
490 } // end if
491 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
492 continue;
495 echo "\n";
496 $or = 'Or' . $y;
497 if (!isset(${$or})) {
498 ${$or} = '';
500 if (!empty(${$or}) && isset(${$or}[$x])) {
501 $tmp_or = ${$or}[$x];
502 } else {
503 $tmp_or = '';
506 <td align="center">
507 <input type="text" name="Or<?php echo $w . '[' . $z . ']'; ?>" value="<?php echo htmlspecialchars($tmp_or); ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
508 </td>
509 <?php
510 if (!empty(${$or}) && isset(${$or}[$x])) {
511 ${'cur' . $or}[$z] = ${$or}[$x];
513 $z++;
514 } // end for
515 $w++;
516 echo "\n";
518 </tr>
519 <?php
520 echo "\n";
521 $odd_row =! $odd_row;
522 } // end for
524 <!-- Modify columns -->
525 <tr class="even noclick">
526 <th><?php echo __('Modify'); ?>:</th>
527 <?php
528 $z = 0;
529 for ($x = 0; $x < $col; $x++) {
530 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
531 $curAndOrCol[$z] = $and_or_col[$y];
532 if ($and_or_col[$z] == 'or') {
533 $chk['or'] = ' checked="checked"';
534 $chk['and'] = '';
535 } else {
536 $chk['and'] = ' checked="checked"';
537 $chk['or'] = '';
540 <td align="center">
541 <strong><?php echo __('Or'); ?>:</strong>
542 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
543 &nbsp;&nbsp;<strong><?php echo __('And'); ?>:</strong>
544 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
545 <br />
546 <?php echo __('Ins') . "\n"; ?>
547 <input type="checkbox" name="ins_col[<?php echo $z; ?>]" />
548 &nbsp;&nbsp;<?php echo __('Del') . "\n"; ?>
549 <input type="checkbox" name="del_col[<?php echo $z; ?>]" />
550 </td>
551 <?php
552 $z++;
553 } // end if
554 echo "\n";
556 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
557 continue;
560 if (isset($and_or_col[$y])) {
561 $curAndOrCol[$z] = $and_or_col[$y];
563 if (isset($and_or_col[$z]) && $and_or_col[$z] == 'or') {
564 $chk['or'] = ' checked="checked"';
565 $chk['and'] = '';
566 } else {
567 $chk['and'] = ' checked="checked"';
568 $chk['or'] = '';
571 <td align="center">
572 <strong><?php echo __('Or'); ?>:</strong>
573 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
574 &nbsp;&nbsp;<strong><?php echo __('And'); ?>:</strong>
575 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
576 <br />
577 <?php echo __('Ins') . "\n"; ?>
578 <input type="checkbox" name="ins_col[<?php echo $z; ?>]" />
579 &nbsp;&nbsp;<?php echo __('Del') . "\n"; ?>
580 <input type="checkbox" name="del_col[<?php echo $z; ?>]" />
581 </td>
582 <?php
583 $z++;
584 echo "\n";
585 } // end for
587 </tr>
588 </table>
590 <!-- Other controls -->
591 <?php
592 $w--;
593 $url_params['db'] = $db;
594 $url_params['col_cnt'] = $z;
595 $url_params['rows'] = $w;
596 echo PMA_generate_common_hidden_inputs($url_params);
598 </fieldset>
599 <fieldset class="tblFooters">
600 <table border="0" cellpadding="2" cellspacing="1">
601 <tr>
602 <td nowrap="nowrap">
603 <?php echo __('Add/Delete criteria rows'); ?>:
604 <select size="1" name="add_row">
605 <option value="-3">-3</option>
606 <option value="-2">-2</option>
607 <option value="-1">-1</option>
608 <option value="0" selected="selected">0</option>
609 <option value="1">1</option>
610 <option value="2">2</option>
611 <option value="3">3</option>
612 </select>
613 </td>
614 <td width="10">&nbsp;</td>
615 <td nowrap="nowrap"><?php echo __('Add/Delete columns'); ?>:
616 <select size="1" name="add_col">
617 <option value="-3">-3</option>
618 <option value="-2">-2</option>
619 <option value="-1">-1</option>
620 <option value="0" selected="selected">0</option>
621 <option value="1">1</option>
622 <option value="2">2</option>
623 <option value="3">3</option>
624 </select>
625 </td>
626 <td width="10">&nbsp;</td>
627 <!-- Generates a query -->
628 <td><input type="submit" name="modify" value="<?php echo __('Update Query'); ?>" /></td>
629 </tr>
630 </table>
631 </fieldset>
633 <table>
634 <tr><td>
635 <fieldset>
636 <legend><?php echo __('Use Tables'); ?></legend>
637 <?php
638 $options = '';
639 $numTableListOptions = 0;
640 foreach ($tbl_names as $key => $val) {
641 $options .= ' ';
642 $options .= '<option value="' . htmlspecialchars($key) . '"' . $val . '>'
643 . str_replace(' ', '&nbsp;', htmlspecialchars($key)) . '</option>' . "\n";
644 $numTableListOptions++;
647 <select name="TableList[]" multiple="multiple" id="listTable"
648 size="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>">
649 <?php echo $options; ?>
650 </select>
651 </fieldset>
652 <fieldset class="tblFooters">
653 <input type="submit" name="modify" value="<?php echo __('Update Query'); ?>" />
654 </fieldset>
655 </td>
656 <td width="20">&nbsp;</td>
657 <td>
658 <fieldset>
659 <legend><?php echo sprintf(__('SQL query on database <b>%s</b>:'), PMA_getDbLink($db)); ?>
660 </legend>
661 <textarea cols="80" name="sql_query" id="textSqlquery"
662 rows="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>"
663 dir="<?php echo $text_dir; ?>">
664 <?php
665 // 1. SELECT
666 $last_select = 0;
667 if (!isset($qry_select)) {
668 $qry_select = '';
670 for ($x = 0; $x < $col; $x++) {
671 if (!empty($curField[$x]) && isset($curShow[$x]) && $curShow[$x] == 'on') {
672 if ($last_select) {
673 $qry_select .= ', ';
675 $qry_select .= $curField[$x];
676 $last_select = 1;
678 } // end for
679 if (!empty($qry_select)) {
680 echo 'SELECT ' . htmlspecialchars($qry_select) . "\n";
683 // 2. FROM
685 // Create LEFT JOINS out of Relations
686 // Code originally by Mike Beck <mike.beck@ibmiller.de>
687 // If we can use Relations we could make some left joins.
688 // First find out if relations are available in this database.
690 // First we need the really needed Tables - those in TableList might still be
691 // all Tables.
692 if (isset($Field) && count($Field) > 0) {
693 // Initialize some variables
694 $tab_all = array();
695 $col_all = array();
696 $tab_wher = array();
697 $tab_know = array();
698 $tab_left = array();
699 $col_where = array();
700 $fromclause = '';
702 // We only start this if we have fields, otherwise it would be dumb
703 foreach ($Field as $value) {
704 $parts = explode('.', $value);
705 if (!empty($parts[0]) && !empty($parts[1])) {
706 $tab_raw = $parts[0];
707 $tab = str_replace('`', '', $tab_raw);
708 $tab_all[$tab] = $tab;
710 $col_raw = $parts[1];
711 $col_all[] = $tab . '.' . str_replace('`', '', $col_raw);
713 } // end while
715 // Check 'where' clauses
716 if ($cfgRelation['relwork'] && count($tab_all) > 0) {
717 // Now we need all tables that we have in the where clause
718 $crit_cnt = count($criteria);
719 for ($x = 0; $x < $crit_cnt; $x++) {
720 $curr_tab = explode('.', $Field[$x]);
721 if (!empty($curr_tab[0]) && !empty($curr_tab[1])) {
722 $tab_raw = $curr_tab[0];
723 $tab = str_replace('`', '', $tab_raw);
725 $col_raw = $curr_tab[1];
726 $col1 = str_replace('`', '', $col_raw);
727 $col1 = $tab . '.' . $col1;
728 // Now we know that our array has the same numbers as $criteria
729 // we can check which of our columns has a where clause
730 if (!empty($criteria[$x])) {
731 if (substr($criteria[$x], 0, 1) == '=' || stristr($criteria[$x], 'is')) {
732 $col_where[$col] = $col1;
733 $tab_wher[$tab] = $tab;
735 } // end if
736 } // end if
737 } // end for
739 // Cleans temp vars w/o further use
740 unset($tab_raw);
741 unset($col_raw);
742 unset($col1);
744 if (count($tab_wher) == 1) {
745 // If there is exactly one column that has a decent where-clause
746 // we will just use this
747 $master = key($tab_wher);
748 } else {
749 // Now let's find out which of the tables has an index
750 // (When the control user is the same as the normal user
751 // because he is using one of his databases as pmadb,
752 // the last db selected is not always the one where we need to work)
753 PMA_DBI_select_db($db);
755 foreach ($tab_all as $tab) {
756 $ind_rs = PMA_DBI_query('SHOW INDEX FROM ' . PMA_backquote($tab) . ';');
757 while ($ind = PMA_DBI_fetch_assoc($ind_rs)) {
758 $col1 = $tab . '.' . $ind['Column_name'];
759 if (isset($col_all[$col1])) {
760 if ($ind['non_unique'] == 0) {
761 if (isset($col_where[$col1])) {
762 $col_unique[$col1] = 'Y';
763 } else {
764 $col_unique[$col1] = 'N';
766 } else {
767 if (isset($col_where[$col1])) {
768 $col_index[$col1] = 'Y';
769 } else {
770 $col_index[$col1] = 'N';
774 } // end while (each col of tab)
775 } // end while (each tab)
776 // now we want to find the best.
777 if (isset($col_unique) && count($col_unique) > 0) {
778 $col_cand = $col_unique;
779 $needsort = 1;
780 } elseif (isset($col_index) && count($col_index) > 0) {
781 $col_cand = $col_index;
782 $needsort = 1;
783 } elseif (isset($col_where) && count($col_where) > 0) {
784 $col_cand = $tab_wher;
785 $needsort = 0;
786 } else {
787 $col_cand = $tab_all;
788 $needsort = 0;
791 // If we came up with $col_unique (very good) or $col_index (still
792 // good) as $col_cand we want to check if we have any 'Y' there
793 // (that would mean that they were also found in the whereclauses
794 // which would be great). if yes, we take only those
795 if ($needsort == 1) {
796 foreach ($col_cand as $col => $is_where) {
797 $tab = explode('.', $col);
798 $tab = $tab[0];
799 if ($is_where == 'Y') {
800 $vg[$col] = $tab;
801 } else {
802 $sg[$col] = $tab;
805 if (isset($vg)) {
806 $col_cand = $vg;
807 // Candidates restricted in index+where
808 } else {
809 $col_cand = $sg;
810 // None of the candidates where in a where-clause
814 // If our array of candidates has more than one member we'll just
815 // find the smallest table.
816 // Of course the actual query would be faster if we check for
817 // the Criteria which gives the smallest result set in its table,
818 // but it would take too much time to check this
819 if (count($col_cand) > 1) {
820 // Of course we only want to check each table once
821 $checked_tables = $col_cand;
822 foreach ($col_cand as $tab) {
823 if ($checked_tables[$tab] != 1) {
824 $tsize[$tab] = PMA_Table::countRecords($db, $tab, false);
825 $checked_tables[$tab] = 1;
827 $csize[$tab] = $tsize[$tab];
829 asort($csize);
830 reset($csize);
831 $master = key($csize); // Smallest
832 } else {
833 reset($col_cand);
834 $master = current($col_cand); // Only one single candidate
836 } // end if (exactly one where clause)
838 $tab_left = $tab_all;
839 unset($tab_left[$master]);
840 $tab_know[$master] = $master;
842 $run = 0;
843 $emerg = '';
844 while (count($tab_left) > 0) {
845 if ($run % 2 == 0) {
846 PMA_getRelatives('master');
847 } else {
848 PMA_getRelatives('foreign');
850 $run++;
851 if ($run > 5) {
853 foreach ($tab_left as $tab) {
854 $emerg .= ', ' . PMA_backquote($tab);
855 unset($tab_left[$tab]);
858 } // end while
859 $qry_from = PMA_backquote($master) . $emerg . $fromclause;
860 } // end if ($cfgRelation['relwork'] && count($tab_all) > 0)
862 } // end count($Field) > 0
864 // In case relations are not defined, just generate the FROM clause
865 // from the list of tables, however we don't generate any JOIN
867 if (empty($qry_from) && isset($tab_all)) {
868 $qry_from = implode(', ', $tab_all);
870 // Now let's see what we got
871 if (!empty($qry_from)) {
872 echo 'FROM ' . htmlspecialchars($qry_from) . "\n";
875 // 3. WHERE
876 $qry_where = '';
877 $criteria_cnt = 0;
878 for ($x = 0; $x < $col; $x++) {
879 if (!empty($curField[$x]) && !empty($curCriteria[$x]) && $x && isset($last_where) && isset($curAndOrCol)) {
880 $qry_where .= ' ' . strtoupper($curAndOrCol[$last_where]) . ' ';
882 if (!empty($curField[$x]) && !empty($curCriteria[$x])) {
883 $qry_where .= '(' . $curField[$x] . ' ' . $curCriteria[$x] . ')';
884 $last_where = $x;
885 $criteria_cnt++;
887 } // end for
888 if ($criteria_cnt > 1) {
889 $qry_where = '(' . $qry_where . ')';
891 // OR rows ${'cur' . $or}[$x]
892 if (!isset($curAndOrRow)) {
893 $curAndOrRow = array();
895 for ($y = 0; $y <= $row; $y++) {
896 $criteria_cnt = 0;
897 $qry_orwhere = '';
898 $last_orwhere = '';
899 for ($x = 0; $x < $col; $x++) {
900 if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x]) && $x) {
901 $qry_orwhere .= ' ' . strtoupper($curAndOrCol[$last_orwhere]) . ' ';
903 if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x])) {
904 $qry_orwhere .= '(' . $curField[$x]
905 . ' '
906 . ${'curOr' . $y}[$x]
907 . ')';
908 $last_orwhere = $x;
909 $criteria_cnt++;
911 } // end for
912 if ($criteria_cnt > 1) {
913 $qry_orwhere = '(' . $qry_orwhere . ')';
915 if (!empty($qry_orwhere)) {
916 $qry_where .= "\n"
917 . strtoupper(isset($curAndOrRow[$y]) ? $curAndOrRow[$y] . ' ' : '')
918 . $qry_orwhere;
919 } // end if
920 } // end for
922 if (!empty($qry_where) && $qry_where != '()') {
923 echo 'WHERE ' . htmlspecialchars($qry_where) . "\n";
924 } // end if
927 // 4. ORDER BY
928 $last_orderby = 0;
929 if (!isset($qry_orderby)) {
930 $qry_orderby = '';
932 for ($x = 0; $x < $col; $x++) {
933 if ($last_orderby && $x && !empty($curField[$x]) && !empty($curSort[$x])) {
934 $qry_orderby .= ', ';
936 if (!empty($curField[$x]) && !empty($curSort[$x])) {
937 // if they have chosen all fields using the * selector,
938 // then sorting is not available
939 // Fix for Bug #570698
940 if (substr($curField[$x], -2) != '.*') {
941 $qry_orderby .= $curField[$x] . ' ' . $curSort[$x];
942 $last_orderby = 1;
945 } // end for
946 if (!empty($qry_orderby)) {
947 echo 'ORDER BY ' . htmlspecialchars($qry_orderby) . "\n";
950 </textarea>
951 </fieldset>
952 <fieldset class="tblFooters">
953 <input type="submit" name="submit_sql" value="<?php echo __('Submit Query'); ?>" />
954 </fieldset>
955 </td>
956 </tr>
957 </table>
958 </form>
959 <?php
961 * Displays the footer
963 require './libraries/footer.inc.php';