Translation update done using Pootle.
[phpmyadmin/crack.git] / db_qbe.php
blob95e72d894f18c21d2ea69ba529acd47eea29f175
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'); ?>
184 <form action="<?php echo $tab_designer['link'] ?>" method="post">
185 <input type="submit" style="background-color:#FFD; border-width:0; color:#00F;
186 font-size: 15px;cursor: pointer" name="query" onmouseover="this.style.color='#F00';"
187 value="<?php echo __('visual builder'); ?>" onmouseout="this.style.color='#00F'"/></span>
188 </form>
189 </div>
190 <?php
193 <form action="db_qbe.php" method="post">
194 <fieldset>
195 <table class="data" style="width: 100%;">
196 <tr class="odd noclick">
197 <th><?php echo __('Column'); ?>:</th>
198 <?php
199 $z = 0;
200 for ($x = 0; $x < $col; $x++) {
201 if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {
202 showColumnSelectCell($fld, $z);
203 $z++;
206 if (! empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
207 continue;
210 $selected = '';
211 if (isset($Field[$x])) {
212 $selected = $Field[$x];
213 $curField[$z] = $Field[$x];
215 showColumnSelectCell($fld, $z, $selected);
216 $z++;
217 } // end for
219 </tr>
221 <!-- Sort row -->
222 <tr class="even noclick">
223 <th><?php echo __('Sort'); ?>:</th>
224 <?php
225 $z = 0;
226 for ($x = 0; $x < $col; $x++) {
227 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
229 <td align="center">
230 <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
231 <option value="">&nbsp;</option>
232 <option value="ASC"><?php echo __('Ascending'); ?></option>
233 <option value="DESC"><?php echo __('Descending'); ?></option>
234 </select>
235 </td>
236 <?php
237 $z++;
238 } // end if
239 echo "\n";
241 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
242 continue;
245 <td align="center">
246 <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
247 <option value="">&nbsp;</option>
248 <?php
249 echo "\n";
251 // If they have chosen all fields using the * selector,
252 // then sorting is not available
253 // Fix for Bug #570698
254 if (isset($Sort[$x]) && isset($Field[$x])
255 && substr($Field[$x], -2) == '.*') {
256 $Sort[$x] = '';
257 } //end if
259 if (isset($Sort[$x]) && $Sort[$x] == 'ASC') {
260 $curSort[$z] = $Sort[$x];
261 $sel = ' selected="selected"';
262 } else {
263 $sel = '';
264 } // end if
265 echo ' ';
266 echo '<option value="ASC"' . $sel . '>' . __('Ascending') . '</option>' . "\n";
267 if (isset($Sort[$x]) && $Sort[$x] == 'DESC') {
268 $curSort[$z] = $Sort[$x];
269 $sel = ' selected="selected"';
270 } else {
271 $sel = '';
272 } // end if
273 echo ' ';
274 echo '<option value="DESC"' . $sel . '>' . __('Descending') . '</option>' . "\n";
276 </select>
277 </td>
278 <?php
279 $z++;
280 echo "\n";
281 } // end for
283 </tr>
285 <!-- Show row -->
286 <tr class="odd noclick">
287 <th><?php echo __('Show'); ?>:</th>
288 <?php
289 $z = 0;
290 for ($x = 0; $x < $col; $x++) {
291 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
293 <td align="center">
294 <input type="checkbox" name="Show[<?php echo $z; ?>]" />
295 </td>
296 <?php
297 $z++;
298 } // end if
299 echo "\n";
301 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
302 continue;
304 if (isset($Show[$x])) {
305 $checked = ' checked="checked"';
306 $curShow[$z] = $Show[$x];
307 } else {
308 $checked = '';
311 <td align="center">
312 <input type="checkbox" name="Show[<?php echo $z; ?>]"<?php echo $checked; ?> />
313 </td>
314 <?php
315 $z++;
316 echo "\n";
317 } // end for
319 </tr>
321 <!-- Criteria row -->
322 <tr class="even noclick">
323 <th><?php echo __('Criteria'); ?>:</th>
324 <?php
325 $z = 0;
326 for ($x = 0; $x < $col; $x++) {
327 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
329 <td align="center">
330 <input type="text" name="criteria[<?php echo $z; ?>]" value="" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
331 </td>
332 <?php
333 $z++;
334 } // end if
335 echo "\n";
337 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
338 continue;
340 if (isset($criteria[$x])) {
341 $tmp_criteria = $criteria[$x];
343 if ((empty($prev_criteria) || !isset($prev_criteria[$x]))
344 || $prev_criteria[$x] != htmlspecialchars($tmp_criteria)) {
345 $curCriteria[$z] = $tmp_criteria;
346 } else {
347 $curCriteria[$z] = $prev_criteria[$x];
350 <td align="center">
351 <input type="hidden" name="prev_criteria[<?php echo $z; ?>]" value="<?php echo htmlspecialchars($curCriteria[$z]); ?>" />
352 <input type="text" name="criteria[<?php echo $z; ?>]" value="<?php echo htmlspecialchars($tmp_criteria); ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
353 </td>
354 <?php
355 $z++;
356 echo "\n";
357 } // end for
359 </tr>
361 <!-- And/Or columns and rows -->
362 <?php
363 $w = 0;
364 $odd_row = true;
365 for ($y = 0; $y <= $row; $y++) {
366 if (isset($ins_row[$y]) && $ins_row[$y] == 'on') {
367 $chk['or'] = ' checked="checked"';
368 $chk['and'] = '';
370 <tr class="<?php echo $odd_row ? 'odd' : 'even'; ?> noclick">
371 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
372 <!-- Row controls -->
373 <table cellpadding="0" cellspacing="0" border="0">
374 <tr>
375 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
376 <small><?php echo __('Ins'); ?>:</small>
377 <input type="checkbox" name="ins_row[<?php echo $w; ?>]" />
378 </td>
379 <td align="<?php echo $cell_align_right; ?>">
380 <strong><?php echo __('And'); ?>:</strong>
381 </td>
382 <td>
383 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
384 &nbsp;
385 </td>
386 </tr>
387 <tr>
388 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
389 <small><?php echo __('Del'); ?>:</small>
390 <input type="checkbox" name="del_row[<?php echo $w; ?>]" />
391 </td>
392 <td align="<?php echo $cell_align_right; ?>">
393 <strong><?php echo __('Or'); ?>:</strong>
394 </td>
395 <td>
396 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
397 &nbsp;
398 </td>
399 </tr>
400 </table>
401 </td>
402 <?php
403 $z = 0;
404 for ($x = 0; $x < $col; $x++) {
405 if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {
406 echo "\n";
407 $or = 'Or' . $w . '[' . $z . ']';
409 <td align="center">
410 <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
411 </td>
412 <?php
413 $z++;
414 } // end if
415 if (isset($del_col[$x]) && $del_col[$x] == 'on') {
416 continue;
419 echo "\n";
420 $or = 'Or' . $w . '[' . $z . ']';
422 <td align="center">
423 <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
424 </td>
425 <?php
426 $z++;
427 } // end for
428 $w++;
429 echo "\n";
431 </tr>
432 <?php
433 $odd_row =! $odd_row;
434 } // end if
436 if (isset($del_row[$y]) && $del_row[$y] == 'on') {
437 continue;
440 if (isset($and_or_row[$y])) {
441 $curAndOrRow[$w] = $and_or_row[$y];
443 if (isset($and_or_row[$y]) && $and_or_row[$y] == 'and') {
444 $chk['and'] = ' checked="checked"';
445 $chk['or'] = '';
446 } else {
447 $chk['or'] = ' checked="checked"';
448 $chk['and'] = '';
450 echo "\n";
452 <tr class="<?php echo $odd_row ? 'odd' : 'even'; ?> noclick">
453 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
454 <!-- Row controls -->
455 <table border="0" cellpadding="0" cellspacing="0">
456 <tr>
457 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
458 <small><?php echo __('Ins'); ?>:</small>
459 <input type="checkbox" name="ins_row[<?php echo $w; ?>]" />
460 </td>
461 <td align="<?php echo $cell_align_right; ?>">
462 <strong><?php echo __('And'); ?>:</strong>
463 </td>
464 <td>
465 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
466 </td>
467 </tr>
468 <tr>
469 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
470 <small><?php echo __('Del'); ?>:</small>
471 <input type="checkbox" name="del_row[<?php echo $w; ?>]" />
472 </td>
473 <td align="<?php echo $cell_align_right; ?>">
474 <strong><?php echo __('Or'); ?>:</strong>
475 </td>
476 <td>
477 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
478 </td>
479 </tr>
480 </table>
481 </td>
482 <?php
483 $z = 0;
484 for ($x = 0; $x < $col; $x++) {
485 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
486 echo "\n";
487 $or = 'Or' . $w . '[' . $z . ']';
489 <td align="center">
490 <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
491 </td>
492 <?php
493 $z++;
494 } // end if
495 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
496 continue;
499 echo "\n";
500 $or = 'Or' . $y;
501 if (!isset(${$or})) {
502 ${$or} = '';
504 if (!empty(${$or}) && isset(${$or}[$x])) {
505 $tmp_or = ${$or}[$x];
506 } else {
507 $tmp_or = '';
510 <td align="center">
511 <input type="text" name="Or<?php echo $w . '[' . $z . ']'; ?>" value="<?php echo htmlspecialchars($tmp_or); ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
512 </td>
513 <?php
514 if (!empty(${$or}) && isset(${$or}[$x])) {
515 ${'cur' . $or}[$z] = ${$or}[$x];
517 $z++;
518 } // end for
519 $w++;
520 echo "\n";
522 </tr>
523 <?php
524 echo "\n";
525 $odd_row =! $odd_row;
526 } // end for
528 <!-- Modify columns -->
529 <tr class="even noclick">
530 <th><?php echo __('Modify'); ?>:</th>
531 <?php
532 $z = 0;
533 for ($x = 0; $x < $col; $x++) {
534 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
535 $curAndOrCol[$z] = $and_or_col[$y];
536 if ($and_or_col[$z] == 'or') {
537 $chk['or'] = ' checked="checked"';
538 $chk['and'] = '';
539 } else {
540 $chk['and'] = ' checked="checked"';
541 $chk['or'] = '';
544 <td align="center">
545 <strong><?php echo __('Or'); ?>:</strong>
546 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
547 &nbsp;&nbsp;<strong><?php echo __('And'); ?>:</strong>
548 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
549 <br />
550 <?php echo __('Ins') . "\n"; ?>
551 <input type="checkbox" name="ins_col[<?php echo $z; ?>]" />
552 &nbsp;&nbsp;<?php echo __('Del') . "\n"; ?>
553 <input type="checkbox" name="del_col[<?php echo $z; ?>]" />
554 </td>
555 <?php
556 $z++;
557 } // end if
558 echo "\n";
560 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
561 continue;
564 if (isset($and_or_col[$y])) {
565 $curAndOrCol[$z] = $and_or_col[$y];
567 if (isset($and_or_col[$z]) && $and_or_col[$z] == 'or') {
568 $chk['or'] = ' checked="checked"';
569 $chk['and'] = '';
570 } else {
571 $chk['and'] = ' checked="checked"';
572 $chk['or'] = '';
575 <td align="center">
576 <strong><?php echo __('Or'); ?>:</strong>
577 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
578 &nbsp;&nbsp;<strong><?php echo __('And'); ?>:</strong>
579 <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
580 <br />
581 <?php echo __('Ins') . "\n"; ?>
582 <input type="checkbox" name="ins_col[<?php echo $z; ?>]" />
583 &nbsp;&nbsp;<?php echo __('Del') . "\n"; ?>
584 <input type="checkbox" name="del_col[<?php echo $z; ?>]" />
585 </td>
586 <?php
587 $z++;
588 echo "\n";
589 } // end for
591 </tr>
592 </table>
594 <!-- Other controls -->
595 <?php
596 $w--;
597 $url_params['db'] = $db;
598 $url_params['col_cnt'] = $z;
599 $url_params['rows'] = $w;
600 echo PMA_generate_common_hidden_inputs($url_params);
602 </fieldset>
603 <fieldset class="tblFooters">
604 <table border="0" cellpadding="2" cellspacing="1">
605 <tr>
606 <td nowrap="nowrap">
607 <?php echo __('Add/Delete criteria rows'); ?>:
608 <select size="1" name="add_row">
609 <option value="-3">-3</option>
610 <option value="-2">-2</option>
611 <option value="-1">-1</option>
612 <option value="0" selected="selected">0</option>
613 <option value="1">1</option>
614 <option value="2">2</option>
615 <option value="3">3</option>
616 </select>
617 </td>
618 <td width="10">&nbsp;</td>
619 <td nowrap="nowrap"><?php echo __('Add/Delete columns'); ?>:
620 <select size="1" name="add_col">
621 <option value="-3">-3</option>
622 <option value="-2">-2</option>
623 <option value="-1">-1</option>
624 <option value="0" selected="selected">0</option>
625 <option value="1">1</option>
626 <option value="2">2</option>
627 <option value="3">3</option>
628 </select>
629 </td>
630 <td width="10">&nbsp;</td>
631 <!-- Generates a query -->
632 <td><input type="submit" name="modify" value="<?php echo __('Update Query'); ?>" /></td>
633 </tr>
634 </table>
635 </fieldset>
637 <table>
638 <tr><td>
639 <fieldset>
640 <legend><?php echo __('Use Tables'); ?></legend>
641 <?php
642 $options = '';
643 $numTableListOptions = 0;
644 foreach ($tbl_names as $key => $val) {
645 $options .= ' ';
646 $options .= '<option value="' . htmlspecialchars($key) . '"' . $val . '>'
647 . str_replace(' ', '&nbsp;', htmlspecialchars($key)) . '</option>' . "\n";
648 $numTableListOptions++;
651 <select name="TableList[]" multiple="multiple" id="listTable"
652 size="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>">
653 <?php echo $options; ?>
654 </select>
655 </fieldset>
656 <fieldset class="tblFooters">
657 <input type="submit" name="modify" value="<?php echo __('Update Query'); ?>" />
658 </fieldset>
659 </td>
660 <td width="20">&nbsp;</td>
661 <td>
662 <fieldset>
663 <legend><?php echo sprintf(__('SQL query on database <b>%s</b>:'), PMA_getDbLink($db)); ?>
664 </legend>
665 <textarea cols="80" name="sql_query" id="textSqlquery"
666 rows="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>"
667 dir="<?php echo $text_dir; ?>">
668 <?php
669 // 1. SELECT
670 $last_select = 0;
671 if (!isset($qry_select)) {
672 $qry_select = '';
674 for ($x = 0; $x < $col; $x++) {
675 if (!empty($curField[$x]) && isset($curShow[$x]) && $curShow[$x] == 'on') {
676 if ($last_select) {
677 $qry_select .= ', ';
679 $qry_select .= $curField[$x];
680 $last_select = 1;
682 } // end for
683 if (!empty($qry_select)) {
684 echo 'SELECT ' . htmlspecialchars($qry_select) . "\n";
687 // 2. FROM
689 // Create LEFT JOINS out of Relations
690 // Code originally by Mike Beck <mike.beck@ibmiller.de>
691 // If we can use Relations we could make some left joins.
692 // First find out if relations are available in this database.
694 // First we need the really needed Tables - those in TableList might still be
695 // all Tables.
696 if (isset($Field) && count($Field) > 0) {
697 // Initialize some variables
698 $tab_all = array();
699 $col_all = array();
700 $tab_wher = array();
701 $tab_know = array();
702 $tab_left = array();
703 $col_where = array();
704 $fromclause = '';
706 // We only start this if we have fields, otherwise it would be dumb
707 foreach ($Field as $value) {
708 $parts = explode('.', $value);
709 if (!empty($parts[0]) && !empty($parts[1])) {
710 $tab_raw = $parts[0];
711 $tab = str_replace('`', '', $tab_raw);
712 $tab_all[$tab] = $tab;
714 $col_raw = $parts[1];
715 $col_all[] = $tab . '.' . str_replace('`', '', $col_raw);
717 } // end while
719 // Check 'where' clauses
720 if ($cfgRelation['relwork'] && count($tab_all) > 0) {
721 // Now we need all tables that we have in the where clause
722 $crit_cnt = count($criteria);
723 for ($x = 0; $x < $crit_cnt; $x++) {
724 $curr_tab = explode('.', $Field[$x]);
725 if (!empty($curr_tab[0]) && !empty($curr_tab[1])) {
726 $tab_raw = $curr_tab[0];
727 $tab = str_replace('`', '', $tab_raw);
729 $col_raw = $curr_tab[1];
730 $col1 = str_replace('`', '', $col_raw);
731 $col1 = $tab . '.' . $col1;
732 // Now we know that our array has the same numbers as $criteria
733 // we can check which of our columns has a where clause
734 if (!empty($criteria[$x])) {
735 if (substr($criteria[$x], 0, 1) == '=' || stristr($criteria[$x], 'is')) {
736 $col_where[$col] = $col1;
737 $tab_wher[$tab] = $tab;
739 } // end if
740 } // end if
741 } // end for
743 // Cleans temp vars w/o further use
744 unset($tab_raw);
745 unset($col_raw);
746 unset($col1);
748 if (count($tab_wher) == 1) {
749 // If there is exactly one column that has a decent where-clause
750 // we will just use this
751 $master = key($tab_wher);
752 } else {
753 // Now let's find out which of the tables has an index
754 // (When the control user is the same as the normal user
755 // because he is using one of his databases as pmadb,
756 // the last db selected is not always the one where we need to work)
757 PMA_DBI_select_db($db);
759 foreach ($tab_all as $tab) {
760 $ind_rs = PMA_DBI_query('SHOW INDEX FROM ' . PMA_backquote($tab) . ';');
761 while ($ind = PMA_DBI_fetch_assoc($ind_rs)) {
762 $col1 = $tab . '.' . $ind['Column_name'];
763 if (isset($col_all[$col1])) {
764 if ($ind['non_unique'] == 0) {
765 if (isset($col_where[$col1])) {
766 $col_unique[$col1] = 'Y';
767 } else {
768 $col_unique[$col1] = 'N';
770 } else {
771 if (isset($col_where[$col1])) {
772 $col_index[$col1] = 'Y';
773 } else {
774 $col_index[$col1] = 'N';
778 } // end while (each col of tab)
779 } // end while (each tab)
780 // now we want to find the best.
781 if (isset($col_unique) && count($col_unique) > 0) {
782 $col_cand = $col_unique;
783 $needsort = 1;
784 } elseif (isset($col_index) && count($col_index) > 0) {
785 $col_cand = $col_index;
786 $needsort = 1;
787 } elseif (isset($col_where) && count($col_where) > 0) {
788 $col_cand = $tab_wher;
789 $needsort = 0;
790 } else {
791 $col_cand = $tab_all;
792 $needsort = 0;
795 // If we came up with $col_unique (very good) or $col_index (still
796 // good) as $col_cand we want to check if we have any 'Y' there
797 // (that would mean that they were also found in the whereclauses
798 // which would be great). if yes, we take only those
799 if ($needsort == 1) {
800 foreach ($col_cand as $col => $is_where) {
801 $tab = explode('.', $col);
802 $tab = $tab[0];
803 if ($is_where == 'Y') {
804 $vg[$col] = $tab;
805 } else {
806 $sg[$col] = $tab;
809 if (isset($vg)) {
810 $col_cand = $vg;
811 // Candidates restricted in index+where
812 } else {
813 $col_cand = $sg;
814 // None of the candidates where in a where-clause
818 // If our array of candidates has more than one member we'll just
819 // find the smallest table.
820 // Of course the actual query would be faster if we check for
821 // the Criteria which gives the smallest result set in its table,
822 // but it would take too much time to check this
823 if (count($col_cand) > 1) {
824 // Of course we only want to check each table once
825 $checked_tables = $col_cand;
826 foreach ($col_cand as $tab) {
827 if ($checked_tables[$tab] != 1) {
828 $tsize[$tab] = PMA_Table::countRecords($db, $tab, false);
829 $checked_tables[$tab] = 1;
831 $csize[$tab] = $tsize[$tab];
833 asort($csize);
834 reset($csize);
835 $master = key($csize); // Smallest
836 } else {
837 reset($col_cand);
838 $master = current($col_cand); // Only one single candidate
840 } // end if (exactly one where clause)
842 $tab_left = $tab_all;
843 unset($tab_left[$master]);
844 $tab_know[$master] = $master;
846 $run = 0;
847 $emerg = '';
848 while (count($tab_left) > 0) {
849 if ($run % 2 == 0) {
850 PMA_getRelatives('master');
851 } else {
852 PMA_getRelatives('foreign');
854 $run++;
855 if ($run > 5) {
857 foreach ($tab_left as $tab) {
858 $emerg .= ', ' . PMA_backquote($tab);
859 unset($tab_left[$tab]);
862 } // end while
863 $qry_from = PMA_backquote($master) . $emerg . $fromclause;
864 } // end if ($cfgRelation['relwork'] && count($tab_all) > 0)
866 } // end count($Field) > 0
868 // In case relations are not defined, just generate the FROM clause
869 // from the list of tables, however we don't generate any JOIN
871 if (empty($qry_from) && isset($tab_all)) {
872 $qry_from = implode(', ', $tab_all);
874 // Now let's see what we got
875 if (!empty($qry_from)) {
876 echo 'FROM ' . htmlspecialchars($qry_from) . "\n";
879 // 3. WHERE
880 $qry_where = '';
881 $criteria_cnt = 0;
882 for ($x = 0; $x < $col; $x++) {
883 if (!empty($curField[$x]) && !empty($curCriteria[$x]) && $x && isset($last_where) && isset($curAndOrCol)) {
884 $qry_where .= ' ' . strtoupper($curAndOrCol[$last_where]) . ' ';
886 if (!empty($curField[$x]) && !empty($curCriteria[$x])) {
887 $qry_where .= '(' . $curField[$x] . ' ' . $curCriteria[$x] . ')';
888 $last_where = $x;
889 $criteria_cnt++;
891 } // end for
892 if ($criteria_cnt > 1) {
893 $qry_where = '(' . $qry_where . ')';
895 // OR rows ${'cur' . $or}[$x]
896 if (!isset($curAndOrRow)) {
897 $curAndOrRow = array();
899 for ($y = 0; $y <= $row; $y++) {
900 $criteria_cnt = 0;
901 $qry_orwhere = '';
902 $last_orwhere = '';
903 for ($x = 0; $x < $col; $x++) {
904 if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x]) && $x) {
905 $qry_orwhere .= ' ' . strtoupper($curAndOrCol[$last_orwhere]) . ' ';
907 if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x])) {
908 $qry_orwhere .= '(' . $curField[$x]
909 . ' '
910 . ${'curOr' . $y}[$x]
911 . ')';
912 $last_orwhere = $x;
913 $criteria_cnt++;
915 } // end for
916 if ($criteria_cnt > 1) {
917 $qry_orwhere = '(' . $qry_orwhere . ')';
919 if (!empty($qry_orwhere)) {
920 $qry_where .= "\n"
921 . strtoupper(isset($curAndOrRow[$y]) ? $curAndOrRow[$y] . ' ' : '')
922 . $qry_orwhere;
923 } // end if
924 } // end for
926 if (!empty($qry_where) && $qry_where != '()') {
927 echo 'WHERE ' . htmlspecialchars($qry_where) . "\n";
928 } // end if
931 // 4. ORDER BY
932 $last_orderby = 0;
933 if (!isset($qry_orderby)) {
934 $qry_orderby = '';
936 for ($x = 0; $x < $col; $x++) {
937 if ($last_orderby && $x && !empty($curField[$x]) && !empty($curSort[$x])) {
938 $qry_orderby .= ', ';
940 if (!empty($curField[$x]) && !empty($curSort[$x])) {
941 // if they have chosen all fields using the * selector,
942 // then sorting is not available
943 // Fix for Bug #570698
944 if (substr($curField[$x], -2) != '.*') {
945 $qry_orderby .= $curField[$x] . ' ' . $curSort[$x];
946 $last_orderby = 1;
949 } // end for
950 if (!empty($qry_orderby)) {
951 echo 'ORDER BY ' . htmlspecialchars($qry_orderby) . "\n";
954 </textarea>
955 </fieldset>
956 <fieldset class="tblFooters">
957 <input type="submit" name="submit_sql" value="<?php echo __('Submit Query'); ?>" />
958 </fieldset>
959 </td>
960 </tr>
961 </table>
962 </form>
963 <?php
965 * Displays the footer
967 require './libraries/footer.inc.php';