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