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