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