db_qbe.php

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