3 // vim: expandtab sw=4 ts=4 sts=4:
6 * query by example the whole database
12 require_once './libraries/common.lib.php';
13 require_once './libraries/Table.class.php';
14 require_once './libraries/relation.lib.php';
18 * Gets the relation settings
20 $cfgRelation = PMA_getRelationsParam();
24 * A query has been submitted -> execute it, else display the headers
26 if (isset($_REQUEST['submit_sql'])
27 && preg_match('@^SELECT@i', $_REQUEST['encoded_sql_query'])) {
28 $goto = 'db_details.php';
29 $zero_rows = htmlspecialchars($GLOBALS['strSuccess']);
30 $sql_query = urldecode($_REQUEST['encoded_sql_query']);
35 require './libraries/db_details_common.inc.php';
36 $url_query .= '&goto=db_details_qbe.php';
37 $url_params['goto'] = 'db_details_qbe.php';
38 require './libraries/db_info.inc.php';
41 if (isset($_REQUEST['submit_sql'])
42 && ! preg_match('@^SELECT@i', $_REQUEST['encoded_sql_query'])) {
43 echo '<div class="warning">' . $GLOBALS['strHaveToShow'] . '</div>';
48 * Initialize some variables
50 $col_cnt = isset($_REQUEST['col_cnt']) ?
(int) $_REQUEST['col_cnt'] : 3;
51 $add_col = isset($_REQUEST['add_col']) ?
(int) $_REQUEST['add_col'] : 0;
52 $add_row = isset($_REQUEST['add_row']) ?
(int) $_REQUEST['add_row'] : 0;
54 $rows = isset($_REQUEST['rows']) ?
(int) $_REQUEST['rows'] : 0;
55 $ins_col = isset($_REQUEST['ins_col']) ?
$_REQUEST['ins_col'] : array();
56 $del_col = isset($_REQUEST['del_col']) ?
$_REQUEST['del_col'] : array();
58 $prev_criteria = isset($_REQUEST['prev_criteria'])
59 ?
$_REQUEST['prev_criteria']
61 $criteria = isset($_REQUEST['criteria'])
62 ?
$_REQUEST['criteria']
63 : array_fill(0, $col_cnt, '');
65 $ins_row = isset($_REQUEST['ins_row'])
66 ?
$_REQUEST['ins_row']
67 : array_fill(0, $col_cnt, '');
68 $del_row = isset($_REQUEST['del_row'])
69 ?
$_REQUEST['del_row']
70 : array_fill(0, $col_cnt, '');
71 $and_or_row = isset($_REQUEST['and_or_row'])
72 ?
$_REQUEST['and_or_row']
73 : array_fill(0, $col_cnt, '');
74 $and_or_col = isset($_REQUEST['and_or_col'])
75 ?
$_REQUEST['and_or_col']
76 : array_fill(0, $col_cnt, '');
79 $form_column_width = 12;
80 $col = max($col_cnt +
$add_col, 0);
81 $row = max($rows +
$add_row, 0);
84 // The tables list sent by a previously submitted form
85 if (!empty($TableList)) {
86 $cnt_table_list = count($TableList);
87 for ($x = 0; $x < $cnt_table_list; $x++
) {
88 $tbl_names[urldecode($TableList[$x])] = ' selected="selected"';
93 // this was a work in progress, deactivated for now
94 //$columns = PMA_DBI_get_columns_full($GLOBALS['db']);
95 //$tables = PMA_DBI_get_columns_full($GLOBALS['db']);
101 $tbl_result = PMA_DBI_query('SHOW TABLES FROM ' . PMA_backquote($db) . ';', null, PMA_DBI_QUERY_STORE
);
102 $tbl_result_cnt = PMA_DBI_num_rows($tbl_result);
106 // The tables list gets from MySQL
107 while ($i < $tbl_result_cnt) {
108 list($tbl) = PMA_DBI_fetch_row($tbl_result);
109 $fld_results = PMA_DBI_get_fields($db, $tbl);
110 $fld_results_cnt = ($fld_results) ?
count($fld_results) : 0;
113 if (empty($tbl_names[$tbl]) && !empty($TableList)) {
114 $tbl_names[$tbl] = '';
116 $tbl_names[$tbl] = ' selected="selected"';
119 // The fields list per selected tables
120 if ($tbl_names[$tbl] == ' selected="selected"') {
121 $fld[$k++
] = PMA_backquote($tbl) . '.*';
122 while ($j < $fld_results_cnt) {
123 $fld[$k] = PMA_convert_display_charset($fld_results[$j]['Field']);
124 $fld[$k] = PMA_backquote($tbl) . '.' . PMA_backquote($fld[$k]);
126 // increase the width if necessary
127 if (strlen($fld[$k]) > $form_column_width) {
128 $form_column_width = strlen($fld[$k]);
138 PMA_DBI_free_result($tbl_result);
140 // largest width found
141 $realwidth = $form_column_width . 'ex';
145 * Displays the Query by example form
148 function showColumnSelectCell($columns, $column_number, $selected = '')
152 <select name
="Field[<?php echo $column_number; ?>]" size
="1">
153 <option value
=""></option
>
155 foreach ($columns as $column) {
156 if ($column === $selected) {
157 $sel = ' selected="selected"';
162 echo '<option value="' . htmlspecialchars($column) . '"' . $sel . '>'
163 . htmlspecialchars($column) . '</option>' . "\n";
173 <form action
="db_details_qbe.php" method
="post">
174 <table
class="data" style
="width: 100%;">
175 <tr
class="odd noclick">
176 <th
><?php
echo $strField; ?
>:</th
>
179 for ($x = 0; $x < $col; $x++
) {
180 if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {
181 showColumnSelectCell( $fld, $z );
185 if (! empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
190 if (isset($Field[$x])) {
191 $selected = urldecode($Field[$x]);
192 $curField[$z] = urldecode($Field[$x]);
194 showColumnSelectCell($fld, $z, $selected);
201 <tr
class="even noclick">
202 <th
><?php
echo $strSort; ?
>:</th
>
205 for ($x = 0; $x < $col; $x++
) {
206 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
209 <select style
="width: <?php echo $realwidth; ?>" name
="Sort[<?php echo $z; ?>]" size
="1">
210 <option value
=""></option
>
211 <option value
="ASC"><?php
echo $strAscending; ?
></option
>
212 <option value
="DESC"><?php
echo $strDescending; ?
></option
>
220 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
225 <select style
="width: <?php echo $realwidth; ?>" name
="Sort[<?php echo $z; ?>]" size
="1">
226 <option value
=""></option
>
230 // If they have chosen all fields using the * selector,
231 // then sorting is not available
232 // Robbat2 - Fix for Bug #570698
233 if (isset($Sort[$x]) && isset($Field[$x])
234 && substr(urldecode($Field[$x]), -2) == '.*') {
238 if (isset($Sort[$x]) && $Sort[$x] == 'ASC') {
239 $curSort[$z] = $Sort[$x];
240 $sel = ' selected="selected"';
245 echo '<option value="ASC"' . $sel . '>' . $strAscending . '</option>' . "\n";
246 if (isset($Sort[$x]) && $Sort[$x] == 'DESC') {
247 $curSort[$z] = $Sort[$x];
248 $sel = ' selected="selected"';
253 echo '<option value="DESC"' . $sel . '>' . $strDescending . '</option>' . "\n";
265 <tr
class="odd noclick">
266 <th
><?php
echo $strShow; ?
>:</th
>
269 for ($x = 0; $x < $col; $x++
) {
270 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
273 <input type
="checkbox" name
="Show[<?php echo $z; ?>]" />
280 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
283 if (isset($Show[$x])) {
284 $checked = ' checked="checked"';
285 $curShow[$z] = $Show[$x];
291 <input type
="checkbox" name
="Show[<?php echo $z; ?>]"<?php
echo $checked; ?
> />
300 <!-- Criteria row
-->
301 <tr
class="even noclick">
302 <th
><?php
echo $strCriteria; ?
>:</th
>
305 for ($x = 0; $x < $col; $x++
) {
306 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
309 <input type
="text" name
="criteria[<?php echo $z; ?>]" value
="" class="textfield" style
="width: <?php echo $realwidth; ?>" size
="20" />
316 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
319 if (isset($criteria[$x])) {
320 $stripped_Criteria = $criteria[$x];
322 if ((empty($prev_criteria) ||
!isset($prev_criteria[$x]))
323 ||
urldecode($prev_criteria[$x]) != htmlspecialchars($stripped_Criteria)) {
324 $curCriteria[$z] = $stripped_Criteria;
325 $encoded_Criteria = urlencode($stripped_Criteria);
327 $curCriteria[$z] = urldecode($prev_criteria[$x]);
328 $encoded_Criteria = $prev_criteria[$x];
332 <input type
="hidden" name
="prev_criteria[<?php echo $z; ?>]" value
="<?php echo $encoded_Criteria; ?>" />
333 <input type
="text" name
="criteria[<?php echo $z; ?>]" value
="<?php echo htmlspecialchars($stripped_Criteria); ?>" class="textfield" style
="width: <?php echo $realwidth; ?>" size
="20" />
342 <!-- And/Or columns
and rows
-->
346 for ($y = 0; $y <= $row; $y++
) {
347 if (isset($ins_row[$y]) && $ins_row[$y] == 'on') {
348 $chk['or'] = ' checked="checked"';
351 <tr
class="<?php echo $odd_row ? 'odd' : 'even'; ?> noclick">
352 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
353 <!-- Row controls
-->
354 <table cellpadding
="0" cellspacing
="0" border
="0">
356 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
357 <small
><?php
echo $strQBEIns; ?
>:</small
>
358 <input type
="checkbox" name
="ins_row[<?php echo $w; ?>]" />
360 <td align
="<?php echo $cell_align_right; ?>">
361 <b
><?php
echo $strAnd; ?
>:</b
>
364 <input type
="radio" name
="and_or_row[<?php echo $w; ?>]" value
="and"<?php
echo $chk['and']; ?
> />
369 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
370 <small
><?php
echo $strQBEDel; ?
>:</small
>
371 <input type
="checkbox" name
="del_row[<?php echo $w; ?>]" />
373 <td align
="<?php echo $cell_align_right; ?>">
374 <b
><?php
echo $strOr; ?
>:</b
>
377 <input type
="radio" name
="and_or_row[<?php echo $w; ?>]" value
="or"<?php
echo $chk['or']; ?
> />
385 for ($x = 0; $x < $col; $x++
) {
386 if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {
388 $or = 'Or' . $w . '[' . $z . ']';
391 <textarea cols
="20" rows
="2" style
="width: <?php echo $realwidth; ?>" name
="<?php echo $or; ?>" dir
="<?php echo $text_dir; ?>"></textarea
>
396 if (isset($del_col[$x]) && $del_col[$x] == 'on') {
401 $or = 'Or' . $w . '[' . $z . ']';
404 <textarea cols
="20" rows
="2" style
="width: <?php echo $realwidth; ?>" name
="<?php echo $or; ?>" dir
="<?php echo $text_dir; ?>"></textarea
>
414 $odd_row =! $odd_row;
417 if (isset($del_row[$y]) && $del_row[$y] == 'on') {
421 if (isset($and_or_row[$y])) {
422 $curAndOrRow[$w] = $and_or_row[$y];
424 if (isset($and_or_row[$y]) && $and_or_row[$y] == 'and') {
425 $chk['and'] = ' checked="checked"';
428 $chk['or'] = ' checked="checked"';
433 <tr
class="<?php echo $odd_row ? 'odd' : 'even'; ?> noclick">
434 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
435 <!-- Row controls
-->
436 <table border
="0" cellpadding
="0" cellspacing
="0">
438 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
439 <small
><?php
echo $strQBEIns; ?
>:</small
>
440 <input type
="checkbox" name
="ins_row[<?php echo $w; ?>]" />
442 <td align
="<?php echo $cell_align_right; ?>">
443 <b
><?php
echo $strAnd; ?
>:</b
>
446 <input type
="radio" name
="and_or_row[<?php echo $w; ?>]" value
="and"<?php
echo $chk['and']; ?
> />
450 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
451 <small
><?php
echo $strQBEDel; ?
>:</small
>
452 <input type
="checkbox" name
="del_row[<?php echo $w; ?>]" />
454 <td align
="<?php echo $cell_align_right; ?>">
455 <b
><?php
echo $strOr; ?
>:</b
>
458 <input type
="radio" name
="and_or_row[<?php echo $w; ?>]" value
="or"<?php
echo $chk['or']; ?
> />
465 for ($x = 0; $x < $col; $x++
) {
466 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
468 $or = 'Or' . $w . '[' . $z . ']';
471 <textarea cols
="20" rows
="2" style
="width: <?php echo $realwidth; ?>" name
="<?php echo $or; ?>" dir
="<?php echo $text_dir; ?>"></textarea
>
476 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
482 if (!isset($
{$or})) {
485 if (!empty($
{$or}) && isset($
{$or}[$x])) {
486 $stripped_or = $
{$or}[$x];
492 <textarea cols
="20" rows
="2" style
="width: <?php echo $realwidth; ?>" name
="Or<?php echo $w . '[' . $z . ']'; ?>" dir
="<?php echo $text_dir; ?>"><?php
echo htmlspecialchars($stripped_or); ?
></textarea
>
495 if (!empty($
{$or}) && isset($
{$or}[$x])) {
496 $
{'cur' . $or}[$z] = $
{$or}[$x];
506 $odd_row =! $odd_row;
509 <!-- Modify columns
-->
510 <tr
class="even noclick">
511 <th
><?php
echo $strModify; ?
>:</th
>
514 for ($x = 0; $x < $col; $x++
) {
515 if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
516 $curAndOrCol[$z] = $and_or_col[$y];
517 if ($and_or_col[$z] == 'or') {
518 $chk['or'] = ' checked="checked"';
521 $chk['and'] = ' checked="checked"';
526 <b
><?php
echo $strOr; ?
>:</b
>
527 <input type
="radio" name
="and_or_col[<?php echo $z; ?>]" value
="or"<?php
echo $chk['or']; ?
> />
528  
; 
;<b
><?php
echo $strAnd; ?
>:</b
>
529 <input type
="radio" name
="and_or_col[<?php echo $z; ?>]" value
="and"<?php
echo $chk['and']; ?
> />
531 <?php
echo $strQBEIns . "\n"; ?
>
532 <input type
="checkbox" name
="ins_col[<?php echo $z; ?>]" />
533  
; 
;<?php
echo $strQBEDel . "\n"; ?
>
534 <input type
="checkbox" name
="del_col[<?php echo $z; ?>]" />
541 if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
545 if (isset($and_or_col[$y])) {
546 $curAndOrCol[$z] = $and_or_col[$y];
548 if (isset($and_or_col[$z]) && $and_or_col[$z] == 'or') {
549 $chk['or'] = ' checked="checked"';
552 $chk['and'] = ' checked="checked"';
557 <b
><?php
echo $strOr; ?
>:</b
>
558 <input type
="radio" name
="and_or_col[<?php echo $z; ?>]" value
="or"<?php
echo $chk['or']; ?
> />
559  
; 
;<b
><?php
echo $strAnd; ?
>:</b
>
560 <input type
="radio" name
="and_or_col[<?php echo $z; ?>]" value
="and"<?php
echo $chk['and']; ?
> />
562 <?php
echo $strQBEIns . "\n"; ?
>
563 <input type
="checkbox" name
="ins_col[<?php echo $z; ?>]" />
564  
; 
;<?php
echo $strQBEDel . "\n"; ?
>
565 <input type
="checkbox" name
="del_col[<?php echo $z; ?>]" />
575 <!-- Other controls
-->
578 $url_params['db'] = $db;
579 $url_params['col_cnt'] = $z;
580 $url_params['rows'] = $w;
581 echo PMA_generate_common_hidden_inputs($url_params);
583 <fieldset
class="tblFooters">
584 <table border
="0" cellpadding
="2" cellspacing
="1">
587 <?php
echo $strAddDeleteRow; ?
>:
588 <select size
="1" name
="add_row" style
="vertical-align: middle">
589 <option value
="-3">-3</option
>
590 <option value
="-2">-2</option
>
591 <option value
="-1">-1</option
>
592 <option value
="0" selected
="selected">0</option
>
593 <option value
="1">1</option
>
594 <option value
="2">2</option
>
595 <option value
="3">3</option
>
598 <td width
="10"> 
;</td
>
599 <td nowrap
="nowrap"><?php
echo $strAddDeleteColumn; ?
>:
600 <select size
="1" name
="add_col" style
="vertical-align: middle">
601 <option value
="-3">-3</option
>
602 <option value
="-2">-2</option
>
603 <option value
="-1">-1</option
>
604 <option value
="0" selected
="selected">0</option
>
605 <option value
="1">1</option
>
606 <option value
="2">2</option
>
607 <option value
="3">3</option
>
610 <td width
="10"> 
;</td
>
611 <!-- Generates a query
-->
612 <td
><input type
="submit" name
="modify" value
="<?php echo $strUpdateQuery; ?>" /></td
>
620 <legend
><?php
echo $strUseTables; ?
></legend
>
622 $strTableListOptions = '';
623 $numTableListOptions = 0;
624 foreach ($tbl_names AS $key => $val) {
625 $strTableListOptions .= ' ';
626 $strTableListOptions .= '<option value="' . htmlspecialchars($key) . '"' . $val . '>' . htmlspecialchars($key) . '</option>' . "\n";
627 $numTableListOptions++
;
630 <select name
="TableList[]" multiple
="multiple" id
="listTable"
631 size
="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>">
632 <?php
echo $strTableListOptions; ?
>
635 <fieldset
class="tblFooters">
636 <input type
="submit" name
="modify" value
="<?php echo $strUpdateQuery; ?>" />
639 <td width
="20"> 
;</td
>
642 <legend
><?php
echo sprintf($strQueryOnDb, PMA_getDbLink($db)); ?
>
644 <textarea cols
="30" name
="sql_query" id
="textSqlquery"
645 rows
="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>"
646 dir
="<?php echo $text_dir; ?>">
651 if (!isset($qry_select)) {
654 for ($x = 0; $x < $col; $x++
) {
655 if (!empty($curField[$x]) && isset($curShow[$x]) && $curShow[$x] == 'on') {
659 $qry_select .= $curField[$x];
663 if (!empty($qry_select)) {
664 $encoded_qry .= urlencode('SELECT ' . $qry_select . "\n");
665 echo 'SELECT ' . htmlspecialchars($qry_select) . "\n";
670 // Create LEFT JOINS out of Relations
671 // Code originally by Mike Beck <mike.beck@ibmiller.de>
672 // If we can use Relations we could make some left joins.
673 // First find out if relations are available in this database.
675 // First we need the really needed Tables - those in TableList might still be
677 if (isset($Field) && count($Field) > 0) {
679 // Initialize some variables
685 $col_where = array();
688 // We only start this if we have fields, otherwise it would be dumb
689 foreach ($Field AS $value) {
690 $parts = explode('.', $value);
691 if (!empty($parts[0]) && !empty($parts[1])) {
692 $tab_raw = urldecode($parts[0]);
693 $tab = str_replace('`', '', $tab_raw);
694 $tab_all[$tab] = $tab;
696 $col_raw = urldecode($parts[1]);
697 $col_all[] = $tab . '.' . str_replace('`', '', $col_raw);
701 // Check 'where' clauses
702 if ($cfgRelation['relwork'] && count($tab_all) > 0) {
703 // Now we need all tables that we have in the where clause
704 $crit_cnt = count($criteria);
705 for ($x = 0; $x < $crit_cnt; $x++
) {
706 $curr_tab = explode('.', urldecode($Field[$x]));
707 if (!empty($curr_tab[0]) && !empty($curr_tab[1])) {
708 $tab_raw = urldecode($curr_tab[0]);
709 $tab = str_replace('`', '', $tab_raw);
711 $col_raw = urldecode($curr_tab[1]);
712 $col1 = str_replace('`', '', $col_raw);
713 $col1 = $tab . '.' . $col1;
714 // Now we know that our array has the same numbers as $criteria
715 // we can check which of our columns has a where clause
716 if (!empty($criteria[$x])) {
717 if (substr($criteria[$x], 0, 1) == '=' ||
stristr($criteria[$x], 'is')) {
718 $col_where[$col] = $col1;
719 $tab_wher[$tab] = $tab;
725 // Cleans temp vars w/o further use
730 if (count($tab_wher) == 1) {
731 // If there is exactly one column that has a decent where-clause
732 // we will just use this
733 $master = key($tab_wher);
735 // Now let's find out which of the tables has an index
736 // ( When the control user is the same as the normal user
737 // because he is using one of his databases as pmadb,
738 // the last db selected is not always the one where we need to work)
739 PMA_DBI_select_db($db);
741 foreach ($tab_all AS $tab) {
742 $ind_rs = PMA_DBI_query('SHOW INDEX FROM ' . PMA_backquote($tab) . ';');
743 while ($ind = PMA_DBI_fetch_assoc($ind_rs)) {
744 $col1 = $tab . '.' . $ind['Column_name'];
745 if (isset($col_all[$col1])) {
746 if ($ind['non_unique'] == 0) {
747 if (isset($col_where[$col1])) {
748 $col_unique[$col1] = 'Y';
750 $col_unique[$col1] = 'N';
753 if (isset($col_where[$col1])) {
754 $col_index[$col1] = 'Y';
756 $col_index[$col1] = 'N';
760 } // end while (each col of tab)
761 } // end while (each tab)
762 // now we want to find the best.
763 if (isset($col_unique) && count($col_unique) > 0) {
764 $col_cand = $col_unique;
766 } elseif (isset($col_index) && count($col_index) > 0) {
767 $col_cand = $col_index;
769 } elseif (isset($col_where) && count($col_where) > 0) {
770 $col_cand = $tab_wher;
773 $col_cand = $tab_all;
777 // If we came up with $col_unique (very good) or $col_index (still
778 // good) as $col_cand we want to check if we have any 'Y' there
779 // (that would mean that they were also found in the whereclauses
780 // which would be great). if yes, we take only those
781 if ($needsort == 1) {
782 foreach ($col_cand AS $col => $is_where) {
783 $tab = explode('.', $col);
785 if ($is_where == 'Y') {
793 // Candidates restricted in index+where
796 // None of the candidates where in a where-clause
800 // If our array of candidates has more than one member we'll just
801 // find the smallest table.
802 // Of course the actual query would be faster if we check for
803 // the Criteria which gives the smallest result set in its table,
804 // but it would take too much time to check this
805 if (count($col_cand) > 1) {
806 // Of course we only want to check each table once
807 $checked_tables = $col_cand;
808 foreach ($col_cand AS $tab) {
809 if ($checked_tables[$tab] != 1 ) {
810 $tsize[$tab] = PMA_Table
::countRecords($db, $tab, true, false);
811 $checked_tables[$tab] = 1;
813 $csize[$tab] = $tsize[$tab];
817 $master = key($csize); // Smallest
820 $master = current($col_cand); // Only one single candidate
822 } // end if (exactly one where clause)
825 * Removes unwanted entries from an array (PHP3 compliant)
827 * @param array the array to work with
828 * @param array the list of keys to remove
830 * @return array the cleaned up array
834 function PMA_arrayShort($array, $key)
836 foreach ($array AS $k => $v) {
846 } // end of the "PMA_arrayShort()" function
850 * Finds all related tables
852 * @param string wether to go from master to foreign or vice versa
854 * @return boolean always TRUE
856 * @global array the list of tables that we still couldn't connect
857 * @global array the list of allready connected tables
858 * @global string the current databse name
859 * @global string the super user connection id
860 * @global array the list of relation settings
864 function PMA_getRelatives($from) {
865 global $tab_left, $tab_know, $fromclause;
866 global $controllink, $db, $cfgRelation;
868 if ($from == 'master') {
873 $in_know = '(\'' . implode('\', \'', $tab_know) . '\')';
874 $in_left = '(\'' . implode('\', \'', $tab_left) . '\')';
876 $rel_query = 'SELECT *'
877 . ' FROM ' . PMA_backquote($cfgRelation['relation'])
878 . ' WHERE ' . $from . '_db = \'' . PMA_sqlAddslashes($db) . '\''
879 . ' AND ' . $to . '_db = \'' . PMA_sqlAddslashes($db) . '\''
880 . ' AND ' . $from . '_table IN ' . $in_know
881 . ' AND ' . $to . '_table IN ' . $in_left;
882 PMA_DBI_select_db($cfgRelation['db'], $controllink);
883 $relations = @PMA_DBI_query
($rel_query, $controllink);
884 PMA_DBI_select_db($db, $controllink);
885 while ($row = PMA_DBI_fetch_assoc($relations)) {
886 $found_table = $row[$to . '_table'];
887 if (isset($tab_left[$found_table])) {
888 $fromclause .= "\n" . ' LEFT JOIN '
889 . PMA_backquote($row[$to . '_table']) . ' ON '
890 . PMA_backquote($row[$from . '_table']) . '.'
891 . PMA_backquote($row[$from . '_field']) . ' = '
892 . PMA_backquote($row[$to . '_table']) . '.'
893 . PMA_backquote($row[$to . '_field']) . ' ';
894 $tab_know[$found_table] = $found_table;
895 $tab_left = PMA_arrayShort($tab_left, $found_table);
900 } // end of the "PMA_getRelatives()" function
903 $tab_left = PMA_arrayShort($tab_all, $master);
904 $tab_know[$master] = $master;
908 while (count($tab_left) > 0) {
910 PMA_getRelatives('master');
912 PMA_getRelatives('foreign');
917 foreach ($tab_left AS $tab) {
918 $emerg .= ', ' . PMA_backquote($tab);
919 $tab_left = PMA_arrayShort($tab_left, $tab);
923 $qry_from = PMA_backquote($master) . $emerg . $fromclause;
924 } // end if ($cfgRelation['relwork'] && count($tab_all) > 0)
926 } // end count($Field) > 0
928 // In case relations are not defined, just generate the FROM clause
929 // from the list of tables, however we don't generate any JOIN
931 if (empty($qry_from) && isset($tab_all)) {
932 $qry_from = implode(', ', $tab_all);
934 // Now let's see what we got
935 if (!empty($qry_from)) {
936 $encoded_qry .= urlencode('FROM ' . $qry_from . "\n");
937 echo 'FROM ' . htmlspecialchars($qry_from) . "\n";
943 for ($x = 0; $x < $col; $x++
) {
944 if (!empty($curField[$x]) && !empty($curCriteria[$x]) && $x && isset($last_where) && isset($curAndOrCol)) {
945 $qry_where .= ' ' . strtoupper($curAndOrCol[$last_where]) . ' ';
947 if (!empty($curField[$x]) && !empty($curCriteria[$x])) {
948 $qry_where .= '(' . $curField[$x] . ' ' . $curCriteria[$x] . ')';
953 if ($criteria_cnt > 1) {
954 $qry_where = '(' . $qry_where . ')';
956 // OR rows ${'cur' . $or}[$x]
957 if (!isset($curAndOrRow)) {
958 $curAndOrRow = array();
960 for ($y = 0; $y <= $row; $y++
) {
964 for ($x = 0; $x < $col; $x++
) {
965 if (!empty($curField[$x]) && !empty($
{'curOr' . $y}[$x]) && $x) {
966 $qry_orwhere .= ' ' . strtoupper($curAndOrCol[$last_orwhere]) . ' ';
968 if (!empty($curField[$x]) && !empty($
{'curOr' . $y}[$x])) {
969 $qry_orwhere .= '(' . $curField[$x]
971 . $
{'curOr' . $y}[$x]
977 if ($criteria_cnt > 1) {
978 $qry_orwhere = '(' . $qry_orwhere . ')';
980 if (!empty($qry_orwhere)) {
982 . strtoupper(isset($curAndOrRow[$y]) ?
$curAndOrRow[$y] . ' ' : '')
987 if (!empty($qry_where) && $qry_where != '()') {
988 $encoded_qry .= urlencode('WHERE ' . $qry_where . "\n");
989 echo 'WHERE ' . htmlspecialchars($qry_where) . "\n";
994 if (!isset($qry_orderby)) {
997 for ($x = 0; $x < $col; $x++
) {
998 if ($last_orderby && $x && !empty($curField[$x]) && !empty($curSort[$x])) {
999 $qry_orderby .= ', ';
1001 if (!empty($curField[$x]) && !empty($curSort[$x])) {
1002 // if they have chosen all fields using the * selector,
1003 // then sorting is not available
1004 // Robbat2 - Fix for Bug #570698
1005 if (substr($curField[$x], -2) != '.*') {
1006 $qry_orderby .= $curField[$x] . ' ' . $curSort[$x];
1011 if (!empty($qry_orderby)) {
1012 $encoded_qry .= urlencode('ORDER BY ' . $qry_orderby);
1013 echo 'ORDER BY ' . htmlspecialchars($qry_orderby) . "\n";
1017 <input type
="hidden" name
="encoded_sql_query" value
="<?php echo $encoded_qry; ?>" />
1019 <fieldset
class="tblFooters">
1020 <input type
="submit" name
="submit_sql" value
="<?php echo $strRunQuery; ?>" />
1028 * Displays the footer
1030 require_once './libraries/footer.inc.php';