db_details_qbe.php

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