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