db_qbe.php

   1 <?php
   2 /* vim: set expandtab sw=4 ts=4 sts=4: */
   3 /**
   4  * query by example the whole database
   5  *
   6  * @version $Id$
   7  * @package phpMyAdmin
   8  */
   9
  10 /**
  11  * requirements
  12  */
  13 require_once './libraries/common.inc.php';
  14 require_once './libraries/Table.class.php';
  15 require_once './libraries/relation.lib.php';
  16
  17
  18 /**
  19  * Gets the relation settings
  20  */
  21 $cfgRelation = PMA_getRelationsParam();
  22
  23
  24 /**
  25  * A query has been submitted -> execute it, else display the headers
  26  */
  27 if (isset($_REQUEST['submit_sql']) && ! empty($sql_query)) {
  28     $goto      = 'db_sql.php';
  29     $zero_rows = htmlspecialchars(__('Your SQL query has been executed successfully'));
  30     require './sql.php';
  31     exit;
  32 } else {
  33     $sub_part  = '_qbe';
  34     require './libraries/db_common.inc.php';
  35     $url_query .= '&amp;goto=db_qbe.php';
  36     $url_params['goto'] = 'db_qbe.php';
  37     require './libraries/db_info.inc.php';
  38 }
  39
  40 if (isset($_REQUEST['submit_sql'])
  41  && ! preg_match('@^SELECT@i', $sql_query)) {
  42     PMA_Message::warning('strHaveToShow')->display();
  43 }
  44
  45
  46 /**
  47  * Initialize some variables
  48  */
  49 $col_cnt = PMA_ifSetOr($_REQUEST['col_cnt'], 3, 'numeric');
  50 $add_col = PMA_ifSetOr($_REQUEST['add_col'], 0, 'numeric');
  51 $add_row = PMA_ifSetOr($_REQUEST['add_row'], 0, 'numeric');
  52
  53 $rows    = PMA_ifSetOr($_REQUEST['rows'],    0, 'numeric');
  54 $ins_col = PMA_ifSetOr($_REQUEST['ins_col'], null, 'array');
  55 $del_col = PMA_ifSetOr($_REQUEST['del_col'], null, 'array');
  56
  57 $prev_criteria = isset($_REQUEST['prev_criteria'])
  58     ? $_REQUEST['prev_criteria']
  59     : array();
  60 $criteria = isset($_REQUEST['criteria'])
  61     ? $_REQUEST['criteria']
  62     : array_fill(0, $col_cnt, '');
  63
  64 $ins_row = isset($_REQUEST['ins_row'])
  65     ? $_REQUEST['ins_row']
  66     : array_fill(0, $col_cnt, '');
  67 $del_row = isset($_REQUEST['del_row'])
  68     ? $_REQUEST['del_row']
  69     : array_fill(0, $col_cnt, '');
  70 $and_or_row = isset($_REQUEST['and_or_row'])
  71     ? $_REQUEST['and_or_row']
  72     : array_fill(0, $col_cnt, '');
  73 $and_or_col = isset($_REQUEST['and_or_col'])
  74     ? $_REQUEST['and_or_col']
  75     : array_fill(0, $col_cnt, '');
  76
  77 // minimum width
  78 $form_column_width = 12;
  79 $col = max($col_cnt + $add_col, 0);
  80 $row = max($rows + $add_row, 0);
  81
  82
  83 // The tables list sent by a previously submitted form
  84 if (PMA_isValid($_REQUEST['TableList'], 'array')) {
  85     foreach ($_REQUEST['TableList'] as $each_table) {
  86         $tbl_names[$each_table] = ' selected="selected"';
  87     }
  88 } // end if
  89
  90
  91 // this was a work in progress, deactivated for now
  92 //$columns = PMA_DBI_get_columns_full($GLOBALS['db']);
  93 //$tables  = PMA_DBI_get_columns_full($GLOBALS['db']);
  94
  95
  96 /**
  97  * Prepares the form
  98  */
  99 $tbl_result     = PMA_DBI_query('SHOW TABLES FROM ' . PMA_backquote($db) . ';', null, PMA_DBI_QUERY_STORE);
 100 $tbl_result_cnt = PMA_DBI_num_rows($tbl_result);
 101 if (0 == $tbl_result_cnt) {
 102     PMA_Message::error('strNoTablesFound')->display();
 103     require_once './libraries/footer.inc.php';
 104     exit;
 105 }
 106
 107 // The tables list gets from MySQL
 108 while (list($tbl) = PMA_DBI_fetch_row($tbl_result)) {
 109     $fld_results = PMA_DBI_get_fields($db, $tbl);
 110
 111     if (empty($tbl_names[$tbl]) && !empty($_REQUEST['TableList'])) {
 112         $tbl_names[$tbl] = '';
 113     } else {
 114         $tbl_names[$tbl] = ' selected="selected"';
 115     } //  end if
 116
 117     // The fields list per selected tables
 118     if ($tbl_names[$tbl] == ' selected="selected"') {
 119         $each_table = PMA_backquote($tbl);
 120         $fld[]  = $each_table . '.*';
 121         foreach ($fld_results as $each_field) {
 122             $each_field = $each_table . '.' . PMA_backquote($each_field['Field']);
 123             $fld[] = $each_field;
 124
 125             // increase the width if necessary
 126             $form_column_width = max(strlen($each_field), $form_column_width);
 127         } // end foreach
 128     } // end if
 129 } // end while
 130 PMA_DBI_free_result($tbl_result);
 131
 132 // largest width found
 133 $realwidth = $form_column_width . 'ex';
 134
 135
 136 /**
 137  * Displays the Query by example form
 138  */
 139
 140 /**
 141  * Enter description here...
 142  *
 143  * @param array     $columns
 144  * @param integer   $column_number
 145  * @param string    $selected
 146  */
 147 function showColumnSelectCell($columns, $column_number, $selected = '')
 148 {
 149     ?>
 150     <td align="center">
 151         <select name="Field[<?php echo $column_number; ?>]" size="1">
 152             <option value="">&nbsp;</option>
 153     <?php
 154     foreach ($columns as $column) {
 155         if ($column === $selected) {
 156             $sel = ' selected="selected"';
 157         } else {
 158             $sel = '';
 159         }
 160         echo '<option value="' . htmlspecialchars($column) . '"' . $sel . '>'
 161             . str_replace(' ', '&nbsp;', htmlspecialchars($column)) . '</option>' . "\n";
 162     }
 163     ?>
 164         </select>
 165     </td>
 166     <?php
 167 }
 168
 169 ?>
 170 <form action="db_qbe.php" method="post">
 171 <fieldset>
 172 <table class="data" style="width: 100%;">
 173 <tr class="odd noclick">
 174     <th><?php echo __('Field'); ?>:</th>
 175 <?php
 176 $z = 0;
 177 for ($x = 0; $x < $col; $x++) {
 178     if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {
 179         showColumnSelectCell($fld, $z);
 180         $z++;
 181     }
 182
 183     if (! empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
 184         continue;
 185     }
 186
 187     $selected = '';
 188     if (isset($Field[$x])) {
 189         $selected = $Field[$x];
 190         $curField[$z] = $Field[$x];
 191     }
 192     showColumnSelectCell($fld, $z, $selected);
 193     $z++;
 194 } // end for
 195 ?>
 196 </tr>
 197
 198 <!-- Sort row -->
 199 <tr class="even noclick">
 200     <th><?php echo __('Sort'); ?>:</th>
 201 <?php
 202 $z = 0;
 203 for ($x = 0; $x < $col; $x++) {
 204     if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
 205         ?>
 206     <td align="center">
 207         <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
 208             <option value="">&nbsp;</option>
 209             <option value="ASC"><?php echo __('Ascending'); ?></option>
 210             <option value="DESC"><?php echo __('Descending'); ?></option>
 211         </select>
 212     </td>
 213         <?php
 214         $z++;
 215     } // end if
 216     echo "\n";
 217
 218     if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
 219         continue;
 220     }
 221     ?>
 222     <td align="center">
 223         <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
 224             <option value="">&nbsp;</option>
 225     <?php
 226     echo "\n";
 227
 228     // If they have chosen all fields using the * selector,
 229     // then sorting is not available
 230     // Robbat2 - Fix for Bug #570698
 231     if (isset($Sort[$x]) && isset($Field[$x])
 232      && substr($Field[$x], -2) == '.*') {
 233         $Sort[$x] = '';
 234     } //end if
 235
 236     if (isset($Sort[$x]) && $Sort[$x] == 'ASC') {
 237         $curSort[$z] = $Sort[$x];
 238         $sel         = ' selected="selected"';
 239     } else {
 240         $sel         = '';
 241     } // end if
 242     echo '                ';
 243     echo '<option value="ASC"' . $sel . '>' . __('Ascending') . '</option>' . "\n";
 244     if (isset($Sort[$x]) && $Sort[$x] == 'DESC') {
 245         $curSort[$z] = $Sort[$x];
 246         $sel         = ' selected="selected"';
 247     } else {
 248         $sel         = '';
 249     } // end if
 250     echo '                ';
 251     echo '<option value="DESC"' . $sel . '>' . __('Descending') . '</option>' . "\n";
 252     ?>
 253         </select>
 254     </td>
 255     <?php
 256     $z++;
 257     echo "\n";
 258 } // end for
 259 ?>
 260 </tr>
 261
 262 <!-- Show row -->
 263 <tr class="odd noclick">
 264     <th><?php echo __('Show'); ?>:</th>
 265 <?php
 266 $z = 0;
 267 for ($x = 0; $x < $col; $x++) {
 268     if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
 269         ?>
 270     <td align="center">
 271         <input type="checkbox" name="Show[<?php echo $z; ?>]" />
 272     </td>
 273         <?php
 274         $z++;
 275     } // end if
 276     echo "\n";
 277
 278     if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
 279         continue;
 280     }
 281     if (isset($Show[$x])) {
 282         $checked     = ' checked="checked"';
 283         $curShow[$z] = $Show[$x];
 284     } else {
 285         $checked     =  '';
 286     }
 287     ?>
 288     <td align="center">
 289         <input type="checkbox" name="Show[<?php echo $z; ?>]"<?php echo $checked; ?> />
 290     </td>
 291     <?php
 292     $z++;
 293     echo "\n";
 294 } // end for
 295 ?>
 296 </tr>
 297
 298 <!-- Criteria row -->
 299 <tr class="even noclick">
 300     <th><?php echo __('Criteria'); ?>:</th>
 301 <?php
 302 $z = 0;
 303 for ($x = 0; $x < $col; $x++) {
 304     if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
 305         ?>
 306     <td align="center">
 307         <input type="text" name="criteria[<?php echo $z; ?>]" value="" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
 308     </td>
 309         <?php
 310         $z++;
 311     } // end if
 312     echo "\n";
 313
 314     if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
 315         continue;
 316     }
 317     if (isset($criteria[$x])) {
 318         $tmp_criteria = $criteria[$x];
 319     }
 320     if ((empty($prev_criteria) || !isset($prev_criteria[$x]))
 321         || $prev_criteria[$x] != htmlspecialchars($tmp_criteria)) {
 322         $curCriteria[$z]   = $tmp_criteria;
 323     } else {
 324         $curCriteria[$z]   = $prev_criteria[$x];
 325     }
 326     ?>
 327     <td align="center">
 328         <input type="hidden" name="prev_criteria[<?php echo $z; ?>]" value="<?php echo htmlspecialchars($curCriteria[$z]); ?>" />
 329         <input type="text" name="criteria[<?php echo $z; ?>]" value="<?php echo htmlspecialchars($tmp_criteria); ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
 330     </td>
 331     <?php
 332     $z++;
 333     echo "\n";
 334 } // end for
 335 ?>
 336 </tr>
 337
 338 <!-- And/Or columns and rows -->
 339 <?php
 340 $w = 0;
 341 $odd_row = true;
 342 for ($y = 0; $y <= $row; $y++) {
 343     if (isset($ins_row[$y]) && $ins_row[$y] == 'on') {
 344         $chk['or']  = ' checked="checked"';
 345         $chk['and'] = '';
 346         ?>
 347 <tr class="<?php echo $odd_row ? 'odd' : 'even'; ?> noclick">
 348     <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 349         <!-- Row controls -->
 350         <table cellpadding="0" cellspacing="0" border="0">
 351         <tr>
 352             <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 353                 <small><?php echo __('Ins'); ?>:</small>
 354                 <input type="checkbox" name="ins_row[<?php echo $w; ?>]" />
 355             </td>
 356             <td align="<?php echo $cell_align_right; ?>">
 357                 <strong><?php echo __('And'); ?>:</strong>
 358             </td>
 359             <td>
 360                 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
 361                 &nbsp;
 362             </td>
 363         </tr>
 364         <tr>
 365             <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 366                 <small><?php echo __('Del'); ?>:</small>
 367                 <input type="checkbox" name="del_row[<?php echo $w; ?>]" />
 368             </td>
 369             <td align="<?php echo $cell_align_right; ?>">
 370                 <strong><?php echo __('Or'); ?>:</strong>
 371             </td>
 372             <td>
 373                 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
 374                 &nbsp;
 375             </td>
 376         </tr>
 377         </table>
 378     </td>
 379         <?php
 380         $z = 0;
 381         for ($x = 0; $x < $col; $x++) {
 382             if (isset($ins_col[$x]) && $ins_col[$x] == 'on') {
 383                 echo "\n";
 384                 $or = 'Or' . $w . '[' . $z . ']';
 385                 ?>
 386     <td align="center">
 387         <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
 388     </td>
 389                 <?php
 390                 $z++;
 391             } // end if
 392             if (isset($del_col[$x]) && $del_col[$x] == 'on') {
 393                 continue;
 394             }
 395
 396             echo "\n";
 397             $or = 'Or' . $w . '[' . $z . ']';
 398             ?>
 399     <td align="center">
 400         <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
 401     </td>
 402             <?php
 403             $z++;
 404         } // end for
 405         $w++;
 406         echo "\n";
 407         ?>
 408 </tr>
 409         <?php
 410         $odd_row =! $odd_row;
 411     } // end if
 412
 413     if (isset($del_row[$y]) && $del_row[$y] == 'on') {
 414         continue;
 415     }
 416
 417     if (isset($and_or_row[$y])) {
 418         $curAndOrRow[$w] = $and_or_row[$y];
 419     }
 420     if (isset($and_or_row[$y]) && $and_or_row[$y] == 'and') {
 421         $chk['and'] =  ' checked="checked"';
 422         $chk['or']  =  '';
 423     } else {
 424         $chk['or']  =  ' checked="checked"';
 425         $chk['and'] =  '';
 426     }
 427     echo "\n";
 428     ?>
 429 <tr class="<?php echo $odd_row ? 'odd' : 'even'; ?> noclick">
 430     <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 431         <!-- Row controls -->
 432         <table border="0" cellpadding="0" cellspacing="0">
 433         <tr>
 434             <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 435                 <small><?php echo __('Ins'); ?>:</small>
 436                 <input type="checkbox" name="ins_row[<?php echo $w; ?>]" />
 437             </td>
 438             <td align="<?php echo $cell_align_right; ?>">
 439                 <strong><?php echo __('And'); ?>:</strong>
 440             </td>
 441             <td>
 442                 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
 443             </td>
 444         </tr>
 445         <tr>
 446             <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
 447                 <small><?php echo __('Del'); ?>:</small>
 448                 <input type="checkbox" name="del_row[<?php echo $w; ?>]" />
 449             </td>
 450             <td align="<?php echo $cell_align_right; ?>">
 451                 <strong><?php echo __('Or'); ?>:</strong>
 452             </td>
 453             <td>
 454                 <input type="radio" name="and_or_row[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
 455             </td>
 456         </tr>
 457         </table>
 458     </td>
 459     <?php
 460     $z = 0;
 461     for ($x = 0; $x < $col; $x++) {
 462         if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
 463             echo "\n";
 464             $or = 'Or' . $w . '[' . $z . ']';
 465             ?>
 466     <td align="center">
 467         <input type="text" name="Or<?php echo $or; ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
 468     </td>
 469             <?php
 470             $z++;
 471         } // end if
 472         if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
 473             continue;
 474         }
 475
 476         echo "\n";
 477         $or = 'Or' . $y;
 478         if (!isset(${$or})) {
 479             ${$or} = '';
 480         }
 481         if (!empty(${$or}) && isset(${$or}[$x])) {
 482             $tmp_or = ${$or}[$x];
 483         } else {
 484             $tmp_or     = '';
 485         }
 486         ?>
 487     <td align="center">
 488         <input type="text" name="Or<?php echo $w . '[' . $z . ']'; ?>" value="<?php echo htmlspecialchars($tmp_or); ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
 489     </td>
 490         <?php
 491         if (!empty(${$or}) && isset(${$or}[$x])) {
 492             ${'cur' . $or}[$z] = ${$or}[$x];
 493         }
 494         $z++;
 495     } // end for
 496     $w++;
 497     echo "\n";
 498     ?>
 499 </tr>
 500     <?php
 501     echo "\n";
 502     $odd_row =! $odd_row;
 503 } // end for
 504 ?>
 505 <!-- Modify columns -->
 506 <tr class="even noclick">
 507     <th><?php echo __('Modify'); ?>:</th>
 508 <?php
 509 $z = 0;
 510 for ($x = 0; $x < $col; $x++) {
 511     if (!empty($ins_col) && isset($ins_col[$x]) && $ins_col[$x] == 'on') {
 512         $curAndOrCol[$z] = $and_or_col[$y];
 513         if ($and_or_col[$z] == 'or') {
 514             $chk['or']  = ' checked="checked"';
 515             $chk['and'] = '';
 516         } else {
 517             $chk['and'] = ' checked="checked"';
 518             $chk['or']  = '';
 519         }
 520         ?>
 521     <td align="center">
 522         <strong><?php echo __('Or'); ?>:</strong>
 523         <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
 524         &nbsp;&nbsp;<strong><?php echo __('And'); ?>:</strong>
 525         <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
 526         <br />
 527         <?php echo __('Ins') . "\n"; ?>
 528         <input type="checkbox" name="ins_col[<?php echo $z; ?>]" />
 529         &nbsp;&nbsp;<?php echo __('Del') . "\n"; ?>
 530         <input type="checkbox" name="del_col[<?php echo $z; ?>]" />
 531     </td>
 532         <?php
 533         $z++;
 534     } // end if
 535     echo "\n";
 536
 537     if (!empty($del_col) && isset($del_col[$x]) && $del_col[$x] == 'on') {
 538         continue;
 539     }
 540
 541     if (isset($and_or_col[$y])) {
 542         $curAndOrCol[$z] = $and_or_col[$y];
 543     }
 544     if (isset($and_or_col[$z]) && $and_or_col[$z] == 'or') {
 545         $chk['or']  = ' checked="checked"';
 546         $chk['and'] = '';
 547     } else {
 548         $chk['and'] = ' checked="checked"';
 549         $chk['or']  = '';
 550     }
 551     ?>
 552     <td align="center">
 553         <strong><?php echo __('Or'); ?>:</strong>
 554         <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
 555         &nbsp;&nbsp;<strong><?php echo __('And'); ?>:</strong>
 556         <input type="radio" name="and_or_col[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
 557         <br />
 558         <?php echo __('Ins') . "\n"; ?>
 559         <input type="checkbox" name="ins_col[<?php echo $z; ?>]" />
 560         &nbsp;&nbsp;<?php echo __('Del') . "\n"; ?>
 561         <input type="checkbox" name="del_col[<?php echo $z; ?>]" />
 562     </td>
 563     <?php
 564     $z++;
 565     echo "\n";
 566 } // end for
 567 ?>
 568     </tr>
 569 </table>
 570
 571 <!-- Other controls -->
 572 <?php
 573 $w--;
 574 $url_params['db']       = $db;
 575 $url_params['col_cnt']  = $z;
 576 $url_params['rows']     = $w;
 577 echo PMA_generate_common_hidden_inputs($url_params);
 578 ?>
 579 </fieldset>
 580 <fieldset class="tblFooters">
 581 <table border="0" cellpadding="2" cellspacing="1">
 582 <tr>
 583     <td nowrap="nowrap">
 584         <?php echo __('Add/Delete Criteria Row'); ?>:
 585         <select size="1" name="add_row" style="vertical-align: middle">
 586             <option value="-3">-3</option>
 587             <option value="-2">-2</option>
 588             <option value="-1">-1</option>
 589             <option value="0" selected="selected">0</option>
 590             <option value="1">1</option>
 591             <option value="2">2</option>
 592             <option value="3">3</option>
 593         </select>
 594     </td>
 595     <td width="10">&nbsp;</td>
 596     <td nowrap="nowrap"><?php echo __('Add/Delete Field Columns'); ?>:
 597         <select size="1" name="add_col" style="vertical-align: middle">
 598             <option value="-3">-3</option>
 599             <option value="-2">-2</option>
 600             <option value="-1">-1</option>
 601             <option value="0" selected="selected">0</option>
 602             <option value="1">1</option>
 603             <option value="2">2</option>
 604             <option value="3">3</option>
 605         </select>
 606     </td>
 607     <td width="10">&nbsp;</td>
 608     <!-- Generates a query -->
 609     <td><input type="submit" name="modify" value="<?php echo __('Update Query'); ?>" /></td>
 610 </tr>
 611 </table>
 612 </fieldset>
 613
 614 <table>
 615 <tr><td>
 616         <fieldset>
 617             <legend><?php echo __('Use Tables'); ?></legend>
 618 <?php
 619 $options = '';
 620 $numTableListOptions = 0;
 621 foreach ($tbl_names as $key => $val) {
 622     $options .= '                        ';
 623     $options .= '<option value="' . htmlspecialchars($key) . '"' . $val . '>'
 624         . str_replace(' ', '&nbsp;', htmlspecialchars($key)) . '</option>' . "\n";
 625     $numTableListOptions++;
 626 }
 627 ?>
 628             <select name="TableList[]" multiple="multiple" id="listTable"
 629                 size="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>">
 630                 <?php echo $options; ?>
 631             </select>
 632         </fieldset>
 633         <fieldset class="tblFooters">
 634             <input type="submit" name="modify" value="<?php echo __('Update Query'); ?>" />
 635         </fieldset>
 636     </td>
 637     <td width="20">&nbsp;</td>
 638     <td>
 639         <fieldset>
 640             <legend><?php echo sprintf(__('SQL query on database <b>%s</b>:'), PMA_getDbLink($db)); ?>
 641                 </legend>
 642             <textarea cols="80" name="sql_query" id="textSqlquery"
 643                 rows="<?php echo ($numTableListOptions > 30) ? '15' : '7'; ?>"
 644                 dir="<?php echo $text_dir; ?>">
 645 <?php
 646 // 1. SELECT
 647 $last_select = 0;
 648 if (!isset($qry_select)) {
 649     $qry_select         = '';
 650 }
 651 for ($x = 0; $x < $col; $x++) {
 652     if (!empty($curField[$x]) && isset($curShow[$x]) && $curShow[$x] == 'on') {
 653         if ($last_select) {
 654             $qry_select .=  ', ';
 655         }
 656         $qry_select     .= $curField[$x];
 657         $last_select    = 1;
 658     }
 659 } // end for
 660 if (!empty($qry_select)) {
 661     echo  'SELECT ' . htmlspecialchars($qry_select) . "\n";
 662 }
 663
 664 // 2. FROM
 665
 666 // Create LEFT JOINS out of Relations
 667 // Code originally by Mike Beck <mike.beck@ibmiller.de>
 668 // If we can use Relations we could make some left joins.
 669 // First find out if relations are available in this database.
 670
 671 // First we need the really needed Tables - those in TableList might still be
 672 // all Tables.
 673 if (isset($Field) && count($Field) > 0) {
 674     // Initialize some variables
 675     $tab_all    = array();
 676     $col_all    = array();
 677     $tab_wher   = array();
 678     $tab_know   = array();
 679     $tab_left   = array();
 680     $col_where  = array();
 681     $fromclause = '';
 682
 683     // We only start this if we have fields, otherwise it would be dumb
 684     foreach ($Field as $value) {
 685         $parts             = explode('.', $value);
 686         if (!empty($parts[0]) && !empty($parts[1])) {
 687             $tab_raw       = $parts[0];
 688             $tab           = str_replace('`', '', $tab_raw);
 689             $tab_all[$tab] = $tab;
 690
 691             $col_raw       = $parts[1];
 692             $col_all[]     = $tab . '.' . str_replace('`', '', $col_raw);
 693          }
 694     } // end while
 695
 696     // Check 'where' clauses
 697     if ($cfgRelation['relwork'] && count($tab_all) > 0) {
 698         // Now we need all tables that we have in the where clause
 699         $crit_cnt         = count($criteria);
 700         for ($x = 0; $x < $crit_cnt; $x++) {
 701             $curr_tab     = explode('.', $Field[$x]);
 702             if (!empty($curr_tab[0]) && !empty($curr_tab[1])) {
 703                 $tab_raw  = $curr_tab[0];
 704                 $tab      = str_replace('`', '', $tab_raw);
 705
 706                 $col_raw  = $curr_tab[1];
 707                 $col1     = str_replace('`', '', $col_raw);
 708                 $col1     = $tab . '.' . $col1;
 709                 // Now we know that our array has the same numbers as $criteria
 710                 // we can check which of our columns has a where clause
 711                 if (!empty($criteria[$x])) {
 712                     if (substr($criteria[$x], 0, 1) == '=' || stristr($criteria[$x], 'is')) {
 713                         $col_where[$col] = $col1;
 714                         $tab_wher[$tab]  = $tab;
 715                     }
 716                 } // end if
 717             } // end if
 718         } // end for
 719
 720         // Cleans temp vars w/o further use
 721         unset($tab_raw);
 722         unset($col_raw);
 723         unset($col1);
 724
 725         if (count($tab_wher) == 1) {
 726             // If there is exactly one column that has a decent where-clause
 727             // we will just use this
 728             $master = key($tab_wher);
 729         } else {
 730             // Now let's find out which of the tables has an index
 731             // (When the control user is the same as the normal user
 732             // because he is using one of his databases as pmadb,
 733             // the last db selected is not always the one where we need to work)
 734             PMA_DBI_select_db($db);
 735
 736             foreach ($tab_all as $tab) {
 737                 $ind_rs   = PMA_DBI_query('SHOW INDEX FROM ' . PMA_backquote($tab) . ';');
 738                 while ($ind = PMA_DBI_fetch_assoc($ind_rs)) {
 739                     $col1 = $tab . '.' . $ind['Column_name'];
 740                     if (isset($col_all[$col1])) {
 741                         if ($ind['non_unique'] == 0) {
 742                             if (isset($col_where[$col1])) {
 743                                 $col_unique[$col1] = 'Y';
 744                             } else {
 745                                 $col_unique[$col1] = 'N';
 746                             }
 747                         } else {
 748                             if (isset($col_where[$col1])) {
 749                                 $col_index[$col1] = 'Y';
 750                             } else {
 751                                 $col_index[$col1] = 'N';
 752                             }
 753                         }
 754                     }
 755                 } // end while (each col of tab)
 756             } // end while (each tab)
 757             // now we want to find the best.
 758             if (isset($col_unique) && count($col_unique) > 0) {
 759                 $col_cand = $col_unique;
 760                 $needsort = 1;
 761             } elseif (isset($col_index) && count($col_index) > 0) {
 762                 $col_cand = $col_index;
 763                 $needsort = 1;
 764             } elseif (isset($col_where) && count($col_where) > 0) {
 765                 $col_cand = $tab_wher;
 766                 $needsort = 0;
 767             } else {
 768                 $col_cand = $tab_all;
 769                 $needsort = 0;
 770             }
 771
 772             // If we came up with $col_unique (very good) or $col_index (still
 773             // good) as $col_cand we want to check if we have any 'Y' there
 774             // (that would mean that they were also found in the whereclauses
 775             // which would be great). if yes, we take only those
 776             if ($needsort == 1) {
 777                 foreach ($col_cand as $col => $is_where) {
 778                     $tab           = explode('.', $col);
 779                     $tab           = $tab[0];
 780                     if ($is_where == 'Y') {
 781                         $vg[$col]  = $tab;
 782                     } else {
 783                         $sg[$col]  = $tab;
 784                     }
 785                 }
 786                 if (isset($vg)) {
 787                     $col_cand      = $vg;
 788                     // Candidates restricted in index+where
 789                 } else {
 790                     $col_cand      = $sg;
 791                     // None of the candidates where in a where-clause
 792                 }
 793             }
 794
 795             // If our array of candidates has more than one member we'll just
 796             // find the smallest table.
 797             // Of course the actual query would be faster if we check for
 798             // the Criteria which gives the smallest result set in its table,
 799             // but it would take too much time to check this
 800             if (count($col_cand) > 1) {
 801                 // Of course we only want to check each table once
 802                 $checked_tables = $col_cand;
 803                 foreach ($col_cand as $tab) {
 804                     if ($checked_tables[$tab] != 1) {
 805                         $tsize[$tab] = PMA_Table::countRecords($db, $tab, false);
 806                         $checked_tables[$tab] = 1;
 807                     }
 808                     $csize[$tab] = $tsize[$tab];
 809                 }
 810                 asort($csize);
 811                 reset($csize);
 812                 $master = key($csize); // Smallest
 813             } else {
 814                 reset($col_cand);
 815                 $master = current($col_cand); // Only one single candidate
 816             }
 817         } // end if (exactly one where clause)
 818
 819         $tab_left = $tab_all;
 820         unset($tab_left[$master]);
 821         $tab_know[$master] = $master;
 822
 823         $run   = 0;
 824         $emerg = '';
 825         while (count($tab_left) > 0) {
 826             if ($run % 2 == 0) {
 827                 PMA_getRelatives('master');
 828             } else {
 829                 PMA_getRelatives('foreign');
 830             }
 831             $run++;
 832             if ($run > 5) {
 833
 834                 foreach ($tab_left as $tab) {
 835                     $emerg .= ', ' . PMA_backquote($tab);
 836                     unset($tab_left[$tab]);
 837                 }
 838             }
 839         } // end while
 840         $qry_from = PMA_backquote($master) . $emerg . $fromclause;
 841     } // end if ($cfgRelation['relwork'] && count($tab_all) > 0)
 842
 843 } // end count($Field) > 0
 844
 845 // In case relations are not defined, just generate the FROM clause
 846 // from the list of tables, however we don't generate any JOIN
 847
 848 if (empty($qry_from) && isset($tab_all)) {
 849     $qry_from = implode(', ', $tab_all);
 850 }
 851 // Now let's see what we got
 852 if (!empty($qry_from)) {
 853     echo 'FROM ' . htmlspecialchars($qry_from) . "\n";
 854 }
 855
 856 // 3. WHERE
 857 $qry_where          = '';
 858 $criteria_cnt       = 0;
 859 for ($x = 0; $x < $col; $x++) {
 860     if (!empty($curField[$x]) && !empty($curCriteria[$x]) && $x && isset($last_where) && isset($curAndOrCol)) {
 861         $qry_where  .= ' ' . strtoupper($curAndOrCol[$last_where]) . ' ';
 862     }
 863     if (!empty($curField[$x]) && !empty($curCriteria[$x])) {
 864         $qry_where  .= '(' . $curField[$x] . ' ' . $curCriteria[$x] . ')';
 865         $last_where = $x;
 866         $criteria_cnt++;
 867     }
 868 } // end for
 869 if ($criteria_cnt > 1) {
 870     $qry_where      = '(' . $qry_where . ')';
 871 }
 872 // OR rows ${'cur' . $or}[$x]
 873 if (!isset($curAndOrRow)) {
 874     $curAndOrRow          = array();
 875 }
 876 for ($y = 0; $y <= $row; $y++) {
 877     $criteria_cnt         = 0;
 878     $qry_orwhere          = '';
 879     $last_orwhere         = '';
 880     for ($x = 0; $x < $col; $x++) {
 881         if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x]) && $x) {
 882             $qry_orwhere  .= ' ' . strtoupper($curAndOrCol[$last_orwhere]) . ' ';
 883         }
 884         if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x])) {
 885             $qry_orwhere  .= '(' . $curField[$x]
 886                           .  ' '
 887                           .  ${'curOr' . $y}[$x]
 888                           .  ')';
 889             $last_orwhere = $x;
 890             $criteria_cnt++;
 891         }
 892     } // end for
 893     if ($criteria_cnt > 1) {
 894         $qry_orwhere      = '(' . $qry_orwhere . ')';
 895     }
 896     if (!empty($qry_orwhere)) {
 897         $qry_where .= "\n"
 898                    .  strtoupper(isset($curAndOrRow[$y]) ? $curAndOrRow[$y] . ' ' : '')
 899                    .  $qry_orwhere;
 900     } // end if
 901 } // end for
 902
 903 if (!empty($qry_where) && $qry_where != '()') {
 904     echo 'WHERE ' . htmlspecialchars($qry_where) . "\n";
 905 } // end if
 906
 907
 908 // 4. ORDER BY
 909 $last_orderby = 0;
 910 if (!isset($qry_orderby)) {
 911     $qry_orderby      = '';
 912 }
 913 for ($x = 0; $x < $col; $x++) {
 914     if ($last_orderby && $x && !empty($curField[$x]) && !empty($curSort[$x])) {
 915         $qry_orderby  .=  ', ';
 916     }
 917     if (!empty($curField[$x]) && !empty($curSort[$x])) {
 918         // if they have chosen all fields using the * selector,
 919         // then sorting is not available
 920         // Robbat2 - Fix for Bug #570698
 921         if (substr($curField[$x], -2) != '.*') {
 922             $qry_orderby  .=  $curField[$x] . ' ' . $curSort[$x];
 923             $last_orderby = 1;
 924         }
 925     }
 926 } // end for
 927 if (!empty($qry_orderby)) {
 928     echo 'ORDER BY ' . htmlspecialchars($qry_orderby) . "\n";
 929 }
 930 ?>
 931         </textarea>
 932         </fieldset>
 933         <fieldset class="tblFooters">
 934             <input type="submit" name="submit_sql" value="<?php echo __('Submit Query'); ?>" />
 935         </fieldset>
 936     </td>
 937 </tr>
 938 </table>
 939 </form>
 940 <?php
 941 /**
 942  * Displays the footer
 943  */
 944 require_once './libraries/footer.inc.php';
 945 ?>