Documentation TYPO3 par Ameos

db_details_qbe.php

00001 <?php
00002 /* $Id: db_details_qbe.php,v 2.4 2003/12/25 20:50:54 lem9 Exp $ */
00003 // vim: expandtab sw=4 ts=4 sts=4:
00004 
00005 
00010 require_once('./libraries/grab_globals.lib.php');
00011 require_once('./libraries/common.lib.php');
00012 require_once('./libraries/relation.lib.php');
00013 
00014 
00018 $cfgRelation = PMA_getRelationsParam();
00019 
00020 
00024 if (isset($submit_sql) && preg_match('@^SELECT@i', $encoded_sql_query)) {
00025     $goto      = 'db_details.php';
00026     $zero_rows = htmlspecialchars($strSuccess);
00027     $sql_query = urldecode($encoded_sql_query);
00028     require('./sql.php');
00029     exit();
00030 } else {
00031     $sub_part  = '_qbe';
00032     require('./db_details_common.php');
00033     $url_query .= '&amp;goto=db_details_qbe.php';
00034     require('./db_details_db_info.php');
00035 }
00036 
00037 if (isset($submit_sql) && !preg_match('@^SELECT@i', $encoded_sql_query)) {
00038     echo '<p class="warning">' . $strHaveToShow . '</p>';
00039 }
00040 
00041 
00045 if (empty($Columns)) {
00046     $Columns  = 3;  // Initial number of columns
00047 }
00048 if (!isset($Add_Col)) {
00049     $Add_Col  = '';
00050 }
00051 if (!isset($Add_Row)) {
00052     $Add_Row  = '';
00053 }
00054 if (!isset($Rows)) {
00055     $Rows     = '';
00056 }
00057 if (!isset($InsCol)) {
00058     $InsCol   = '';
00059 }
00060 if (!isset($DelCol)) {
00061     $DelCol   = '';
00062 }
00063 if (!isset($prev_Criteria)) {
00064     $prev_Criteria = '';
00065 }
00066 // workaround for a PHP3 problem
00067 if (!isset($Criteria)) {
00068     //$Criteria = '';
00069     $Criteria = array();
00070     for ($i = 0; $i < $Columns; $i++) {
00071         $Criteria[$i] = '';
00072     }
00073 }
00074 if (!isset($InsRow)) {
00075 //    $InsRow   = '';
00076     $InsRow = array();
00077     for ($i = 0; $i < $Columns; $i++) {
00078         $InsRow[$i] = '';
00079     }
00080 }
00081 if (!isset($DelRow)) {
00082 //    $DelRow   = '';
00083     $DelRow = array();
00084     for ($i = 0; $i < $Columns; $i++) {
00085         $DelRow[$i] = '';
00086     }
00087 }
00088 if (!isset($AndOrRow)) {
00089 //    $AndOrRow = '';
00090     $AndOrRow = array();
00091     for ($i = 0; $i < $Columns; $i++) {
00092         $AndOrRow[$i] = '';
00093     }
00094 }
00095 if (!isset($AndOrCol)) {
00096 //    $AndOrCol = '';
00097     $AndOrCol = array();
00098     for ($i = 0; $i < $Columns; $i++) {
00099         $AndOrCol[$i] = '';
00100     }
00101 }
00102 // minimum width
00103 $wid          = 12;
00104 $col          = $Columns + $Add_Col;
00105 if ($col < 0) {
00106     $col      = 0;
00107 }
00108 $row          = $Rows + $Add_Row;
00109 if ($row < 0) {
00110     $row      = 0;
00111 }
00112 
00113 
00117 $tbl_result     = PMA_mysql_list_tables($db);
00118 $tbl_result_cnt = mysql_num_rows($tbl_result);
00119 $i              = 0;
00120 $k              = 0;
00121 
00122 // The tables list sent by a previously submitted form
00123 if (!empty($TableList)) {
00124     $cnt_table_list = count($TableList);
00125     for ($x = 0; $x < $cnt_table_list; $x++) {
00126         $tbl_names[urldecode($TableList[$x])] = ' selected="selected"';
00127     }
00128 } // end if
00129 
00130 // The tables list gets from MySQL
00131 while ($i < $tbl_result_cnt) {
00132     $tbl             = PMA_mysql_tablename($tbl_result, $i);
00133     $fld_results     = @PMA_mysql_list_fields_alternate($db, $tbl) or PMA_mysqlDie(PMA_mysql_error(), 'PMA_mysql_list_fields_alternate(' . $db . ', ' . $tbl . ')', FALSE, $err_url);
00134     $fld_results_cnt = ($fld_results) ? count($fld_results) : 0;
00135     $j               = 0;
00136 
00137     if (empty($tbl_names[$tbl]) && !empty($TableList)) {
00138         $tbl_names[$tbl] = '';
00139     } else {
00140         $tbl_names[$tbl] = ' selected="selected"';
00141     } //  end if
00142 
00143     // The fields list per selected tables
00144     if ($tbl_names[$tbl] == ' selected="selected"') {
00145         $fld[$k++]   =  PMA_backquote($tbl) . '.*';
00146         while ($j < $fld_results_cnt) {
00147             $fld[$k] = PMA_convert_display_charset($fld_results[$j]['Field']);
00148             $fld[$k] = PMA_backquote($tbl) . '.' . PMA_backquote($fld[$k]);
00149 
00150             // increase the width if necessary
00151             if (strlen($fld[$k]) > $wid) {
00152                 $wid = strlen($fld[$k]);
00153             } //end if
00154 
00155             $k++;
00156             $j++;
00157         } // end while
00158     } // end if
00159 
00160     $i++;
00161 } // end if
00162 mysql_free_result($tbl_result);
00163 
00164 // largest width found
00165 $realwidth = $wid . 'ex';
00166 
00167 
00171 ?>
00172 
00173 <!-- Query by example form -->
00174 <form action="db_details_qbe.php" method="post">
00175     <table border="<?php echo $cfg['Border']; ?>">
00176 
00177     <!-- Fields row -->
00178     <tr>
00179         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $cfg['ThBgcolor']; ?>">
00180             <b><?php echo $strField; ?>&nbsp;:&nbsp;</b>
00181         </td>
00182 <?php
00183 $z = 0;
00184 for ($x = 0; $x < $col; $x++) {
00185     if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
00186         ?>
00187         <td align="center" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
00188             <select style="width: <?php echo $realwidth; ?>" name="Field[<?php echo $z; ?>]" size="1">
00189                 <option value=""></option>
00190         <?php
00191         echo "\n";
00192         for ($y = 0; $y < sizeof($fld); $y++) {
00193             if ($fld[$y] == '') {
00194                 $sel = ' selected="selected"';
00195             } else {
00196                 $sel = '';
00197             }
00198             echo '                ';
00199             echo '<option value="' . htmlspecialchars($fld[$y]) . '"' . $sel . '>' . htmlspecialchars($fld[$y]) . '</option>' . "\n";
00200         } // end for
00201         ?>
00202             </select>
00203         </td>
00204         <?php
00205         $z++;
00206     } // end if
00207     echo "\n";
00208 
00209     if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
00210         continue;
00211     }
00212     ?>
00213         <td align="center" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
00214             <select style="width: <?php echo $realwidth; ?>" name="Field[<?php echo $z; ?>]" size="1">
00215                 <option value=""></option>
00216     <?php
00217     echo "\n";
00218     for ($y = 0; $y < sizeof($fld); $y++) {
00219         if (isset($Field[$x]) && $fld[$y] == urldecode($Field[$x])) {
00220             $curField[$z] = urldecode($Field[$x]);
00221             $sel          = ' selected="selected"';
00222         } else {
00223             $sel          = '';
00224         } // end if
00225         echo '                ';
00226         echo '<option value="' . htmlspecialchars($fld[$y]) . '"' . $sel . '>' . htmlspecialchars($fld[$y]) . '</option>' . "\n";
00227     } // end for
00228     ?>
00229             </select>
00230         </td>
00231     <?php
00232     $z++;
00233     echo "\n";
00234 } // end for
00235 ?>
00236     </tr>
00237 
00238     <!-- Sort row -->
00239     <tr>
00240         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $cfg['ThBgcolor']; ?>">
00241             <b><?php echo $strSort; ?>&nbsp;:&nbsp;</b>
00242         </td>
00243 <?php
00244 $z = 0;
00245 for ($x = 0; $x < $col; $x++) {
00246     if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
00247         ?>
00248         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
00249             <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
00250                 <option value=""></option>
00251                 <option value="ASC"><?php echo $strAscending; ?></option>
00252                 <option value="DESC"><?php echo $strDescending; ?></option>
00253             </select>
00254         </td>
00255         <?php
00256         $z++;
00257     } // end if
00258     echo "\n";
00259 
00260     if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
00261         continue;
00262     }
00263     ?>
00264         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
00265             <select style="width: <?php echo $realwidth; ?>" name="Sort[<?php echo $z; ?>]" size="1">
00266                 <option value=""></option>
00267     <?php
00268     echo "\n";
00269 
00270     // If they have chosen all fields using the * selector,
00271     // then sorting is not available
00272     // Robbat2 - Fix for Bug #570698
00273     if (isset($Sort[$x]) && isset($Field[$x]) && (substr(urldecode($Field[$x]),-2) == '.*')) {
00274         $Sort[$x] = '';
00275     } //end if
00276 
00277     if (isset($Sort[$x]) && $Sort[$x] == 'ASC') {
00278         $curSort[$z] = $Sort[$x];
00279         $sel         = ' selected="selected"';
00280     } else {
00281         $sel         = '';
00282     } // end if
00283     echo '                ';
00284     echo '<option value="ASC"' . $sel . '>' . $strAscending . '</option>' . "\n";
00285     if (isset($Sort[$x]) && $Sort[$x] == 'DESC') {
00286         $curSort[$z] = $Sort[$x];
00287         $sel         = ' selected="selected"';
00288     } else {
00289         $sel         = '';
00290     } // end if
00291     echo '                ';
00292     echo '<option value="DESC"' . $sel . '>' . $strDescending . '</option>' . "\n";
00293     ?>
00294             </select>
00295         </td>
00296     <?php
00297     $z++;
00298     echo "\n";
00299 } // end for
00300 ?>
00301     </tr>
00302 
00303     <!-- Show row -->
00304     <tr>
00305         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $cfg['ThBgcolor']; ?>">
00306             <b><?php echo $strShow; ?>&nbsp;:&nbsp;</b>
00307         </td>
00308 <?php
00309 $z = 0;
00310 for ($x = 0; $x < $col; $x++) {
00311     if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
00312         ?>
00313         <td align="center" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
00314             <input type="checkbox" name="Show[<?php echo $z; ?>]" />
00315         </td>
00316         <?php
00317         $z++;
00318     } // end if
00319     echo "\n";
00320 
00321     if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
00322         continue;
00323     }
00324     if (isset($Show[$x])) {
00325         $checked     = ' checked="checked"';
00326         $curShow[$z] = $Show[$x];
00327     } else {
00328         $checked     =  '';
00329     }
00330     ?>
00331         <td align="center" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
00332             <input type="checkbox" name="Show[<?php echo $z; ?>]"<?php echo $checked; ?> />
00333         </td>
00334     <?php
00335     $z++;
00336     echo "\n";
00337 } // end for
00338 ?>
00339     </tr>
00340 
00341     <!-- Criteria row -->
00342     <tr>
00343         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $cfg['ThBgcolor']; ?>">
00344             <b><?php echo $strCriteria; ?>&nbsp;:&nbsp;</b>
00345         </td>
00346 <?php
00347 $z = 0;
00348 for ($x = 0; $x < $col; $x++) {
00349     if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
00350         ?>
00351         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
00352             <input type="text" name="Criteria[<?php echo $z; ?>]" value="" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
00353         </td>
00354         <?php
00355         $z++;
00356     } // end if
00357     echo "\n";
00358 
00359     if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
00360         continue;
00361     }
00362     if (isset($Criteria[$x])) {
00363         $stripped_Criteria = $Criteria[$x];
00364     }
00365     if ((empty($prev_Criteria) || !isset($prev_Criteria[$x]))
00366         || urldecode($prev_Criteria[$x]) != htmlspecialchars($stripped_Criteria)) {
00367         $curCriteria[$z]   = $stripped_Criteria;
00368         $encoded_Criteria  = urlencode($stripped_Criteria);
00369     } else {
00370         $curCriteria[$z]   = urldecode($prev_Criteria[$x]);
00371         $encoded_Criteria  = $prev_Criteria[$x];
00372     }
00373     ?>
00374         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
00375             <input type="hidden" name="prev_Criteria[<?php echo $z; ?>]" value="<?php echo $encoded_Criteria; ?>" />
00376             <input type="text" name="Criteria[<?php echo $z; ?>]" value="<?php echo htmlspecialchars($stripped_Criteria); ?>" class="textfield" style="width: <?php echo $realwidth; ?>" size="20" />
00377         </td>
00378     <?php
00379     $z++;
00380     echo "\n";
00381 } // end for
00382 ?>
00383     </tr>
00384 
00385     <!-- And/Or columns and rows -->
00386 <?php
00387 $w = 0;
00388 for ($y = 0; $y <= $row; $y++) {
00389     $bgcolor = ($y % 2) ? $cfg['BgcolorOne'] : $cfg['BgcolorTwo'];
00390     if (isset($InsRow[$y]) && $InsRow[$y] == 'on') {
00391         $chk['or']  = ' checked="checked"';
00392         $chk['and'] = '';
00393         ?>
00394     <tr>
00395         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $bgcolor; ?>" nowrap="nowrap">
00396             <!-- Row controls -->
00397             <table bgcolor="<?php echo $bgcolor; ?>">
00398             <tr>
00399                 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
00400                     <small><?php echo $strQBEIns; ?>&nbsp;:</small>
00401                     <input type="checkbox" name="InsRow[<?php echo $w; ?>]" />
00402                 </td>
00403                 <td align="<?php echo $cell_align_right; ?>">
00404                     <b><?php echo $strAnd; ?>&nbsp;:</b>
00405                 </td>
00406                 <td>
00407                     <input type="radio" name="AndOrRow[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
00408                     &nbsp;
00409                 </td>
00410             </tr>
00411             <tr>
00412                 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
00413                     <small><?php echo $strQBEDel; ?>&nbsp;:</small>
00414                     <input type="checkbox" name="DelRow[<?php echo $w; ?>]" />
00415                 </td>
00416                 <td align="<?php echo $cell_align_right; ?>">
00417                     <b><?php echo $strOr; ?>&nbsp;:</b>
00418                 </td>
00419                 <td>
00420                     <input type="radio" name="AndOrRow[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
00421                     &nbsp;
00422                 </td>
00423             </tr>
00424             </table>
00425         </td>
00426         <?php
00427         $z = 0;
00428         for ($x = 0; $x < $col; $x++) {
00429             if ($InsCol[$x] == 'on') {
00430                 echo "\n";
00431                 $or = 'Or' . $w . '[' . $z . ']';
00432                 ?>
00433         <td align="center" bgcolor="<?php echo $bgcolor; ?>">
00434             <textarea cols="20" rows="2" style="width: <?php echo $realwidth; ?>" name="<?php echo $or; ?>" dir="<?php echo $text_dir; ?>"></textarea>
00435         </td>
00436                 <?php
00437                 $z++;
00438             } // end if
00439             if ($DelCol[$x] == 'on') {
00440                 continue;
00441             }
00442 
00443             echo "\n";
00444             $or = 'Or' . $w . '[' . $z . ']';
00445             ?>
00446         <td align="center" bgcolor="<?php echo $bgcolor; ?>">
00447             <textarea cols="20" rows="2" style="width: <?php echo $realwidth; ?>" name="<?php echo $or; ?>" dir="<?php echo $text_dir; ?>"></textarea>
00448         </td>
00449             <?php
00450             $z++;
00451         } // end for
00452         $w++;
00453         echo "\n";
00454         ?>
00455     </tr>
00456         <?php
00457     } // end if
00458 
00459     if (isset($DelRow[$y]) && $DelRow[$y] == 'on') {
00460         continue;
00461     }
00462 
00463     if (isset($AndOrRow[$y])) {
00464         $curAndOrRow[$w] = $AndOrRow[$y];
00465     }
00466     if (isset($AndOrRow[$y]) && $AndOrRow[$y] == 'and') {
00467         $chk['and'] =  ' checked="checked"';
00468         $chk['or']  =  '';
00469     } else {
00470         $chk['or']  =  ' checked="checked"';
00471         $chk['and'] =  '';
00472     }
00473     echo "\n";
00474     ?>
00475     <tr>
00476         <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
00477             <!-- Row controls -->
00478             <table bgcolor="<?php echo $bgcolor; ?>">
00479             <tr>
00480                 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
00481                     <small><?php echo $strQBEIns; ?>&nbsp;:</small>
00482                     <input type="checkbox" name="InsRow[<?php echo $w; ?>]" />
00483                 </td>
00484                 <td align="<?php echo $cell_align_right; ?>">
00485                     <b><?php echo $strAnd; ?>&nbsp;:</b>
00486                 </td>
00487                 <td>
00488                     <input type="radio" name="AndOrRow[<?php echo $w; ?>]" value="and"<?php echo $chk['and']; ?> />
00489                 </td>
00490             </tr>
00491             <tr>
00492                 <td align="<?php echo $cell_align_right; ?>" nowrap="nowrap">
00493                     <small><?php echo $strQBEDel; ?>&nbsp;:</small>
00494                     <input type="checkbox" name="DelRow[<?php echo $w; ?>]" />
00495                 </td>
00496                 <td align="<?php echo $cell_align_right; ?>">
00497                     <b><?php echo $strOr; ?>&nbsp;:</b>
00498                 </td>
00499                 <td>
00500                     <input type="radio" name="AndOrRow[<?php echo $w; ?>]" value="or"<?php echo $chk['or']; ?> />
00501                 </td>
00502             </tr>
00503             </table>
00504         </td>
00505     <?php
00506     $z = 0;
00507     for ($x = 0; $x < $col; $x++) {
00508         if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
00509             echo "\n";
00510             $or = 'Or' . $w . '[' . $z . ']';
00511             ?>
00512         <td align="center" bgcolor="<?php echo $bgcolor; ?>">
00513             <textarea cols="20" rows="2" style="width: <?php echo $realwidth; ?>" name="<?php echo $or; ?>" dir="<?php echo $text_dir; ?>"></textarea>
00514         </td>
00515             <?php
00516             $z++;
00517         } // end if
00518         if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
00519             continue;
00520         }
00521 
00522         echo "\n";
00523         $or = 'Or' . $y;
00524         if (!isset(${$or})) {
00525             ${$or} = '';
00526         }
00527         if (!empty(${$or}) && isset(${$or}[$x])) {
00528             $stripped_or = ${$or}[$x];
00529         } else {
00530             $stripped_or     = '';
00531         }
00532         ?>
00533         <td align="center" bgcolor="<?php echo $bgcolor; ?>">
00534             <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>
00535         </td>
00536         <?php
00537         if (!empty(${$or}) && isset(${$or}[$x])) {
00538             ${'cur' . $or}[$z] = ${$or}[$x];
00539         }
00540         $z++;
00541     } // end for
00542     $w++;
00543     echo "\n";
00544     ?>
00545     </tr>
00546     <?php
00547     echo "\n";
00548 } // end for
00549 ?>
00550 
00551     <!-- Modify columns -->
00552     <tr>
00553         <td align="<?php echo $cell_align_right; ?>" bgcolor="<?php echo $cfg['ThBgcolor']; ?>">
00554             <b><?php echo $strModify; ?>&nbsp;:&nbsp;</b>
00555         </td>
00556 <?php
00557 $z = 0;
00558 for ($x = 0; $x < $col; $x++) {
00559     if (!empty($InsCol) && isset($InsCol[$x]) && $InsCol[$x] == 'on') {
00560         $curAndOrCol[$z] = $AndOrCol[$y];
00561         if ($AndOrCol[$z] == 'or') {
00562             $chk['or']  = ' checked="checked"';
00563             $chk['and'] = '';
00564         } else {
00565             $chk['and'] = ' checked="checked"';
00566             $chk['or']  = '';
00567         }
00568         ?>
00569         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
00570             <b><?php echo $strOr; ?>&nbsp;:</b>
00571             <input type="radio" name="AndOrCol[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
00572             &nbsp;&nbsp;<b><?php echo $strAnd; ?>&nbsp;:</b>
00573             <input type="radio" name="AndOrCol[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
00574             <br />
00575             <?php echo $strQBEIns . "\n"; ?>
00576             <input type="checkbox" name="InsCol[<?php echo $z; ?>]" />
00577             &nbsp;&nbsp;<?php echo $strQBEDel . "\n"; ?>
00578             <input type="checkbox" name="DelCol[<?php echo $z; ?>]" />
00579         </td>
00580         <?php
00581         $z++;
00582     } // end if
00583     echo "\n";
00584 
00585     if (!empty($DelCol) && isset($DelCol[$x]) && $DelCol[$x] == 'on') {
00586         continue;
00587     }
00588 
00589     if (isset($AndOrCol[$y])) {
00590         $curAndOrCol[$z] = $AndOrCol[$y];
00591     }
00592     if (isset($AndOrCol[$z]) && $AndOrCol[$z] == 'or') {
00593         $chk['or']  = ' checked="checked"';
00594         $chk['and'] = '';
00595     } else {
00596         $chk['and'] = ' checked="checked"';
00597         $chk['or']  = '';
00598     }
00599     ?>
00600         <td align="center" bgcolor="<?php echo $cfg['BgcolorTwo']; ?>">
00601             <b><?php echo $strOr; ?>&nbsp;:</b>
00602             <input type="radio" name="AndOrCol[<?php echo $z; ?>]" value="or"<?php echo $chk['or']; ?> />
00603             &nbsp;&nbsp;<b><?php echo $strAnd; ?>&nbsp;:</b>
00604             <input type="radio" name="AndOrCol[<?php echo $z; ?>]" value="and"<?php echo $chk['and']; ?> />
00605             <br />
00606             <?php echo $strQBEIns . "\n"; ?>
00607             <input type="checkbox" name="InsCol[<?php echo $z; ?>]" />
00608             &nbsp;&nbsp;<?php echo $strQBEDel . "\n"; ?>
00609             <input type="checkbox" name="DelCol[<?php echo $z; ?>]" />
00610         </td>
00611     <?php
00612     $z++;
00613     echo "\n";
00614 } // end for
00615 ?>
00616     </tr>
00617     </table>
00618 
00619 
00620     <!-- Other controls -->
00621     <table border="0">
00622     <tr>
00623         <td valign="top">
00624             <table border="0" align="<?php echo $cell_align_left; ?>">
00625             <tr>
00626                 <td rowspan="4" valign="top">
00627                     <?php echo $strUseTables; ?>&nbsp;:
00628                     <br />
00629                     <select name="TableList[]" size="7" multiple="multiple">
00630 <?php
00631 foreach($tbl_names AS $key => $val) {
00632     echo '                        ';
00633     echo '<option value="' . htmlspecialchars($key) . '"' . $val . '>' . htmlspecialchars($key) . '</option>' . "\n";
00634 }
00635 ?>
00636                     </select>
00637                 </td>
00638                 <td align="<?php echo $cell_align_right; ?>" valign="bottom">
00639                     <input type="hidden" value="<?php echo htmlspecialchars($db); ?>" name="db" />
00640                     <input type="hidden" value="<?php echo $z; ?>" name="Columns" />
00641 <?php
00642 $w--;
00643 ?>
00644                     <input type="hidden" value="<?php echo $w; ?>" name="Rows" />
00645                     <?php echo $strAddDeleteRow; ?>&nbsp;:
00646                     <select size="1" name="Add_Row">
00647                         <option value="-3">-3</option>
00648                         <option value="-2">-2</option>
00649                         <option value="-1">-1</option>
00650                         <option value="0" selected="selected">0</option>
00651                         <option value="1">1</option>
00652                         <option value="2">2</option>
00653                         <option value="3">3</option>
00654                     </select>
00655                 </td>
00656             </tr>
00657             <tr>
00658                 <td align="<?php echo $cell_align_right; ?>" valign="bottom">
00659                     <?php echo $strAddDeleteColumn; ?>&nbsp;:
00660                     <select size="1" name="Add_Col">
00661                         <option value="-3">-3</option>
00662                         <option value="-2">-2</option>
00663                         <option value="-1">-1</option>
00664                         <option value="0" selected="selected">0</option>
00665                         <option value="1">1</option>
00666                         <option value="2">2</option>
00667                         <option value="3">3</option>
00668                     </select>
00669                 </td>
00670             </tr>
00671             <!-- Generates a query -->
00672             <tr align="center" valign="top">
00673                 <td>
00674                     <input type="submit" name="modify" value="<?php echo $strUpdateQuery; ?>" />
00675                     <?php echo PMA_generate_common_hidden_inputs(); ?>
00676                 </td>
00677             </tr>
00678             <!-- Executes a query -->
00679             <tr align="center" valign="top">
00680                 <td>
00681                     <input type="submit" name="submit_sql" value="<?php echo $strRunQuery; ?>" />
00682                 </td>
00683             </tr>
00684             </table>
00685         </td>
00686         <td>
00687             <!-- Displays the current query -->
00688             <?php echo sprintf($strQueryOnDb, htmlspecialchars($db)); ?><br />
00689 <textarea cols="30" rows="7" name="sql_query" dir="<?php echo $text_dir; ?>">
00690 <?php
00691 // 1. SELECT
00692 $last_select = 0;
00693 $encoded_qry = '';
00694 if (!isset($qry_select)) {
00695     $qry_select         = '';
00696 }
00697 for ($x = 0; $x < $col; $x++) {
00698     if (!empty($curField[$x]) && isset($curShow[$x]) && $curShow[$x] == 'on') {
00699         if ($last_select) {
00700             $qry_select .=  ', ';
00701         }
00702         $qry_select     .= $curField[$x];
00703         $last_select    = 1;
00704     }
00705 } // end for
00706 if (!empty($qry_select)) {
00707     $encoded_qry .= urlencode('SELECT ' . $qry_select . "\n");
00708     echo  'SELECT ' . htmlspecialchars($qry_select) . "\n";
00709 }
00710 
00711 // 2. FROM
00712 
00713 // Create LEFT JOINS out of Relations
00714 // Code originally by Mike Beck <mike.beck@ibmiller.de>
00715 // If we can use Relations we could make some left joins.
00716 // First find out if relations are available in this database.
00717 
00718 // First we need the really needed Tables - those in TableList might still be
00719 // all Tables.
00720 if (isset($Field) && count($Field) > 0) {
00721 
00722     // Initialize some variables
00723     $tab_all    = array();
00724     $col_all    = array();
00725     $tab_wher   = array();
00726     $tab_know   = array();
00727     $tab_left   = array();
00728     $col_where  = array();
00729     $fromclause = '';
00730 
00731     // We only start this if we have fields, otherwise it would be dumb
00732     foreach($Field AS $value) {
00733         $parts             = explode('.', $value);
00734         if (!empty($parts[0]) && !empty($parts[1])) {
00735             $tab_raw       = urldecode($parts[0]);
00736             $tab           = str_replace('`', '', $tab_raw);
00737             $tab_all[$tab] = $tab;
00738 
00739             $col_raw       = urldecode($parts[1]);
00740             $col_all[]     = $tab . '.' . str_replace('`', '', $col_raw);
00741          }
00742     } // end while
00743 
00744     // Check 'where' clauses
00745     if ($cfgRelation['relwork'] && count($tab_all) > 0) {
00746         // Now we need all tables that we have in the where clause
00747         $crit_cnt         = count($Criteria);
00748         for ($x = 0; $x < $crit_cnt; $x++) {
00749             $curr_tab     = explode('.', urldecode($Field[$x]));
00750             if (!empty($curr_tab[0]) && !empty($curr_tab[1])) {
00751                 $tab_raw  = urldecode($curr_tab[0]);
00752                 $tab      = str_replace('`', '', $tab_raw);
00753 
00754                 $col_raw  = urldecode($curr_tab[1]);
00755                 $col1     = str_replace('`', '', $col_raw);
00756                 $col1     = $tab . '.' . $col1;
00757                 // Now we know that our array has the same numbers as $Criteria
00758                 // we can check which of our columns has a where clause
00759                 if (!empty($Criteria[$x])) {
00760                     if (substr($Criteria[$x], 0, 1) == '=' || stristr($Criteria[$x], 'is')) {
00761                         $col_where[$col] = $col1;
00762                         $tab_wher[$tab]  = $tab;
00763                     }
00764                 } // end if
00765             } // end if
00766         } // end for
00767 
00768         // Cleans temp vars w/o further use
00769         unset($tab_raw);
00770         unset($col_raw);
00771         unset($col1);
00772 
00773         if (count($tab_wher) == 1) {
00774             // If there is exactly one column that has a decent where-clause
00775             // we will just use this
00776             $master = key($tab_wher);
00777         } else {
00778             // Now let's find out which of the tables has an index
00779             // ( When the control user is the same as the normal user
00780             // because he is using one of his databases as pmadb,
00781             // the last db selected is not always the one where we need to work)
00782             PMA_mysql_select_db($db);
00783 
00784             foreach($tab_all AS $tab) {
00785                 $ind_qry  = 'SHOW INDEX FROM ' . PMA_backquote($tab);
00786                 $ind_rs   = PMA_mysql_query($ind_qry);
00787                 while ($ind = PMA_mysql_fetch_array($ind_rs)) {
00788                     $col1 = $tab . '.' . $ind['Column_name'];
00789                     if (isset($col_all[$col1])) {
00790                         if ($ind['non_unique'] == 0) {
00791                             if (isset($col_where[$col1])) {
00792                                 $col_unique[$col1] = 'Y';
00793                             } else {
00794                                 $col_unique[$col1] = 'N';
00795                             }
00796                         } else {
00797                             if (isset($col_where[$col1])) {
00798                                 $col_index[$col1] = 'Y';
00799                             } else {
00800                                 $col_index[$col1] = 'N';
00801                             }
00802                         }
00803                     }
00804                 } // end while (each col of tab)
00805             } // end while (each tab)
00806             // now we want to find the best.
00807             if (isset($col_unique) && count($col_unique) > 0) {
00808                 $col_cand = $col_unique;
00809                 $needsort = 1;
00810             } else if (isset($col_index) && count($col_index) > 0) {
00811                 $col_cand = $col_index;
00812                 $needsort = 1;
00813             } else if (isset($col_where) && count($col_where) > 0) {
00814                 $col_cand = $tab_wher;
00815                 $needsort = 0;
00816             } else {
00817                 $col_cand = $tab_all;
00818                 $needsort = 0;
00819             }
00820 
00821             // If we came up with $col_unique (very good) or $col_index (still
00822             // good) as $col_cand we want to check if we have any 'Y' there
00823             // (that would mean that they were also found in the whereclauses
00824             // which would be great). if yes, we take only those
00825             if ($needsort == 1) {
00826                 foreach($col_cand AS $col => $is_where) {
00827                     $tab           = explode('.', $col);
00828                     $tab           = $tab[0];
00829                     if ($is_where == 'Y') {
00830                         $vg[$col]  = $tab;
00831                     } else {
00832                         $sg[$col]  = $tab;
00833                     }
00834                 }
00835                 if (isset($vg)) {
00836                     $col_cand      = $vg;
00837                     // Candidates restricted in index+where
00838                 } else {
00839                     $col_cand      = $sg;
00840                     // None of the candidates where in a where-clause
00841                 }
00842             }
00843 
00844             // If our array of candidates has more than one member we'll just
00845             // find the smallest table.
00846             // Of course the actual query would be faster if we check for
00847             // the Criteria which gives the smallest result set in its table,
00848             // but it would take too much time to check this
00849             if (count($col_cand) > 1) {
00850                 // Of course we only want to check each table once
00851                 $checked_tables = $col_cand;
00852                 foreach($col_cand AS $tab) {
00853                     if ($checked_tables[$tab] != 1 ) {
00854                         $rows_qry = 'SELECT COUNT(1) AS anz '
00855                                   . 'FROM ' . PMA_backquote($tab);
00856                         $rows_rs  = PMA_mysql_query($rows_qry);
00857                         while ($res = PMA_mysql_fetch_array($rows_rs)) {
00858                             $tsize[$tab] = $res['anz'];
00859                         }
00860                         $checked_tables[$tab] = 1;
00861                     }
00862                     $csize[$tab] = $tsize[$tab];
00863                 }
00864                 asort($csize);
00865                 reset($csize);
00866                 $master = key($csize); // Smallest
00867             } else {
00868                 reset($col_cand);
00869                 $master = current($col_cand); // Only one single candidate
00870             }
00871         } // end if (exactly one where clause)
00872 
00883         function PMA_arrayShort($array, $key)
00884         {
00885             foreach($array AS $k => $v) {
00886                 if ($k != $key) {
00887                     $reta[$k] = $v;
00888                 }
00889             }
00890             if (!isset($reta)) {
00891                 $reta = array();
00892             }
00893 
00894             return $reta;
00895         } // end of the "PMA_arrayShort()" function
00896 
00897 
00913         function PMA_getRelatives($from) {
00914             global $tab_left, $tab_know, $fromclause;
00915             global $dbh, $db, $cfgRelation;
00916 
00917             if ($from == 'master') {
00918                 $to    = 'foreign';
00919             } else {
00920                 $to    = 'master';
00921             }
00922             $in_know = '(\'' . implode('\', \'', $tab_know) . '\')';
00923             $in_left = '(\'' . implode('\', \'', $tab_left) . '\')';
00924 
00925             $rel_query = 'SELECT *'
00926                        . ' FROM ' . PMA_backquote($cfgRelation['relation'])
00927                        . ' WHERE ' . $from . '_db   = \'' . PMA_sqlAddslashes($db) . '\''
00928                        . ' AND ' . $to   . '_db   = \'' . PMA_sqlAddslashes($db) . '\''
00929                        . ' AND ' . $from . '_table IN ' . $in_know
00930                        . ' AND ' . $to   . '_table IN ' . $in_left;
00931             if (isset($dbh)) {
00932                 PMA_mysql_select_db($cfgRelation['db'], $dbh);
00933                 $relations = @PMA_mysql_query($rel_query, $dbh) or PMA_mysqlDie(PMA_mysql_error($dbh), $rel_query, '', $err_url_0);
00934                 PMA_mysql_select_db($db, $dbh);
00935             } else {
00936                 PMA_mysql_select_db($cfgRelation['db']);
00937                 $relations = @PMA_mysql_query($rel_query) or PMA_mysqlDie('', $rel_query, '', $err_url_0);
00938                 PMA_mysql_select_db($db);
00939             }
00940             while ($row = PMA_mysql_fetch_array($relations)) {
00941                 $found_table                = $row[$to . '_table'];
00942                 if (isset($tab_left[$found_table])) {
00943                     $fromclause             .= "\n" . ' LEFT JOIN '
00944                                             . PMA_backquote($row[$to . '_table']) . ' ON '
00945                                             . PMA_backquote($row[$from . '_table']) . '.'
00946                                             . PMA_backquote($row[$from . '_field']) . ' = '
00947                                             . PMA_backquote($row[$to . '_table']) . '.'
00948                                             . PMA_backquote($row[$to . '_field']) . ' ';
00949                     $tab_know[$found_table] = $found_table;
00950                     $tab_left               = PMA_arrayShort($tab_left, $found_table);
00951                 }
00952             } // end while
00953 
00954             return TRUE;
00955         } // end of the "PMA_getRelatives()" function
00956 
00957 
00958         $tab_left          = PMA_arrayShort($tab_all, $master);
00959         $tab_know[$master] = $master;
00960 
00961         $run   = 0;
00962         $emerg = '';
00963         while (count($tab_left) > 0) {
00964             if ($run % 2 == 0) {
00965                 PMA_getRelatives('master');
00966             } else {
00967                 PMA_getRelatives('foreign');
00968             }
00969             $run++;
00970             if ($run > 5) {
00971 
00972                 foreach($tab_left AS $tab) {
00973                     $emerg    .= ', ' . $tab;
00974                     $tab_left = PMA_arrayShort($tab_left, $tab);
00975                 }
00976             }
00977         } // end while
00978         $qry_from = $master . $emerg . $fromclause;
00979     } // end if ($cfgRelation['relwork'] && count($tab_all) > 0)
00980 
00981 } // end count($Field) > 0
00982 
00983 // In case relations are not defined, just generate the FROM clause
00984 // from the list of tables, however we don't generate any JOIN
00985 
00986 if (empty($qry_from) && isset($tab_all)) {
00987     $qry_from = implode(', ', $tab_all);
00988 }
00989 // Now let's see what we got
00990 if (!empty($qry_from)) {
00991     $encoded_qry  .= urlencode('FROM ' . $qry_from . "\n");
00992     echo 'FROM ' . htmlspecialchars($qry_from) . "\n";
00993 }
00994 
00995 // 3. WHERE
00996 $qry_where          = '';
00997 $criteria_cnt       = 0;
00998 for ($x = 0; $x < $col; $x++) {
00999     if (!empty($curField[$x]) && !empty($curCriteria[$x]) && $x && isset($last_where)) {
01000         $qry_where  .= ' ' . strtoupper($curAndOrCol[$last_where]) . ' ';
01001     }
01002     if (!empty($curField[$x]) && !empty($curCriteria[$x])) {
01003         $qry_where  .= '(' . $curField[$x] . ' ' . $curCriteria[$x] . ')';
01004         $last_where = $x;
01005         $criteria_cnt++;
01006     }
01007 } // end for
01008 if ($criteria_cnt > 1) {
01009     $qry_where      = '(' . $qry_where . ')';
01010 }
01011 // OR rows ${'cur' . $or}[$x]
01012 if (!isset($curAndOrRow)) {
01013     $curAndOrRow          = array();
01014 }
01015 for ($y = 0; $y <= $row; $y++) {
01016     $criteria_cnt         = 0;
01017     $qry_orwhere          = '';
01018     $last_orwhere         = '';
01019     for ($x = 0; $x < $col; $x++) {
01020         if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x]) && $x) {
01021             $qry_orwhere  .= ' ' . strtoupper($curAndOrCol[$last_orwhere]) . ' ';
01022         }
01023         if (!empty($curField[$x]) && !empty(${'curOr' . $y}[$x])) {
01024             $qry_orwhere  .= '(' . $curField[$x]
01025                           .  ' '
01026                           .  ${'curOr' . $y}[$x]
01027                           .  ')';
01028             $last_orwhere = $x;
01029             $criteria_cnt++;
01030         }
01031     } // end for
01032     if ($criteria_cnt > 1) {
01033         $qry_orwhere      = '(' . $qry_orwhere . ')';
01034     }
01035     if (!empty($qry_orwhere)) {
01036         $qry_where .= "\n"
01037                    .  strtoupper(isset($curAndOrRow[$y]) ? $curAndOrRow[$y] . ' ' : '')
01038                    .  $qry_orwhere;
01039     } // end if
01040 } // end for
01041 
01042 if (!empty($qry_where) && $qry_where != '()') {
01043     $encoded_qry .= urlencode('WHERE ' . $qry_where . "\n");
01044     echo 'WHERE ' . htmlspecialchars($qry_where) . "\n";
01045 } // end if
01046 
01047 // 4. ORDER BY
01048 $last_orderby = 0;
01049 if (!isset($qry_orderby)) {
01050     $qry_orderby      = '';
01051 }
01052 for ($x = 0; $x < $col; $x++) {
01053     if ($last_orderby && $x && !empty($curField[$x]) && !empty($curSort[$x])) {
01054         $qry_orderby  .=  ', ';
01055     }
01056     if (!empty($curField[$x]) && !empty($curSort[$x])) {
01057         // if they have chosen all fields using the * selector,
01058         // then sorting is not available
01059         // Robbat2 - Fix for Bug #570698
01060         if (substr($curField[$x], -2) != '.*') {
01061             $qry_orderby  .=  $curField[$x] . ' ' . $curSort[$x];
01062             $last_orderby = 1;
01063         }
01064     }
01065 } // end for
01066 if (!empty($qry_orderby)) {
01067     $encoded_qry .= urlencode('ORDER BY ' . $qry_orderby);
01068     echo 'ORDER BY ' . htmlspecialchars($qry_orderby) . "\n";
01069 }
01070 ?>
01071 </textarea>
01072             <input type="hidden" name="encoded_sql_query" value="<?php echo $encoded_qry; ?>" />
01073         </td>
01074     </tr>
01075     </table>
01076 
01077 </form>
01078 
01079 
01080 <?php
01084 require_once('./footer.inc.php');
01085 ?>


Généré par Les experts TYPO3 avec  doxygen 1.4.6