db_details_qbe.php3

   1 <?php
   2 /* $Id$ */
   3 // vim: expandtab sw=4 ts=4 sts=4:
   4
   5
   6 /**
   7  * Get the values of the variables posted or sent to this script and display
   8  * the headers
   9  */
  10 require('./libraries/grab_globals.lib.php3');
  11 require('./libraries/common.lib.php3');
  12 require('./libraries/relation.lib.php3');
  13
  14
  15 /**
  16  * Gets the relation settings
  17  */
  18 $cfgRelation = PMA_getRelationsParam();
  19
  20
  21 /**
  22  * A query has been submitted -> execute it, else display the headers
  23  */
  24 if (isset($submit_sql) && eregi('^SELECT', $encoded_sql_query)) {
  25     $goto      = 'db_details.php3';
  26     $zero_rows = htmlspecialchars($strSuccess);
  27     $sql_query = urldecode($encoded_sql_query);
  28     if (get_magic_quotes_gpc()) {
  29         $sql_query = addslashes($sql_query);
  30     }
  31     include('./sql.php3');
  32     exit();
  33 } else {
  34     $sub_part  = '_qbe';
  35     include('./db_details_common.php3');
  36     $url_query .= '&amp;goto=db_details_qbe.php3';
  37     include('./db_details_db_info.php3');
  38 }
  39
  40 if (isset($submit_sql) && !eregi('^SELECT', $encoded_sql_query)) {
  41     echo '<p class="warning">' . $strHaveToShow . '</p>';
  42 }
  43
  44
  45 /**
  46  * Initialize some variables
  47  */
  48 if (empty($Columns)) {
  49     $Columns  = 3;  // Initial number of columns
  50 }
  51 if (!isset($Add_Col)) {
  52     $Add_Col  = '';
  53 }
  54 if (!isset($Add_Row)) {
  55     $Add_Row  = '';
  56 }
  57 if (!isset($Rows)) {
  58     $Rows     = '';
  59 }
  60 if (!isset($InsCol)) {
  61     $InsCol   = '';
  62 }
  63 if (!isset($DelCol)) {
  64     $DelCol   = '';
  65 }
  66 if (!isset($prev_Criteria)) {
  67     $prev_Criteria = '';
  68 }
  69 // workaround for a PHP3 problem
  70 if (!isset($Criteria)) {
  71     //$Criteria = '';
  72     $Criteria = array();
  73     for ($i = 0; $i < $Columns; $i++) {
  74         $Criteria[$i] = '';
  75     }
  76 }
  77 if (!isset($InsRow)) {
  78 //    $InsRow   = '';
  79     $InsRow = array();
  80     for ($i = 0; $i < $Columns; $i++) {
  81         $InsRow[$i] = '';
  82     }
  83 }
  84 if (!isset($DelRow)) {
  85 //    $DelRow   = '';
  86     $DelRow = array();
  87     for ($i = 0; $i < $Columns; $i++) {
  88         $DelRow[$i] = '';
  89     }
  90 }
  91 if (!isset($AndOrRow)) {
  92 //    $AndOrRow = '';
  93     $AndOrRow = array();
  94     for ($i = 0; $i < $Columns; $i++) {
  95         $AndOrRow[$i] = '';
  96     }
  97 }
  98 if (!isset($AndOrCol)) {
  99 //    $AndOrCol = '';
 100     $AndOrCol = array();
 101     for ($i = 0; $i < $Columns; $i++) {
 102         $AndOrCol[$i] = '';
 103     }
 104 }
 105 // minimum width
 106 $wid          = 12;
 107 $col          = $Columns + $Add_Col;
 108 if ($col < 0) {
 109     $col      = 0;
 110 }
 111 $row          = $Rows + $Add_Row;
 112 if ($row < 0) {
 113     $row      = 0;
 114 }
 115
 116
 117 /**
 118  * Prepares the form
 119  */
 120 $tbl_result     = PMA_mysql_list_tables($db);
 121 $tbl_result_cnt = mysql_num_rows($tbl_result);
 122 $i              = 0;
 123 $k              = 0;
 124
 125 // The tables list sent by a previously submitted form
 126 if (!empty($TableList)) {
 127     for ($x = 0; $x < count($TableList); $x++) {
 128         $tbl_names[urldecode($TableList[$x])] = ' selected="selected"';
 129     }
 130 } // end if
 131
 132 // The tables list gets from MySQL
 133 while ($i < $tbl_result_cnt) {
 134     $tbl             = PMA_mysql_tablename($tbl_result, $i);
 135     $fld_results     = @PMA_mysql_list_fields($db, $tbl) or PMA_mysqlDie(PMA_mysql_error(), 'PMA_mysql_list_fields(' . $db . ', ' . $tbl . ')', FALSE, $err_url);
 136     $fld_results_cnt = ($fld_results) ? mysql_num_fields($fld_results) : 0;
 137     $j               = 0;
 138
 139     if (empty($tbl_names[$tbl]) && !empty($TableList)) {
 140         $tbl_names[$tbl] = '';
 141     } else {
 142         $tbl_names[$tbl] = ' selected="selected"';
 143     } //  end if
 144
 145     // The fields list per selected tables
 146     if ($tbl_names[$tbl] == ' selected="selected"') {
 147         $fld[$k++]   =  PMA_backquote($tbl) . '.*';
 148         while ($j < $fld_results_cnt) {
 149             $fld[$k] = PMA_mysql_field_name($fld_results, $j);
 150             $fld[$k] = PMA_backquote($tbl) . '.' . PMA_backquote($fld[$k]);
 151
 152             // increase the width if necessary
 153             if (strlen($fld[$k]) > $wid) {
 154                 $wid = strlen($fld[$k]);
 155             } //end if
 156
 157             $k++;
 158             $j++;
 159         } // end while
 160     } // end if
 161     if ($fld_results) {
 162         mysql_free_result($fld_results);
 163     }
 164
 165     $i++;
 166 } // end if
 167 mysql_free_result($tbl_result);
 168
 169 // largest width found
 170 $realwidth = $wid . 'ex';
 171
 172
 173 /**
 174  * Displays the form
 175  */
 176 ?>
 177
 178 <!-- Query by example form -->
 179 <form action="db_details_qbe.php3" method="post">
 180     <table border="<?php echo $cfg['Border']; ?>">
 181
 182     <!-- Fields row -->
 183     <tr>
 184         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $cfg['ThBgcolor']; ?>">
 185             <b><?php echo $strField; ?>&nbsp;:&nbsp;</b>
 186         </td>
 187 <?php
 188 $z = 0;
 189 for ($x = 0; $x < $col; $x++) {
 190     if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
 191         ?>
 192         <td align="center" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
 193             <select style="width: <?php echo $realwidth; ?>" name="Field[<?php echo $z; ?>]" size="1">
 194                 <option value=""></option>
 195         <?php
 196         echo "\n";
 197         for ($y = 0; $y < sizeof($fld); $y++) {
 198             if ($fld[$y] == '') {
 199                 $sel = ' selected="selected"';
 200             } else {
 201                 $sel = '';
 202             }
 203             echo '                ';
 204             echo '<option value="' . urlencode($fld[$y]) . '"' . $sel . '>' . htmlspecialchars($fld[$y]) . '</option>' . "\n";
 205         } // end for
 206         ?>
 207             </select>
 208         </td>
 209         <?php
 210         $z++;
 211     } // end if
 212     echo "\n";
 213
 214     if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
 215         continue;
 216     }
 217     ?>
 218         <td align="center" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
 219             <select style="width: <?php echo $realwidth; ?>" name="Field[<?php echo $z; ?>]" size="1">
 220                 <option value=""></option>
 221     <?php
 222     echo "\n";
 223     for ($y = 0; $y < sizeof($fld); $y++) {
 224         if (isset($Field[$x]) && $fld[$y] == urldecode($Field[$x])) {
 225             $curField[$z] = urldecode($Field[$x]);
 226             $sel          = ' selected="selected"';
 227         } else {
 228             $sel          = '';
 229         } // end if
 230         echo '                ';
 231         echo '<option value="' . urlencode($fld[$y]) . '"' . $sel . '>' . htmlspecialchars($fld[$y]) . '</option>' . "\n";
 232     } // end for
 233     ?>
 234             </select>
 235         </td>
 236     <?php
 237     $z++;
 238     echo "\n";
 239 } // end for
 240 ?>
 241     </tr>
 242
 243     <!-- Sort row -->
 244     <tr>
 245         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $cfg['ThBgcolor']; ?>">
 246             <b><?php echo $strSort; ?>&nbsp;:&nbsp;</b>
 247         </td>
 248 <?php
 249 $z = 0;
 250 for ($x = 0; $x < $col; $x++) {
 251     if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
 252         ?>
 253         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
 254             <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
 255                 <option value=""></option>
 256                 <option value="ASC"><?php echo $strAscending; ?></option>
 257                 <option value="DESC"><?php echo $strDescending; ?></option>
 258             </select>
 259         </td>
 260         <?php
 261         $z++;
 262     } // end if
 263     echo "\n";
 264
 265     if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
 266         continue;
 267     }
 268     ?>
 269         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
 270             <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
 271                 <option value=""></option>
 272     <?php
 273     echo "\n";
 274
 275     // If they have chosen all fields using the * selector,
 276     // then sorting is not available
 277     // Robbat2 - Fix for Bug #570698
 278     if (isset($Sort[$x]) && isset($Field[$x]) && (substr(urldecode($Field[$x]),-2) == '.*')) {
 279         $Sort[$x] = '';
 280     } //end if
 281
 282     if (isset($Sort[$x]) && $Sort[$x] == 'ASC') {
 283         $curSort[$z] = $Sort[$x];
 284         $sel         = ' selected="selected"';
 285     } else {
 286         $sel         = '';
 287     } // end if
 288     echo '                ';
 289     echo '<option value="ASC"' . $sel . '>' . $strAscending . '</option>' . "\n";
 290     if (isset($Sort[$x]) && $Sort[$x] == 'DESC') {
 291         $curSort[$z] = $Sort[$x];
 292         $sel         = ' selected="selected"';
 293     } else {
 294         $sel         = '';
 295     } // end if
 296     echo '                ';
 297     echo '<option value="DESC"' . $sel . '>' . $strDescending . '</option>' . "\n";
 298     ?>
 299             </select>
 300         </td>
 301     <?php
 302     $z++;
 303     echo "\n";
 304 } // end for
 305 ?>
 306     </tr>
 307
 308     <!-- Show row -->
 309     <tr>
 310         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $cfg['ThBgcolor']; ?>">
 311             <b><?php echo $strShow; ?>&nbsp;:&nbsp;</b>
 312         </td>
 313 <?php
 314 $z = 0;
 315 for ($x = 0; $x < $col; $x++) {
 316     if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
 317         ?>
 318         <td align="center" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
 319             <input type="checkbox" name="Show[<?php echo $z; ?>]" />
 320         </td>
 321         <?php
 322         $z++;
 323     } // end if
 324     echo "\n";
 325
 326     if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
 327         continue;
 328     }
 329     if (isset($Show[$x])) {
 330         $checked     = ' checked="checked"';
 331         $curShow[$z] = $Show[$x];
 332     } else {
 333         $checked     =  '';
 334     }
 335     ?>
 336         <td align="center" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
 337             <input type="checkbox" name="Show[<?php echo $z; ?>]"<?php echo $checked; ?> />
 338         </td>
 339     <?php
 340     $z++;
 341     echo "\n";
 342 } // end for
 343 ?>
 344     </tr>
 345
 346     <!-- Criteria row -->
 347     <tr>
 348         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $cfg['ThBgcolor']; ?>">
 349             <b><?php echo $strCriteria; ?>&nbsp;:&nbsp;</b>
 350         </td>
 351 <?php
 352 $z = 0;
 353 for ($x = 0; $x < $col; $x++) {
 354     if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
 355         ?>
 356         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
 357             <input type="text" name="Criteria[<?php echo $z; ?>]" value="" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
 358         </td>
 359         <?php
 360         $z++;
 361     } // end if
 362     echo "\n";
 363
 364     if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
 365         continue;
 366     }
 367     if (isset($Criteria[$x])) {
 368         if (get_magic_quotes_gpc()) {
 369             $stripped_Criteria = stripslashes($Criteria[$x]);
 370         } else {
 371             $stripped_Criteria = $Criteria[$x];
 372         }
 373     }
 374     if ((empty($prev_Criteria) || !isset($prev_Criteria[$x]))
 375         || urldecode($prev_Criteria[$x]) != htmlspecialchars($stripped_Criteria)) {
 376         $curCriteria[$z]   = $stripped_Criteria;
 377         $encoded_Criteria  = urlencode($stripped_Criteria);
 378     } else {
 379         $curCriteria[$z]   = urldecode($prev_Criteria[$x]);
 380         $encoded_Criteria  = $prev_Criteria[$x];
 381     }
 382     ?>
 383         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
 384             <input type="hidden" name="prev_Criteria[<?php echo $z; ?>]" value="<?php echo $encoded_Criteria; ?>" />
 385             <input type="text" name="Criteria[<?php echo $z; ?>]" value="<?php echo htmlspecialchars($stripped_Criteria); ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
 386         </td>
 387     <?php
 388     $z++;
 389     echo "\n";
 390 } // end for
 391 ?>
 392     </tr>
 393
 394     <!-- And/Or columns and rows -->
 395 <?php
 396 $w = 0;
 397 for ($y = 0; $y <= $row; $y++) {
 398     $bgcolor = ($y % 2) ? $cfg['BgcolorOne'] : $cfg['BgcolorTwo'];
 399     if (isset($InsRow[$y]) && $InsRow[$y] == 'on') {
 400         $chk['or']  = ' checked="checked"';
 401         $chk['and'] = '';
 402         ?>
 403     <tr>
 404         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $bgcolor; ?>" nowrap="nowrap">
 405             <!-- Row controls -->
 406             <table bgcolor="<?php echo $bgcolor; ?>">
 407             <tr>
 408                 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 409                     <small><?php echo $strQBEIns; ?>&nbsp;:</small>
 410                     <input type="checkbox" name="InsRow[<?php echo $w; ?>]" />
 411                 </td>
 412                 <td align="<?php echo $cell_align_right; ?>">
 413                     <b><?php echo $strAnd; ?>&nbsp;:</b>
 414                 </td>
 415                 <td>
 416                     <input type="radio" name="AndOrRow[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
 417                     &nbsp;
 418                 </td>
 419             </tr>
 420             <tr>
 421                 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 422                     <small><?php echo $strQBEDel; ?>&nbsp;:</small>
 423                     <input type="checkbox" name="DelRow[<?php echo $w; ?>]" />
 424                 </td>
 425                 <td align="<?php echo $cell_align_right; ?>">
 426                     <b><?php echo $strOr; ?>&nbsp;:</b>
 427                 </td>
 428                 <td>
 429                     <input type="radio" name="AndOrRow[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
 430                     &nbsp;
 431                 </td>
 432             </tr>
 433             </table>
 434         </td>
 435         <?php
 436         $z = 0;
 437         for ($x = 0; $x < $col; $x++) {
 438             if ($InsCol[$x] == 'on') {
 439                 echo "\n";
 440                 $or = 'Or' . $w . '[' . $z . ']';
 441                 ?>
 442         <td align="center" bgcolor="<?php echo $bgcolor; ?>">
 443             <textarea cols="20" rows="2" style="width: <?php echo $realwidth; ?>" name="<?php echo $or; ?>" dir="<?php echo $text_dir; ?>"></textarea>
 444         </td>
 445                 <?php
 446                 $z++;
 447             } // end if
 448             if ($DelCol[$x] == 'on') {
 449                 continue;
 450             }
 451
 452             echo "\n";
 453             $or = 'Or' . $w . '[' . $z . ']';
 454             ?>
 455         <td align="center" bgcolor="<?php echo $bgcolor; ?>">
 456             <textarea cols="20" rows="2" style="width: <?php echo $realwidth; ?>" name="<?php echo $or; ?>" dir="<?php echo $text_dir; ?>"></textarea>
 457         </td>
 458             <?php
 459             $z++;
 460         } // end for
 461         $w++;
 462         echo "\n";
 463         ?>
 464     </tr>
 465         <?php
 466     } // end if
 467
 468     if (isset($DelRow[$y]) && $DelRow[$y] == 'on') {
 469         continue;
 470     }
 471
 472     if (isset($AndOrRow[$y])) {
 473         $curAndOrRow[$w] = $AndOrRow[$y];
 474     }
 475     if (isset($AndOrRow[$y]) && $AndOrRow[$y] == 'and') {
 476         $chk['and'] =  ' checked="checked"';
 477         $chk['or']  =  '';
 478     } else {
 479         $chk['or']  =  ' checked="checked"';
 480         $chk['and'] =  '';
 481     }
 482     echo "\n";
 483     ?>
 484     <tr>
 485         <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 486             <!-- Row controls -->
 487             <table bgcolor="<?php echo $bgcolor; ?>">
 488             <tr>
 489                 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 490                     <small><?php echo $strQBEIns; ?>&nbsp;:</small>
 491                     <input type="checkbox" name="InsRow[<?php echo $w; ?>]" />
 492                 </td>
 493                 <td align="<?php echo $cell_align_right; ?>">
 494                     <b><?php echo $strAnd; ?>&nbsp;:</b>
 495                 </td>
 496                 <td>
 497                     <input type="radio" name="AndOrRow[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
 498                 </td>
 499             </tr>
 500             <tr>
 501                 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 502                     <small><?php echo $strQBEDel; ?>&nbsp;:</small>
 503                     <input type="checkbox" name="DelRow[<?php echo $w; ?>]" />
 504                 </td>
 505                 <td align="<?php echo $cell_align_right; ?>">
 506                     <b><?php echo $strOr; ?>&nbsp;:</b>
 507                 </td>
 508                 <td>
 509                     <input type="radio" name="AndOrRow[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
 510                 </td>
 511             </tr>
 512             </table>
 513         </td>
 514     <?php
 515     $z = 0;
 516     for ($x = 0; $x < $col; $x++) {
 517         if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
 518             echo "\n";
 519             $or = 'Or' . $w . '[' . $z . ']';
 520             ?>
 521         <td align="center" bgcolor="<?php echo $bgcolor; ?>">
 522             <textarea cols="20" rows="2" style="width: <?php echo $realwidth; ?>" name="<?php echo $or; ?>" dir="<?php echo $text_dir; ?>"></textarea>
 523         </td>
 524             <?php
 525             $z++;
 526         } // end if
 527         if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
 528             continue;
 529         }
 530
 531         echo "\n";
 532         $or = 'Or' . $y;
 533         if (!isset(${$or})) {
 534             ${$or} = '';
 535         }
 536         if (!empty(${$or}) && isset(${$or}[$x])) {
 537             if (get_magic_quotes_gpc()) {
 538                 $stripped_or = stripslashes(${$or}[$x]);
 539             } else {
 540                 $stripped_or = ${$or}[$x];
 541             }
 542         } else {
 543             $stripped_or     = '';
 544         }
 545         ?>
 546         <td align="center" bgcolor="<?php echo $bgcolor; ?>">
 547             <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>
 548         </td>
 549         <?php
 550         if (!empty(${$or}) && isset(${$or}[$x])) {
 551             ${'cur' . $or}[$z] = ${$or}[$x];
 552         }
 553         $z++;
 554     } // end for
 555     $w++;
 556     echo "\n";
 557     ?>
 558     </tr>
 559     <?php
 560     echo "\n";
 561 } // end for
 562 ?>
 563
 564     <!-- Modify columns -->
 565     <tr>
 566         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $cfg['ThBgcolor']; ?>">
 567             <b><?php echo $strModify; ?>&nbsp;:&nbsp;</b>
 568         </td>
 569 <?php
 570 $z = 0;
 571 for ($x = 0; $x < $col; $x++) {
 572     if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
 573         $curAndOrCol[$z] = $AndOrCol[$y];
 574         if ($AndOrCol[$z] == 'or') {
 575             $chk['or']  = ' checked="checked"';
 576             $chk['and'] = '';
 577         } else {
 578             $chk['and'] = ' checked="checked"';
 579             $chk['or']  = '';
 580         }
 581         ?>
 582         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
 583             <b><?php echo $strOr; ?>&nbsp;:</b>
 584             <input type="radio" name="AndOrCol[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
 585             &nbsp;&nbsp;<b><?php echo $strAnd; ?>&nbsp;:</b>
 586             <input type="radio" name="AndOrCol[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
 587             <br />
 588             <?php echo $strQBEIns . "\n"; ?>
 589             <input type="checkbox" name="InsCol[<?php echo $z; ?>]" />
 590             &nbsp;&nbsp;<?php echo $strQBEDel . "\n"; ?>
 591             <input type="checkbox" name="DelCol[<?php echo $z; ?>]" />
 592         </td>
 593         <?php
 594         $z++;
 595     } // end if
 596     echo "\n";
 597
 598     if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
 599         continue;
 600     }
 601
 602     if (isset($AndOrCol[$y])) {
 603         $curAndOrCol[$z] = $AndOrCol[$y];
 604     }
 605     if (isset($AndOrCol[$z]) && $AndOrCol[$z] == 'or') {
 606         $chk['or']  = ' checked="checked"';
 607         $chk['and'] = '';
 608     } else {
 609         $chk['and'] = ' checked="checked"';
 610         $chk['or']  = '';
 611     }
 612     ?>
 613         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
 614             <b><?php echo $strOr; ?>&nbsp;:</b>
 615             <input type="radio" name="AndOrCol[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
 616             &nbsp;&nbsp;<b><?php echo $strAnd; ?>&nbsp;:</b>
 617             <input type="radio" name="AndOrCol[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
 618             <br />
 619             <?php echo $strQBEIns . "\n"; ?>
 620             <input type="checkbox" name="InsCol[<?php echo $z; ?>]" />
 621             &nbsp;&nbsp;<?php echo $strQBEDel . "\n"; ?>
 622             <input type="checkbox" name="DelCol[<?php echo $z; ?>]" />
 623         </td>
 624     <?php
 625     $z++;
 626     echo "\n";
 627 } // end for
 628 ?>
 629     </tr>
 630     </table>
 631
 632
 633     <!-- Other controls -->
 634     <table border="0">
 635     <tr>
 636         <td valign="top">
 637             <table border="0" align="<?php echo $cell_align_left; ?>">
 638             <tr>
 639                 <td rowspan="4" valign="top">
 640                     <?php echo $strUseTables; ?>&nbsp;:
 641                     <br />
 642                     <select name="TableList[]" size="7" multiple="multiple">
 643 <?php
 644 while (list($key, $val) = each($tbl_names)) {
 645     echo '                        ';
 646     echo '<option value="' . urlencode($key) . '"' . $val . '>' . htmlspecialchars($key) . '</option>' . "\n";
 647 }
 648 ?>
 649                     </select>
 650                 </td>
 651                 <td align="<?php echo $cell_align_right; ?>" valign="bottom">
 652                     <input type="hidden" value="<?php echo htmlspecialchars($db); ?>" name="db" />
 653                     <input type="hidden" value="<?php echo $z; ?>" name="Columns" />
 654 <?php
 655 $w--;
 656 ?>
 657                     <input type="hidden" value="<?php echo $w; ?>" name="Rows" />
 658                     <?php echo $strAddDeleteRow; ?>&nbsp;:
 659                     <select size="1" name="Add_Row">
 660                         <option value="-3">-3</option>
 661                         <option value="-2">-2</option>
 662                         <option value="-1">-1</option>
 663                         <option value="0" selected="selected">0</option>
 664                         <option value="1">1</option>
 665                         <option value="2">2</option>
 666                         <option value="3">3</option>
 667                     </select>
 668                 </td>
 669             </tr>
 670             <tr>
 671                 <td align="<?php echo $cell_align_right; ?>" valign="bottom">
 672                     <?php echo $strAddDeleteColumn; ?>&nbsp;:
 673                     <select size="1" name="Add_Col">
 674                         <option value="-3">-3</option>
 675                         <option value="-2">-2</option>
 676                         <option value="-1">-1</option>
 677                         <option value="0" selected="selected">0</option>
 678                         <option value="1">1</option>
 679                         <option value="2">2</option>
 680                         <option value="3">3</option>
 681                     </select>
 682                 </td>
 683             </tr>
 684             <!-- Generates a query -->
 685             <tr align="center" valign="top">
 686                 <td>
 687                     <input type="submit" name="modify" value="<?php echo $strUpdateQuery; ?>" />
 688                     <?php echo PMA_generate_common_hidden_inputs(); ?>
 689                 </td>
 690             </tr>
 691             <!-- Executes a query -->
 692             <tr align="center" valign="top">
 693                 <td>
 694                     <input type="submit" name="submit_sql" value="<?php echo $strRunQuery; ?>" />
 695                 </td>
 696             </tr>
 697             </table>
 698         </td>
 699         <td>
 700             <!-- Displays the current query -->
 701             <?php echo sprintf($strQueryOnDb, htmlspecialchars($db)); ?><br />
 702 <textarea cols="30" rows="7" name="sql_query" dir="<?php echo $text_dir; ?>">
 703 <?php
 704 // 1. SELECT
 705 $last_select = 0;
 706 $encoded_qry = '';
 707 if (!isset($qry_select)) {
 708     $qry_select         = '';
 709 }
 710 for ($x = 0; $x < $col; $x++) {
 711     if (!empty($curField[$x]) && isset($curShow[$x]) && $curShow[$x] == 'on') {
 712         if ($last_select) {
 713             $qry_select .=  ', ';
 714         }
 715         $qry_select     .= $curField[$x];
 716         $last_select    = 1;
 717     }
 718 } // end for
 719 if (!empty($qry_select)) {
 720     $encoded_qry .= urlencode('SELECT ' . $qry_select . "\n");
 721     echo  'SELECT ' . htmlspecialchars($qry_select) . "\n";
 722 }
 723
 724 // 2. FROM
 725
 726 // Create LEFT JOINS out of Relations
 727 // Code originally by Mike Beck <mike.beck@ibmiller.de>
 728 // If we can use Relations we could make some left joins.
 729 // First find out if relations are available in this database.
 730
 731 // Debugging:
 732 //echo '</textarea><pre style="background-color: white;">';
 733 // First we need the really needed Tables - those in TableList might still be
 734 // all Tables.
 735 if (isset($Field) && count($Field) > 0) {
 736
 737     // Initialize some variables
 738     $tab_all    = array();
 739     $col_all    = array();
 740     $tab_wher   = array();
 741     $tab_know   = array();
 742     $tab_left   = array();
 743     $col_where  = array();
 744     $fromclause = '';
 745
 746     // We only start this if we have fields, otherwise it would be dumb
 747 // echo "get everything\n";
 748     while (list(, $value) = each($Field)) {
 749         $parts             = explode('.', $value);
 750         if (!empty($parts[0]) && !empty($parts[1])) {
 751             $tab_raw       = urldecode($parts[0]);
 752             $tab           = str_replace('`', '', $tab_raw);
 753 // echo 'new Tab: ' . $tab . "\n";
 754             $tab_all[$tab] = $tab;
 755             $col_raw       = urldecode($parts[1]);
 756             $col_all[]     = $tab . '.' . str_replace('`', '', $col_raw);
 757 // echo 'new col: ' . $tab . '.' . str_replace('`', '', $col_raw) . "\n";
 758          }
 759     } // end while
 760
 761 // echo "check where clauses\n";
 762     if ($cfgRelation['relwork'] && count($tab_all) > 0) {
 763         // Now we need all tables that we have in the where clause
 764         $crit_cnt         = count($Criteria);
 765         for ($x = 0; $x < $crit_cnt; $x++) {
 766             $curr_tab     = explode('.', urldecode($Field[$x]));
 767             if (!empty($curr_tab[0]) && !empty($curr_tab[1])) {
 768                 $tab_raw  = urldecode($curr_tab[0]);
 769                 $tab      = str_replace('`', '', $tab_raw);
 770
 771                 $col_raw  = urldecode($curr_tab[1]);
 772                 $col1     = str_replace('`', '', $col_raw);
 773                 $col1     = $tab . '.' . $col1;
 774                 // Now we know that our array has the same numbers as $Criteria
 775                 // we can check which of our columns has a where clause
 776                 if (!empty($Criteria[$x])) {
 777                     if (substr($Criteria[$x], 0, 1) == '=' || eregi('is', $Criteria[$x])) {
 778                         $col_where[$col] = $col1;
 779                         $tab_wher[$tab]  = $tab;
 780 // echo 'new where clause: ' . $tab_wher[$tab] . "||\n";
 781                     }
 782                 } // end if
 783             } // end if
 784         } // end for
 785
 786         // Cleans temp vars w/o further use
 787         unset($tab_raw);
 788         unset($col_raw);
 789         unset($col1);
 790
 791         if (count($tab_wher) == 1) {
 792             // If there is exactly one column that has a decent where-clause
 793             // we will just use this
 794             $master = key($tab_wher);
 795 // echo 'nur ein where: master = ' .$master . "||\n";
 796         } else {
 797             // Now let's find out which of the tables has an index
 798 // echo "prüfe indexe:\n";
 799             while (list(, $tab) = each($tab_all)) {
 800                 $ind_qry  = 'SHOW INDEX FROM ' . PMA_backquote($tab);
 801                 $ind_rs   = PMA_mysql_query($ind_qry);
 802                 while ($ind = PMA_mysql_fetch_array($ind_rs)) {
 803                     $col1 = $tab . '.' . $ind['Column_name'];
 804                     if (isset($col_all[$col1])) {
 805                         if ($ind['non_unique'] == 0) {
 806                             if (isset($col_where[$col1])) {
 807                                 $col_unique[$col1] = 'Y';
 808                             } else {
 809                                 $col_unique[$col1] = 'N';
 810                             }
 811 //echo 'neuen unique index gefunden: ' . $col . "\n";
 812                         } else {
 813                             if (isset($col_where[$col1])) {
 814                                 $col_index[$col1] = 'Y';
 815                             } else {
 816                                 $col_index[$col1] = 'N';
 817                             }
 818 //echo 'neuen index gefunden: ' . $col . "\n";
 819                         }
 820                     }
 821                 } // end while (each col of tab)
 822             } // end while (each tab)
 823             // now we want to find the best.
 824             if (isset($col_unique) && count($col_unique) > 0) {
 825                 $col_cand = $col_unique;
 826 //echo "Kandidaten sind jetzt alle mit unique index\n";
 827                 $needsort = 1;
 828             } else if (isset($col_index) && count($col_index) > 0) {
 829                 $col_cand = $col_index;
 830                 $needsort = 1;
 831 //echo "Kandidaten sind jetzt alle mit index\n";
 832             } else if (isset($col_where) && count($col_where) > 0) {
 833                 $col_cand = $tab_wher;
 834 //echo "Kandidaten sind jetzt alle im whereclause\n";
 835                 $needsort = 0;
 836             } else {
 837                 $col_cand = $tab_all;
 838                 $needsort = 0;
 839 //echo "Kandidaten sind jetzt alle \n";
 840             }
 841
 842             // If we came up with $col_unique (very good) or $col_index (still
 843             // good) as $col_cand we want to check if we have any 'Y' there
 844             // (that would mean that they were also found in the whereclauses
 845             // which would be great). if yes, we take only those
 846             if ($needsort == 1) {
 847                 while (list($col, $is_where) = each($col_cand)) {
 848                     $tab           = explode('.', $col);
 849                     $tab           = $tab[0];
 850                     if ($is_where == 'Y') {
 851                         $vg[$col]  = $tab;
 852                     } else {
 853                         $sg[$col]  = $tab;
 854                     }
 855                 }
 856                 if (isset($vg)) {
 857                     $col_cand      = $vg;
 858 //echo "Kandidaten konnten auf index+where beschränkt werden\n";
 859                 } else {
 860                     $col_cand      = $sg;
 861 //echo "keiner der Kandidaten mit Index ist im wherclause\n";
 862                 }
 863             }
 864
 865             // If our array of candidates has more than one member we'll just
 866             // find the smallest table.
 867             // Of course the actual query would be faster if we check for
 868             // the Criteria which gives the smallest result set in its table,
 869             // but it would take too much time to check this
 870             if (count($col_cand) > 1) {
 871 //echo "wir haben immer noch mehr als einen Kandidaten. Prüfe Größe\n";
 872                 // Of course we only want to check each table once
 873                 $checked_tables = $col_cand;
 874                 while (list(, $tab) = each($col_cand)) {
 875                     if ($checked_tables[$tab] != 1 ) {
 876 //echo 'prüfe jetzt: Tabelle ' . $tab . "\n";
 877                         $rows_qry = 'SELECT COUNT(1) AS anz '
 878                                   . 'FROM ' . PMA_backquote($tab);
 879                         $rows_rs  = PMA_mysql_query($rows_qry);
 880                         while ($res = PMA_mysql_fetch_array($rows_rs)) {
 881                             $tsize[$tab] = $res['anz'];
 882 //echo "$tab hat: "  . $tsize[$tab] . "\n";
 883                         }
 884                         $checked_tables[$tab] = 1;
 885                     }
 886                     $csize[$tab] = $tsize[$tab];
 887 //echo 'erster csize: ' . $csize[$tab] . "\n";
 888                 }
 889                 asort($csize);
 890                 reset($csize);
 891                 $master = key($csize);
 892 //echo 'kleinste Datei: ' . $master . "\n";
 893             } else {
 894                 //$master = $col_cand[0];
 895                 reset($col_cand);
 896                 $master = current($col_cand);
 897 //echo 'master ist der einzige Kandidat: ' . $master . "\n";
 898             }
 899         } // end if (exactly one where clause)
 900 //echo 'ich habe mich entschieden: ' . $master;
 901 //die;
 902
 903
 904         /**
 905          * Removes unwanted entries from an array (PHP3 compliant)
 906          *
 907          * @param   array  the array to work with
 908          * @param   array  the list of keys to remove
 909          *
 910          * @return  array  the cleaned up array
 911          *
 912          * @access  private
 913          */
 914         function PMA_arrayShort($array, $key)
 915         {
 916             while (list($k, $v) = each($array)) {
 917                 if ($k != $key) {
 918                     $reta[$k] = $v;
 919                 }
 920             }
 921             if (!isset($reta)) {
 922                 $reta = array();
 923             }
 924
 925             return $reta;
 926         } // end of the "PMA_arrayShort()" function
 927
 928
 929         /**
 930          * Finds all related tables
 931          *
 932          * @param   string   wether to go from master to foreign or vice versa
 933          *
 934          * @return  boolean  always TRUE
 935          *
 936          * @global  array    the list of tables that we still couldn't connect
 937          * @global  array    the list of allready connected tables
 938          * @global  string   the current databse name
 939          * @global  string   the super user connection id
 940          * @global  array    the list of relation settings
 941          *
 942          * @access  private
 943          */
 944         function PMA_getRelatives($from) {
 945             global $tab_left, $tab_know, $fromclause;
 946             global $dbh, $db, $cfgRelation;
 947
 948             if ($from == 'master') {
 949                 $to    = 'foreign';
 950             } else {
 951                 $to    = 'master';
 952             }
 953             $in_know = '(\'' . implode('\', \'', $tab_know) . '\')';
 954             $in_left = '(\'' . implode('\', \'', $tab_left) . '\')';
 955
 956             $rel_query = 'SELECT *'
 957                        . ' FROM ' . PMA_backquote($cfgRelation['relation'])
 958                        . ' WHERE ' . $from . '_db   = \'' . PMA_sqlAddslashes($db) . '\''
 959                        . ' AND ' . $to   . '_db   = \'' . PMA_sqlAddslashes($db) . '\''
 960                        . ' AND ' . $from . '_table IN ' . $in_know
 961                        . ' AND ' . $to   . '_table IN ' . $in_left;
 962             if (isset($dbh)) {
 963                 PMA_mysql_select_db($cfgRelation['db'], $dbh);
 964                 $relations = @PMA_mysql_query($rel_query, $dbh) or PMA_mysqlDie(PMA_mysql_error($dbh), $rel_query, '', $err_url_0);
 965                 PMA_mysql_select_db($db, $dbh);
 966             } else {
 967                 PMA_mysql_select_db($cfgRelation['db']);
 968                 $relations = @PMA_mysql_query($rel_query) or PMA_mysqlDie('', $rel_query, '', $err_url_0);
 969                 PMA_mysql_select_db($db);
 970             }
 971             while ($row = PMA_mysql_fetch_array($relations)) {
 972                 $found_table                = $row[$to . '_table'];
 973                 if (isset($tab_left[$found_table])) {
 974                     $fromclause             .= "\n" . ' LEFT JOIN '
 975                                             . PMA_backquote($row[$to . '_table']) . ' ON '
 976                                             . PMA_backquote($row[$from . '_table']) . '.'
 977                                             . PMA_backquote($row[$from . '_field']) . ' = '
 978                                             . PMA_backquote($row[$to . '_table']) . '.'
 979                                             . PMA_backquote($row[$to . '_field']) . ' ';
 980                     $tab_know[$found_table] = $found_table;
 981                     $tab_left               = PMA_arrayShort($tab_left, $found_table);
 982                 }
 983             } // end while
 984
 985             return TRUE;
 986         } // end of the "PMA_getRelatives()" function
 987
 988
 989         $tab_left          = PMA_arrayShort($tab_all, $master);
 990         $tab_know[$master] = $master;
 991
 992         $run   = 0;
 993         $emerg = '';
 994         while (count($tab_left) > 0) {
 995             if ($run % 2 == 0) {
 996                 PMA_getRelatives('master');
 997             } else {
 998                 PMA_getRelatives('foreign');
 999             }
1000             $run++;
1001             if ($run > 5) {
1002
1003                 while (list(, $tab) = each($tab_left)) {
1004                     $emerg    .= ', ' . $tab;
1005                     $tab_left = PMA_arrayShort($tab_left, $tab);
1006                 }
1007             }
1008         } // end while
1009         $qry_from = $master . $emerg . $fromclause;
1010     } // end if ($cfgRelation['relwork'] && count($tab_all) > 0)
1011
1012 } // end count($Field) > 0
1013
1014 // Now let's see what we got
1015 if (!empty($qry_from)) {
1016     $encoded_qry  .= urlencode('FROM ' . $qry_from . "\n");
1017     echo 'FROM ' . htmlspecialchars($qry_from) . "\n";
1018 }
1019
1020 // 3. WHERE
1021 $qry_where          = '';
1022 $criteria_cnt       = 0;
1023 for ($x = 0; $x < $col; $x++) {
1024     if (!empty($curField[$x]) && !empty($curCriteria[$x]) && $x && isset($last_where)) {
1025         $qry_where  .= ' ' . strtoupper($curAndOrCol[$last_where]) . ' ';
1026     }
1027     if (!empty($curField[$x]) && !empty($curCriteria[$x])) {
1028         $qry_where  .= '(' . $curField[$x] . ' ' . $curCriteria[$x] . ')';
1029         $last_where = $x;
1030         $criteria_cnt++;
1031     }
1032 } // end for
1033 if ($criteria_cnt > 1) {
1034     $qry_where      = '(' . $qry_where . ')';
1035 }
1036 // OR rows ${'cur' . $or}[$x]
1037 if (!isset($curAndOrRow)) {
1038     $curAndOrRow          = array();
1039 }
1040 for ($y = 0; $y <= $row; $y++) {
1041     $criteria_cnt         = 0;
1042     $qry_orwhere          = '';
1043     $last_orwhere         = '';
1044     for ($x = 0; $x < $col; $x++) {
1045         if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x]) && $x) {
1046             $qry_orwhere  .= ' ' . strtoupper($curAndOrCol[$last_orwhere]) . ' ';
1047         }
1048         if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x])) {
1049             $qry_orwhere  .= '(' . $curField[$x]
1050                           .  ' '
1051                           .  (get_magic_quotes_gpc() ? stripslashes(${'curOr' . $y}[$x]) : ${'curOr' . $y}[$x])
1052                           .  ')';
1053             $last_orwhere = $x;
1054             $criteria_cnt++;
1055         }
1056     } // end for
1057     if ($criteria_cnt > 1) {
1058         $qry_orwhere      = '(' . $qry_orwhere . ')';
1059     }
1060     if (!empty($qry_orwhere)) {
1061         $qry_where .= "\n"
1062                    .  strtoupper(isset($curAndOrRow[$y]) ? $curAndOrRow[$y] . ' ' : '')
1063                    .  $qry_orwhere;
1064     } // end if
1065 } // end for
1066
1067 if (!empty($qry_where) && $qry_where != '()') {
1068     $encoded_qry .= urlencode('WHERE ' . $qry_where . "\n");
1069     echo 'WHERE ' . htmlspecialchars($qry_where) . "\n";
1070 } // end if
1071
1072 // 4. ORDER BY
1073 $last_orderby = 0;
1074 if (!isset($qry_orderby)) {
1075     $qry_orderby      = '';
1076 }
1077 for ($x = 0; $x < $col; $x++) {
1078     if ($last_orderby && $x && !empty($curField[$x]) && !empty($curSort[$x])) {
1079         $qry_orderby  .=  ', ';
1080     }
1081     if (!empty($curField[$x]) && !empty($curSort[$x])) {
1082         // if they have chosen all fields using the * selector,
1083         // then sorting is not available
1084         // Robbat2 - Fix for Bug #570698
1085         if (substr($curField[$x], -2) != '.*') {
1086             $qry_orderby  .=  $curField[$x] . ' ' . $curSort[$x];
1087             $last_orderby = 1;
1088         }
1089     }
1090 } // end for
1091 if (!empty($qry_orderby)) {
1092     $encoded_qry .= urlencode('ORDER BY ' . $qry_orderby);
1093     echo 'ORDER BY ' . htmlspecialchars($qry_orderby) . "\n";
1094 }
1095 ?>
1096 </textarea>
1097             <input type="hidden" name="encoded_sql_query" value="<?php echo $encoded_qry; ?>" />
1098         </td>
1099     </tr>
1100     </table>
1101
1102 </form>
1103
1104
1105 <?php
1106 /**
1107  * Displays the footer
1108  */
1109 require('./footer.inc.php3');
1110 ?>