3 // vim: expandtab sw=4 ts=4 sts=4:
7 * Get the values of the variables posted or sent to this script and display
10 require_once('./libraries/grab_globals.lib.php');
11 require_once('./libraries/common.lib.php');
12 require_once('./libraries/relation.lib.php');
16 * Gets the relation settings
18 $cfgRelation = PMA_getRelationsParam();
22 * A query has been submitted -> execute it, else display the headers
24 if (isset($submit_sql) && preg_match('@^SELECT@i', $encoded_sql_query)) {
25 $goto = 'db_details.php';
26 $zero_rows = htmlspecialchars($strSuccess);
27 $sql_query = urldecode($encoded_sql_query);
32 require('./db_details_common.php');
33 $url_query .= '&goto=db_details_qbe.php';
34 require('./db_details_db_info.php');
37 if (isset($submit_sql) && !preg_match('@^SELECT@i', $encoded_sql_query)) {
38 echo '<p class="warning">' . $strHaveToShow . '</p>';
43 * Initialize some variables
45 if (empty($Columns)) {
46 $Columns = 3; // Initial number of columns
48 if (!isset($Add_Col)) {
51 if (!isset($Add_Row)) {
57 if (!isset($InsCol)) {
60 if (!isset($DelCol)) {
63 if (!isset($prev_Criteria)) {
66 // workaround for a PHP3 problem
67 if (!isset($Criteria)) {
70 for ($i = 0; $i < $Columns; $i++
) {
74 if (!isset($InsRow)) {
77 for ($i = 0; $i < $Columns; $i++
) {
81 if (!isset($DelRow)) {
84 for ($i = 0; $i < $Columns; $i++
) {
88 if (!isset($AndOrRow)) {
91 for ($i = 0; $i < $Columns; $i++
) {
95 if (!isset($AndOrCol)) {
98 for ($i = 0; $i < $Columns; $i++
) {
104 $col = $Columns +
$Add_Col;
108 $row = $Rows +
$Add_Row;
117 $tbl_result = PMA_mysql_list_tables($db);
118 $tbl_result_cnt = mysql_num_rows($tbl_result);
122 // The tables list sent by a previously submitted form
123 if (!empty($TableList)) {
124 $cnt_table_list = count($TableList);
125 for ($x = 0; $x < $cnt_table_list; $x++
) {
126 $tbl_names[urldecode($TableList[$x])] = ' selected="selected"';
130 // The tables list gets from MySQL
131 while ($i < $tbl_result_cnt) {
132 $tbl = PMA_mysql_tablename($tbl_result, $i);
133 $fld_results = @PMA_mysql_list_fields_alternate
($db, $tbl) or PMA_mysqlDie(PMA_mysql_error(), 'PMA_mysql_list_fields_alternate(' . $db . ', ' . $tbl . ')', FALSE, $err_url);
134 $fld_results_cnt = ($fld_results) ?
count($fld_results) : 0;
137 if (empty($tbl_names[$tbl]) && !empty($TableList)) {
138 $tbl_names[$tbl] = '';
140 $tbl_names[$tbl] = ' selected="selected"';
143 // The fields list per selected tables
144 if ($tbl_names[$tbl] == ' selected="selected"') {
145 $fld[$k++
] = PMA_backquote($tbl) . '.*';
146 while ($j < $fld_results_cnt) {
147 $fld[$k] = PMA_convert_display_charset($fld_results[$j]['Field']);
148 $fld[$k] = PMA_backquote($tbl) . '.' . PMA_backquote($fld[$k]);
150 // increase the width if necessary
151 if (strlen($fld[$k]) > $wid) {
152 $wid = strlen($fld[$k]);
162 mysql_free_result($tbl_result);
164 // largest width found
165 $realwidth = $wid . 'ex';
173 <!-- Query by example form
-->
174 <form action
="db_details_qbe.php" method
="post">
175 <table border
="<?php echo $cfg['Border']; ?>">
179 <td align
="<?php echo $cell_align_right; ?>" bgcolor
="<?php echo $cfg['ThBgcolor']; ?>">
180 <b
><?php
echo $strField; ?
> 
;: 
;</b
>
184 for ($x = 0; $x < $col; $x++
) {
185 if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
187 <td align
="center" bgcolor
="<?php echo $cfg['BgcolorOne']; ?>">
188 <select style
="width: <?php echo $realwidth; ?>" name
="Field[<?php echo $z; ?>]" size
="1">
189 <option value
=""></option
>
192 for ($y = 0; $y < sizeof($fld); $y++
) {
193 if ($fld[$y] == '') {
194 $sel = ' selected="selected"';
199 echo '<option value="' . htmlspecialchars($fld[$y]) . '"' . $sel . '>' . htmlspecialchars($fld[$y]) . '</option>' . "\n";
209 if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
213 <td align
="center" bgcolor
="<?php echo $cfg['BgcolorOne']; ?>">
214 <select style
="width: <?php echo $realwidth; ?>" name
="Field[<?php echo $z; ?>]" size
="1">
215 <option value
=""></option
>
218 for ($y = 0; $y < sizeof($fld); $y++
) {
219 if (isset($Field[$x]) && $fld[$y] == urldecode($Field[$x])) {
220 $curField[$z] = urldecode($Field[$x]);
221 $sel = ' selected="selected"';
226 echo '<option value="' . htmlspecialchars($fld[$y]) . '"' . $sel . '>' . htmlspecialchars($fld[$y]) . '</option>' . "\n";
240 <td align
="<?php echo $cell_align_right; ?>" bgcolor
="<?php echo $cfg['ThBgcolor']; ?>">
241 <b
><?php
echo $strSort; ?
> 
;: 
;</b
>
245 for ($x = 0; $x < $col; $x++
) {
246 if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
248 <td align
="center" bgcolor
="<?php echo $cfg['BgcolorTwo']; ?>">
249 <select style
="width: <?php echo $realwidth; ?>" name
="Sort[<?php echo $z; ?>]" size
="1">
250 <option value
=""></option
>
251 <option value
="ASC"><?php
echo $strAscending; ?
></option
>
252 <option value
="DESC"><?php
echo $strDescending; ?
></option
>
260 if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
264 <td align
="center" bgcolor
="<?php echo $cfg['BgcolorTwo']; ?>">
265 <select style
="width: <?php echo $realwidth; ?>" name
="Sort[<?php echo $z; ?>]" size
="1">
266 <option value
=""></option
>
270 // If they have chosen all fields using the * selector,
271 // then sorting is not available
272 // Robbat2 - Fix for Bug #570698
273 if (isset($Sort[$x]) && isset($Field[$x]) && (substr(urldecode($Field[$x]),-2) == '.*')) {
277 if (isset($Sort[$x]) && $Sort[$x] == 'ASC') {
278 $curSort[$z] = $Sort[$x];
279 $sel = ' selected="selected"';
284 echo '<option value="ASC"' . $sel . '>' . $strAscending . '</option>' . "\n";
285 if (isset($Sort[$x]) && $Sort[$x] == 'DESC') {
286 $curSort[$z] = $Sort[$x];
287 $sel = ' selected="selected"';
292 echo '<option value="DESC"' . $sel . '>' . $strDescending . '</option>' . "\n";
305 <td align
="<?php echo $cell_align_right; ?>" bgcolor
="<?php echo $cfg['ThBgcolor']; ?>">
306 <b
><?php
echo $strShow; ?
> 
;: 
;</b
>
310 for ($x = 0; $x < $col; $x++
) {
311 if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
313 <td align
="center" bgcolor
="<?php echo $cfg['BgcolorOne']; ?>">
314 <input type
="checkbox" name
="Show[<?php echo $z; ?>]" />
321 if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
324 if (isset($Show[$x])) {
325 $checked = ' checked="checked"';
326 $curShow[$z] = $Show[$x];
331 <td align
="center" bgcolor
="<?php echo $cfg['BgcolorOne']; ?>">
332 <input type
="checkbox" name
="Show[<?php echo $z; ?>]"<?php
echo $checked; ?
> />
341 <!-- Criteria row
-->
343 <td align
="<?php echo $cell_align_right; ?>" bgcolor
="<?php echo $cfg['ThBgcolor']; ?>">
344 <b
><?php
echo $strCriteria; ?
> 
;: 
;</b
>
348 for ($x = 0; $x < $col; $x++
) {
349 if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
351 <td align
="center" bgcolor
="<?php echo $cfg['BgcolorTwo']; ?>">
352 <input type
="text" name
="Criteria[<?php echo $z; ?>]" value
="" class="textfield" style
="width: <?php echo $realwidth; ?>" size
="20" />
359 if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
362 if (isset($Criteria[$x])) {
363 $stripped_Criteria = $Criteria[$x];
365 if ((empty($prev_Criteria) ||
!isset($prev_Criteria[$x]))
366 ||
urldecode($prev_Criteria[$x]) != htmlspecialchars($stripped_Criteria)) {
367 $curCriteria[$z] = $stripped_Criteria;
368 $encoded_Criteria = urlencode($stripped_Criteria);
370 $curCriteria[$z] = urldecode($prev_Criteria[$x]);
371 $encoded_Criteria = $prev_Criteria[$x];
374 <td align
="center" bgcolor
="<?php echo $cfg['BgcolorTwo']; ?>">
375 <input type
="hidden" name
="prev_Criteria[<?php echo $z; ?>]" value
="<?php echo $encoded_Criteria; ?>" />
376 <input type
="text" name
="Criteria[<?php echo $z; ?>]" value
="<?php echo htmlspecialchars($stripped_Criteria); ?>" class="textfield" style
="width: <?php echo $realwidth; ?>" size
="20" />
385 <!-- And/Or columns
and rows
-->
388 for ($y = 0; $y <= $row; $y++
) {
389 $bgcolor = ($y %
2) ?
$cfg['BgcolorOne'] : $cfg['BgcolorTwo'];
390 if (isset($InsRow[$y]) && $InsRow[$y] == 'on') {
391 $chk['or'] = ' checked="checked"';
395 <td align
="<?php echo $cell_align_right; ?>" bgcolor
="<?php echo $bgcolor; ?>" nowrap
="nowrap">
396 <!-- Row controls
-->
397 <table bgcolor
="<?php echo $bgcolor; ?>">
399 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
400 <small
><?php
echo $strQBEIns; ?
> 
;:</small
>
401 <input type
="checkbox" name
="InsRow[<?php echo $w; ?>]" />
403 <td align
="<?php echo $cell_align_right; ?>">
404 <b
><?php
echo $strAnd; ?
> 
;:</b
>
407 <input type
="radio" name
="AndOrRow[<?php echo $w; ?>]" value
="and"<?php
echo $chk['and']; ?
> />
412 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
413 <small
><?php
echo $strQBEDel; ?
> 
;:</small
>
414 <input type
="checkbox" name
="DelRow[<?php echo $w; ?>]" />
416 <td align
="<?php echo $cell_align_right; ?>">
417 <b
><?php
echo $strOr; ?
> 
;:</b
>
420 <input type
="radio" name
="AndOrRow[<?php echo $w; ?>]" value
="or"<?php
echo $chk['or']; ?
> />
428 for ($x = 0; $x < $col; $x++
) {
429 if ($InsCol[$x] == 'on') {
431 $or = 'Or' . $w . '[' . $z . ']';
433 <td align
="center" bgcolor
="<?php echo $bgcolor; ?>">
434 <textarea cols
="20" rows
="2" style
="width: <?php echo $realwidth; ?>" name
="<?php echo $or; ?>" dir
="<?php echo $text_dir; ?>"></textarea
>
439 if ($DelCol[$x] == 'on') {
444 $or = 'Or' . $w . '[' . $z . ']';
446 <td align
="center" bgcolor
="<?php echo $bgcolor; ?>">
447 <textarea cols
="20" rows
="2" style
="width: <?php echo $realwidth; ?>" name
="<?php echo $or; ?>" dir
="<?php echo $text_dir; ?>"></textarea
>
459 if (isset($DelRow[$y]) && $DelRow[$y] == 'on') {
463 if (isset($AndOrRow[$y])) {
464 $curAndOrRow[$w] = $AndOrRow[$y];
466 if (isset($AndOrRow[$y]) && $AndOrRow[$y] == 'and') {
467 $chk['and'] = ' checked="checked"';
470 $chk['or'] = ' checked="checked"';
476 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
477 <!-- Row controls
-->
478 <table bgcolor
="<?php echo $bgcolor; ?>">
480 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
481 <small
><?php
echo $strQBEIns; ?
> 
;:</small
>
482 <input type
="checkbox" name
="InsRow[<?php echo $w; ?>]" />
484 <td align
="<?php echo $cell_align_right; ?>">
485 <b
><?php
echo $strAnd; ?
> 
;:</b
>
488 <input type
="radio" name
="AndOrRow[<?php echo $w; ?>]" value
="and"<?php
echo $chk['and']; ?
> />
492 <td align
="<?php echo $cell_align_right; ?>" nowrap
="nowrap">
493 <small
><?php
echo $strQBEDel; ?
> 
;:</small
>
494 <input type
="checkbox" name
="DelRow[<?php echo $w; ?>]" />
496 <td align
="<?php echo $cell_align_right; ?>">
497 <b
><?php
echo $strOr; ?
> 
;:</b
>
500 <input type
="radio" name
="AndOrRow[<?php echo $w; ?>]" value
="or"<?php
echo $chk['or']; ?
> />
507 for ($x = 0; $x < $col; $x++
) {
508 if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
510 $or = 'Or' . $w . '[' . $z . ']';
512 <td align
="center" bgcolor
="<?php echo $bgcolor; ?>">
513 <textarea cols
="20" rows
="2" style
="width: <?php echo $realwidth; ?>" name
="<?php echo $or; ?>" dir
="<?php echo $text_dir; ?>"></textarea
>
518 if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
524 if (!isset($
{$or})) {
527 if (!empty($
{$or}) && isset($
{$or}[$x])) {
528 $stripped_or = $
{$or}[$x];
533 <td align
="center" bgcolor
="<?php echo $bgcolor; ?>">
534 <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
>
537 if (!empty($
{$or}) && isset($
{$or}[$x])) {
538 $
{'cur' . $or}[$z] = $
{$or}[$x];
551 <!-- Modify columns
-->
553 <td align
="<?php echo $cell_align_right; ?>" bgcolor
="<?php echo $cfg['ThBgcolor']; ?>">
554 <b
><?php
echo $strModify; ?
> 
;: 
;</b
>
558 for ($x = 0; $x < $col; $x++
) {
559 if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
560 $curAndOrCol[$z] = $AndOrCol[$y];
561 if ($AndOrCol[$z] == 'or') {
562 $chk['or'] = ' checked="checked"';
565 $chk['and'] = ' checked="checked"';
569 <td align
="center" bgcolor
="<?php echo $cfg['BgcolorTwo']; ?>">
570 <b
><?php
echo $strOr; ?
> 
;:</b
>
571 <input type
="radio" name
="AndOrCol[<?php echo $z; ?>]" value
="or"<?php
echo $chk['or']; ?
> />
572  
; 
;<b
><?php
echo $strAnd; ?
> 
;:</b
>
573 <input type
="radio" name
="AndOrCol[<?php echo $z; ?>]" value
="and"<?php
echo $chk['and']; ?
> />
575 <?php
echo $strQBEIns . "\n"; ?
>
576 <input type
="checkbox" name
="InsCol[<?php echo $z; ?>]" />
577  
; 
;<?php
echo $strQBEDel . "\n"; ?
>
578 <input type
="checkbox" name
="DelCol[<?php echo $z; ?>]" />
585 if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
589 if (isset($AndOrCol[$y])) {
590 $curAndOrCol[$z] = $AndOrCol[$y];
592 if (isset($AndOrCol[$z]) && $AndOrCol[$z] == 'or') {
593 $chk['or'] = ' checked="checked"';
596 $chk['and'] = ' checked="checked"';
600 <td align
="center" bgcolor
="<?php echo $cfg['BgcolorTwo']; ?>">
601 <b
><?php
echo $strOr; ?
> 
;:</b
>
602 <input type
="radio" name
="AndOrCol[<?php echo $z; ?>]" value
="or"<?php
echo $chk['or']; ?
> />
603  
; 
;<b
><?php
echo $strAnd; ?
> 
;:</b
>
604 <input type
="radio" name
="AndOrCol[<?php echo $z; ?>]" value
="and"<?php
echo $chk['and']; ?
> />
606 <?php
echo $strQBEIns . "\n"; ?
>
607 <input type
="checkbox" name
="InsCol[<?php echo $z; ?>]" />
608  
; 
;<?php
echo $strQBEDel . "\n"; ?
>
609 <input type
="checkbox" name
="DelCol[<?php echo $z; ?>]" />
620 <!-- Other controls
-->
624 <table border
="0" align
="<?php echo $cell_align_left; ?>">
626 <td rowspan
="4" valign
="top">
627 <?php
echo $strUseTables; ?
> 
;:
629 <select name
="TableList[]" size
="7" multiple
="multiple">
631 foreach($tbl_names AS $key => $val) {
633 echo '<option value="' . htmlspecialchars($key) . '"' . $val . '>' . htmlspecialchars($key) . '</option>' . "\n";
638 <td align
="<?php echo $cell_align_right; ?>" valign
="bottom">
639 <input type
="hidden" value
="<?php echo htmlspecialchars($db); ?>" name
="db" />
640 <input type
="hidden" value
="<?php echo $z; ?>" name
="Columns" />
644 <input type
="hidden" value
="<?php echo $w; ?>" name
="Rows" />
645 <?php
echo $strAddDeleteRow; ?
> 
;:
646 <select size
="1" name
="Add_Row">
647 <option value
="-3">-3</option
>
648 <option value
="-2">-2</option
>
649 <option value
="-1">-1</option
>
650 <option value
="0" selected
="selected">0</option
>
651 <option value
="1">1</option
>
652 <option value
="2">2</option
>
653 <option value
="3">3</option
>
658 <td align
="<?php echo $cell_align_right; ?>" valign
="bottom">
659 <?php
echo $strAddDeleteColumn; ?
> 
;:
660 <select size
="1" name
="Add_Col">
661 <option value
="-3">-3</option
>
662 <option value
="-2">-2</option
>
663 <option value
="-1">-1</option
>
664 <option value
="0" selected
="selected">0</option
>
665 <option value
="1">1</option
>
666 <option value
="2">2</option
>
667 <option value
="3">3</option
>
671 <!-- Generates a query
-->
672 <tr align
="center" valign
="top">
674 <input type
="submit" name
="modify" value
="<?php echo $strUpdateQuery; ?>" />
675 <?php
echo PMA_generate_common_hidden_inputs(); ?
>
678 <!-- Executes a query
-->
679 <tr align
="center" valign
="top">
681 <input type
="submit" name
="submit_sql" value
="<?php echo $strRunQuery; ?>" />
687 <!-- Displays the current query
-->
688 <?php
echo sprintf($strQueryOnDb, htmlspecialchars($db)); ?
><br
/>
689 <textarea cols
="30" rows
="7" name
="sql_query" dir
="<?php echo $text_dir; ?>">
694 if (!isset($qry_select)) {
697 for ($x = 0; $x < $col; $x++
) {
698 if (!empty($curField[$x]) && isset($curShow[$x]) && $curShow[$x] == 'on') {
702 $qry_select .= $curField[$x];
706 if (!empty($qry_select)) {
707 $encoded_qry .= urlencode('SELECT ' . $qry_select . "\n");
708 echo 'SELECT ' . htmlspecialchars($qry_select) . "\n";
713 // Create LEFT JOINS out of Relations
714 // Code originally by Mike Beck <mike.beck@ibmiller.de>
715 // If we can use Relations we could make some left joins.
716 // First find out if relations are available in this database.
718 // First we need the really needed Tables - those in TableList might still be
720 if (isset($Field) && count($Field) > 0) {
722 // Initialize some variables
728 $col_where = array();
731 // We only start this if we have fields, otherwise it would be dumb
732 foreach($Field AS $value) {
733 $parts = explode('.', $value);
734 if (!empty($parts[0]) && !empty($parts[1])) {
735 $tab_raw = urldecode($parts[0]);
736 $tab = str_replace('`', '', $tab_raw);
737 $tab_all[$tab] = $tab;
739 $col_raw = urldecode($parts[1]);
740 $col_all[] = $tab . '.' . str_replace('`', '', $col_raw);
744 // Check 'where' clauses
745 if ($cfgRelation['relwork'] && count($tab_all) > 0) {
746 // Now we need all tables that we have in the where clause
747 $crit_cnt = count($Criteria);
748 for ($x = 0; $x < $crit_cnt; $x++
) {
749 $curr_tab = explode('.', urldecode($Field[$x]));
750 if (!empty($curr_tab[0]) && !empty($curr_tab[1])) {
751 $tab_raw = urldecode($curr_tab[0]);
752 $tab = str_replace('`', '', $tab_raw);
754 $col_raw = urldecode($curr_tab[1]);
755 $col1 = str_replace('`', '', $col_raw);
756 $col1 = $tab . '.' . $col1;
757 // Now we know that our array has the same numbers as $Criteria
758 // we can check which of our columns has a where clause
759 if (!empty($Criteria[$x])) {
760 if (substr($Criteria[$x], 0, 1) == '=' ||
stristr($Criteria[$x], 'is')) {
761 $col_where[$col] = $col1;
762 $tab_wher[$tab] = $tab;
768 // Cleans temp vars w/o further use
773 if (count($tab_wher) == 1) {
774 // If there is exactly one column that has a decent where-clause
775 // we will just use this
776 $master = key($tab_wher);
778 // Now let's find out which of the tables has an index
779 foreach($tab_all AS $tab) {
780 $ind_qry = 'SHOW INDEX FROM ' . PMA_backquote($tab);
781 $ind_rs = PMA_mysql_query($ind_qry);
782 while ($ind = PMA_mysql_fetch_array($ind_rs)) {
783 $col1 = $tab . '.' . $ind['Column_name'];
784 if (isset($col_all[$col1])) {
785 if ($ind['non_unique'] == 0) {
786 if (isset($col_where[$col1])) {
787 $col_unique[$col1] = 'Y';
789 $col_unique[$col1] = 'N';
792 if (isset($col_where[$col1])) {
793 $col_index[$col1] = 'Y';
795 $col_index[$col1] = 'N';
799 } // end while (each col of tab)
800 } // end while (each tab)
801 // now we want to find the best.
802 if (isset($col_unique) && count($col_unique) > 0) {
803 $col_cand = $col_unique;
805 } else if (isset($col_index) && count($col_index) > 0) {
806 $col_cand = $col_index;
808 } else if (isset($col_where) && count($col_where) > 0) {
809 $col_cand = $tab_wher;
812 $col_cand = $tab_all;
816 // If we came up with $col_unique (very good) or $col_index (still
817 // good) as $col_cand we want to check if we have any 'Y' there
818 // (that would mean that they were also found in the whereclauses
819 // which would be great). if yes, we take only those
820 if ($needsort == 1) {
821 foreach($col_cand AS $col => $is_where) {
822 $tab = explode('.', $col);
824 if ($is_where == 'Y') {
832 // Candidates restricted in index+where
835 // None of the candidates where in a where-clause
839 // If our array of candidates has more than one member we'll just
840 // find the smallest table.
841 // Of course the actual query would be faster if we check for
842 // the Criteria which gives the smallest result set in its table,
843 // but it would take too much time to check this
844 if (count($col_cand) > 1) {
845 // Of course we only want to check each table once
846 $checked_tables = $col_cand;
847 foreach($col_cand AS $tab) {
848 if ($checked_tables[$tab] != 1 ) {
849 $rows_qry = 'SELECT COUNT(1) AS anz '
850 . 'FROM ' . PMA_backquote($tab);
851 $rows_rs = PMA_mysql_query($rows_qry);
852 while ($res = PMA_mysql_fetch_array($rows_rs)) {
853 $tsize[$tab] = $res['anz'];
855 $checked_tables[$tab] = 1;
857 $csize[$tab] = $tsize[$tab];
861 $master = key($csize); // Smallest
864 $master = current($col_cand); // Only one single candidate
866 } // end if (exactly one where clause)
869 * Removes unwanted entries from an array (PHP3 compliant)
871 * @param array the array to work with
872 * @param array the list of keys to remove
874 * @return array the cleaned up array
878 function PMA_arrayShort($array, $key)
880 foreach($array AS $k => $v) {
890 } // end of the "PMA_arrayShort()" function
894 * Finds all related tables
896 * @param string wether to go from master to foreign or vice versa
898 * @return boolean always TRUE
900 * @global array the list of tables that we still couldn't connect
901 * @global array the list of allready connected tables
902 * @global string the current databse name
903 * @global string the super user connection id
904 * @global array the list of relation settings
908 function PMA_getRelatives($from) {
909 global $tab_left, $tab_know, $fromclause;
910 global $dbh, $db, $cfgRelation;
912 if ($from == 'master') {
917 $in_know = '(\'' . implode('\', \'', $tab_know) . '\')';
918 $in_left = '(\'' . implode('\', \'', $tab_left) . '\')';
920 $rel_query = 'SELECT *'
921 . ' FROM ' . PMA_backquote($cfgRelation['relation'])
922 . ' WHERE ' . $from . '_db = \'' . PMA_sqlAddslashes($db) . '\''
923 . ' AND ' . $to . '_db = \'' . PMA_sqlAddslashes($db) . '\''
924 . ' AND ' . $from . '_table IN ' . $in_know
925 . ' AND ' . $to . '_table IN ' . $in_left;
927 PMA_mysql_select_db($cfgRelation['db'], $dbh);
928 $relations = @PMA_mysql_query
($rel_query, $dbh) or PMA_mysqlDie(PMA_mysql_error($dbh), $rel_query, '', $err_url_0);
929 PMA_mysql_select_db($db, $dbh);
931 PMA_mysql_select_db($cfgRelation['db']);
932 $relations = @PMA_mysql_query
($rel_query) or PMA_mysqlDie('', $rel_query, '', $err_url_0);
933 PMA_mysql_select_db($db);
935 while ($row = PMA_mysql_fetch_array($relations)) {
936 $found_table = $row[$to . '_table'];
937 if (isset($tab_left[$found_table])) {
938 $fromclause .= "\n" . ' LEFT JOIN '
939 . PMA_backquote($row[$to . '_table']) . ' ON '
940 . PMA_backquote($row[$from . '_table']) . '.'
941 . PMA_backquote($row[$from . '_field']) . ' = '
942 . PMA_backquote($row[$to . '_table']) . '.'
943 . PMA_backquote($row[$to . '_field']) . ' ';
944 $tab_know[$found_table] = $found_table;
945 $tab_left = PMA_arrayShort($tab_left, $found_table);
950 } // end of the "PMA_getRelatives()" function
953 $tab_left = PMA_arrayShort($tab_all, $master);
954 $tab_know[$master] = $master;
958 while (count($tab_left) > 0) {
960 PMA_getRelatives('master');
962 PMA_getRelatives('foreign');
967 foreach($tab_left AS $tab) {
968 $emerg .= ', ' . $tab;
969 $tab_left = PMA_arrayShort($tab_left, $tab);
973 $qry_from = $master . $emerg . $fromclause;
974 } // end if ($cfgRelation['relwork'] && count($tab_all) > 0)
976 } // end count($Field) > 0
978 // In case relations are not defined, just generate the FROM clause
979 // from the list of tables, however we don't generate any JOIN
981 if (empty($qry_from) && isset($tab_all)) {
982 $qry_from = implode(', ', $tab_all);
984 // Now let's see what we got
985 if (!empty($qry_from)) {
986 $encoded_qry .= urlencode('FROM ' . $qry_from . "\n");
987 echo 'FROM ' . htmlspecialchars($qry_from) . "\n";
993 for ($x = 0; $x < $col; $x++
) {
994 if (!empty($curField[$x]) && !empty($curCriteria[$x]) && $x && isset($last_where)) {
995 $qry_where .= ' ' . strtoupper($curAndOrCol[$last_where]) . ' ';
997 if (!empty($curField[$x]) && !empty($curCriteria[$x])) {
998 $qry_where .= '(' . $curField[$x] . ' ' . $curCriteria[$x] . ')';
1003 if ($criteria_cnt > 1) {
1004 $qry_where = '(' . $qry_where . ')';
1006 // OR rows ${'cur' . $or}[$x]
1007 if (!isset($curAndOrRow)) {
1008 $curAndOrRow = array();
1010 for ($y = 0; $y <= $row; $y++
) {
1014 for ($x = 0; $x < $col; $x++
) {
1015 if (!empty($curField[$x]) && !empty($
{'curOr' . $y}[$x]) && $x) {
1016 $qry_orwhere .= ' ' . strtoupper($curAndOrCol[$last_orwhere]) . ' ';
1018 if (!empty($curField[$x]) && !empty($
{'curOr' . $y}[$x])) {
1019 $qry_orwhere .= '(' . $curField[$x]
1021 . $
{'curOr' . $y}[$x]
1027 if ($criteria_cnt > 1) {
1028 $qry_orwhere = '(' . $qry_orwhere . ')';
1030 if (!empty($qry_orwhere)) {
1032 . strtoupper(isset($curAndOrRow[$y]) ?
$curAndOrRow[$y] . ' ' : '')
1037 if (!empty($qry_where) && $qry_where != '()') {
1038 $encoded_qry .= urlencode('WHERE ' . $qry_where . "\n");
1039 echo 'WHERE ' . htmlspecialchars($qry_where) . "\n";
1044 if (!isset($qry_orderby)) {
1047 for ($x = 0; $x < $col; $x++
) {
1048 if ($last_orderby && $x && !empty($curField[$x]) && !empty($curSort[$x])) {
1049 $qry_orderby .= ', ';
1051 if (!empty($curField[$x]) && !empty($curSort[$x])) {
1052 // if they have chosen all fields using the * selector,
1053 // then sorting is not available
1054 // Robbat2 - Fix for Bug #570698
1055 if (substr($curField[$x], -2) != '.*') {
1056 $qry_orderby .= $curField[$x] . ' ' . $curSort[$x];
1061 if (!empty($qry_orderby)) {
1062 $encoded_qry .= urlencode('ORDER BY ' . $qry_orderby);
1063 echo 'ORDER BY ' . htmlspecialchars($qry_orderby) . "\n";
1067 <input type
="hidden" name
="encoded_sql_query" value
="<?php echo $encoded_qry; ?>" />
1077 * Displays the footer
1079 require_once('./footer.inc.php');