db_details_qbe.php

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