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