Documentation TYPO3 par Ameos

db_details_qbe.php

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


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