db_search.php3

   1 <?php
   2 /* $Id$ */
   3
   4 /**
   5  * Credits for this script goes to Thomas Chaumeny <chaume92 at aol.com>
   6  */
   7
   8
   9 /**
  10  * Gets some core libraries and send headers
  11  */
  12 require('./db_details_common.php3');
  13 // If config variable $cfg['Usedbsearch'] is on FALSE : exit.
  14 if (!$cfg['UseDbSearch']) {
  15     PMA_mysqlDie($strAccessDenied, '', FALSE, $err_url);
  16 } // end if
  17 $url_query .= '&amp;goto=db_search.php3';
  18
  19
  20 /**
  21  * Get the list of tables from the current database
  22  */
  23 $list_tables  = PMA_mysql_list_tables($db);
  24 $num_tables   = ($list_tables ? mysql_num_rows($list_tables) : 0);
  25 for ($i = 0; $i < $num_tables; $i++) {
  26     $tables[] = PMA_mysql_tablename($list_tables, $i);
  27 }
  28 if ($num_tables) {
  29     mysql_free_result($list_tables);
  30 }
  31
  32
  33 /**
  34  * Displays top links
  35  */
  36 $sub_part = '';
  37 require('./db_details_links.php3');
  38
  39
  40 /**
  41  * 1. Main search form has been submitted
  42  */
  43 if (isset($submit_search)) {
  44
  45     /**
  46      * Builds the SQL search query
  47      *
  48      * @param   string   the table name
  49      * @param   string   the string to search
  50      * @param   integer  type of search (1 -> 1 word at least, 2 -> all words,
  51      *                                   3 -> exact string, 4 -> regexp)
  52      *
  53      * @return  array    3 SQL querys (for count, display and delete results)
  54      *
  55      * @global  string   the url to retun to in case of errors
  56      */
  57     function PMA_getSearchSqls($table, $search_str, $search_option)
  58     {
  59         global $err_url;
  60
  61         // Statement types
  62         $sqlstr_select = 'SELECT';
  63         $sqlstr_delete = 'DELETE';
  64
  65         // Fields to select
  66         $local_query           = 'SHOW FIELDS FROM ' . PMA_backquote($GLOBALS['db']) . '.' . PMA_backquote($table);
  67         $res                   = @PMA_mysql_query($local_query) or PMA_mysqlDie('', $local_query, FALSE, $err_url);
  68         $res_cnt               = ($res ? mysql_num_rows($res) : 0);
  69         for ($i = 0; $i < $res_cnt; $i++) {
  70             $tblfields[]       = PMA_backquote(PMA_mysql_result($res, $i, 'field'));
  71         } // end if
  72         $sqlstr_fieldstoselect = ' ' . implode(', ', $tblfields);
  73         $tblfields_cnt         = count($tblfields);
  74         if ($res) {
  75             mysql_free_result($res);
  76         }
  77
  78         // Table to use
  79         $sqlstr_from = ' FROM ' . PMA_backquote($GLOBALS['db']) . '.' . PMA_backquote($table);
  80
  81         // Beginning of WHERE clause
  82         $sqlstr_where    = ' WHERE';
  83
  84         $search_words    = (($search_option > 2) ? array($search_str) : explode(' ', $search_str));
  85         $search_wds_cnt  = count($search_words);
  86
  87         $like_or_regex   = (($search_option == 4) ? 'REGEXP' : 'LIKE');
  88
  89         for ($i = 0; $i < $search_wds_cnt; $i++) {
  90             // Elimines empty values
  91             if (!empty($search_words[$i])) {
  92                 for ($j = 0; $j < $tblfields_cnt; $j++) {
  93                     $thefieldlikevalue[] = $tblfields[$j]
  94                                          . ' ' . $like_or_regex
  95                                          . ' \'' . $search_words[$i] . '\'';
  96                 } // end for
  97
  98                 $fieldslikevalues[]      = ($search_wds_cnt > 1)
  99                                          ? '(' . implode(' OR ', $thefieldlikevalue) . ')'
 100                                          : implode(' OR ', $thefieldlikevalue);
 101                 unset($thefieldlikevalue);
 102             } // end if
 103         } // end for
 104
 105         $implode_str  = ($search_option == 1 ? ' OR ' : ' AND ');
 106         $sqlstr_where .= ' ' . implode($implode_str, $fieldslikevalues);
 107         unset($fieldslikevalues);
 108
 109         // Builds complete queries
 110         $sql['select_fields'] = $sqlstr_select . $sqlstr_fieldstoselect . $sqlstr_from . $sqlstr_where;
 111         $sql['select_count']  = $sqlstr_select . ' COUNT(*) AS count' . $sqlstr_from . $sqlstr_where;
 112         $sql['delete']        = $sqlstr_delete . $sqlstr_from . $sqlstr_where;
 113
 114         return $sql;
 115     } // end of the "PMA_getSearchSqls()" function
 116
 117
 118     /**
 119      * Strip slashes if necessary
 120      */
 121     if (get_magic_quotes_gpc()) {
 122         $search_str               = stripslashes($search_str);
 123         if (isset($table)) {
 124             $table                = stripslashes($table);
 125         }
 126         else if (isset($table_select)) {
 127             $table_select_cnt     = count($table_select);
 128             reset($table_select);
 129             for ($i = 0; $i < $table_select_cnt; $i++) {
 130                 $table_select[$i] = stripslashes($table_select[$i]);
 131             } // end for
 132         } // end if... else if...
 133     } // end if
 134
 135
 136     /**
 137      * Displays the results
 138      */
 139     if (!empty($search_str) && !empty($search_option)) {
 140
 141         $original_search_str = $search_str;
 142         $search_str          = PMA_sqlAddslashes($search_str, TRUE);
 143
 144         // Get the true string to display as option's comment
 145         switch ($search_option) {
 146             case 1:
 147                 $option_str = ' (' . $strSearchOption1 . ')';
 148                 break;
 149             case 2:
 150                 $option_str = ' (' . $strSearchOption2 . ')';
 151                 break;
 152             case 3:
 153                 $option_str = ' (' . $strSearchOption3 . ')';
 154                 break;
 155             case 4:
 156                 $option_str = ' (' . $strSearchOption4 . ')';
 157                 break;
 158         } // end switch
 159
 160         // If $table is defined or if there is only one table in $table_select
 161         // set $onetable to the table's name (display is different if there is
 162         // only one table).
 163         //
 164         // Recall:
 165         //  $tables is an array with all tables in database $db
 166         //  $num_tables is the size of $tables
 167         if (isset($table)) {
 168             $onetable           = $table;
 169         }
 170         else if (isset($table_select)) {
 171             $num_selectedtables = count($table_select);
 172             if ($num_selectedtables == 1) {
 173                 $onetable       = $table_select[0];
 174             }
 175         }
 176         else if ($num_tables == 1) {
 177             $onetable           = $tables[0];
 178         }
 179         else {
 180             for ($i = 0; $i < $num_tables; $i++) {
 181                 $table_select[] = $tables[$i];
 182             }
 183             $num_selectedtables = $num_tables;
 184         } // end if... else if... else
 185         ?>
 186 <br />
 187
 188 <!-- Results form -->
 189 <form method="post" action="sql.php3" name="db_search_results_form">
 190     <input type="hidden" name="is_js_confirmed" value="0" />
 191     <input type="hidden" name="lang" value="<?php echo $lang; ?>" />
 192     <input type="hidden" name="convcharset" value="<?php echo $convcharset; ?>" />
 193     <input type="hidden" name="server" value="<?php echo $server; ?>" />
 194     <input type="hidden" name="db" value="<?php echo $db; ?>" />
 195     <input type="hidden" name="goto" value="db_details.php3" />
 196     <input type="hidden" name="pos" value="0" />
 197         <?php
 198         echo "\n";
 199         // Only one table defined in an variable $onetable
 200         if (isset($onetable)) {
 201             // Displays search string
 202             echo '    ' . sprintf($strSearchResultsFor, htmlspecialchars($original_search_str), $option_str) . "\n";
 203             echo '    <br />' . "\n";
 204
 205             // Gets the SQL statements
 206             $newsearchsqls = PMA_getSearchSqls($onetable, $search_str, $search_option);
 207
 208             // Executes the "COUNT" statement
 209             $local_query   = $newsearchsqls['select_count'];
 210             $res           = @PMA_mysql_query($local_query)  or PMA_mysqlDie('', $local_query, FALSE, $err_url);
 211             if ($res) {
 212                 $res_cnt   = PMA_mysql_result($res, 0, 'count');
 213                 mysql_free_result($res);
 214             } else {
 215                 $res_cnt   = 0;
 216             } // end if... else ...
 217             $num_search_result_total = $res_cnt;
 218
 219             echo '    <!-- Search results in table ' . $onetable . ' (' . $res_cnt . ') -->' . "\n"
 220                  . '    <br />' . "\n"
 221                  . '    ' . sprintf($strNumSearchResultsInTable, $res_cnt, htmlspecialchars($onetable)) . "\n";
 222
 223             if ($res_cnt > 0) {
 224                 echo '    <input id="checkbox_select_' . $i . '" type="radio" name="sql_query" value="' . htmlspecialchars($newsearchsqls['select_fields']) . '" onclick="this.form.submit()" />' . "\n";
 225                 echo '    <label for="checkbox_select_' . $i . '">' . $strBrowse . '</label>' . "\n";
 226                 echo '    <input id="checkbox_delete_' . $i . '" type="radio" name="sql_query" value="' . htmlspecialchars($newsearchsqls['delete']).'" onclick="this.form.submit()" />' . "\n";
 227                 echo '    <label for="checkbox_delete_' . $i . '">' . $strDelete . '</label>' . "\n";
 228             } // end if
 229         } // end only one table
 230
 231         // Several tables defined in the array $table_select
 232         else if (isset($table_select)) {
 233             // Displays search string
 234             echo '    ' . sprintf($strSearchResultsFor, htmlspecialchars($original_search_str), $option_str) . "\n";
 235             echo '    <ul>' . "\n";
 236
 237             $num_search_result_total = 0;
 238             for ($i = 0; $i < $num_selectedtables; $i++) {
 239                 // Gets the SQL statements
 240                 $newsearchsqls = PMA_getSearchSqls($table_select[$i], $search_str, $search_option);
 241
 242                 // Executes the "COUNT" statement
 243                 $local_query   = $newsearchsqls['select_count'];
 244                 $res           = @PMA_mysql_query($local_query)  or PMA_mysqlDie('', $local_query, FALSE, $err_url);
 245                 if ($res) {
 246                     $res_cnt   = PMA_mysql_result($res, 0, 'count');
 247                     mysql_free_result($res);
 248                 } else {
 249                     $res_cnt   = 0;
 250                 } // end if... else ...
 251                 $num_search_result_total += $res_cnt;
 252
 253                 echo '        <!-- Search results in table ' . $table_select[$i] . ' (' . $res_cnt . ') -->' . "\n"
 254                      . '        <li>' . "\n"
 255                      . '            ' . sprintf($strNumSearchResultsInTable, $res_cnt, htmlspecialchars($table_select[$i])) . "\n";
 256
 257                 if ($res_cnt > 0) {
 258                     echo '            <input id="checkbox_select_' . $i .'" type="radio" name="sql_query" value="' . htmlspecialchars($newsearchsqls['select_fields']) . '" onclick="this.form.submit()" />' . "\n";
 259                     echo '            <label for="checkbox_select_' . $i . '">' . $strBrowse . '</label>' . "\n";
 260                     echo '            <input id="checkbox_delete_' . $i . '" type="radio" name="sql_query" value="' . htmlspecialchars($newsearchsqls['delete']) . '" onclick="this.form.submit()" />' . "\n";
 261                     echo '            <label for="checkbox_delete_' . $i . '">' . $strDelete . '</label>' . "\n";
 262                 } // end if
 263
 264                 echo '        </li>' . "\n";
 265             } // end for
 266
 267             echo '    </ul>' . "\n";
 268             echo '    <p>' . sprintf($strNumSearchResultsTotal, $num_search_result_total) . '</p>' . "\n";
 269         } // end several tables
 270
 271         // Submit button if string found
 272         if ($num_search_result_total) {
 273             ?>
 274     <script type="text/javascript" language="javascript">
 275     <!--
 276         // Fake js to allow the use of the <noscript> tag
 277     //-->
 278     </script>
 279     <noscript>
 280         <input type="submit" value="<?php echo $strGo; ?>" />
 281     </noscript>
 282             <?php
 283         } // end if
 284
 285         echo "\n";
 286         ?>
 287 </form>
 288 <hr width="100%">
 289         <?php
 290     } // end if (!empty($search_str) && !empty($search_option))
 291
 292 } // end 1.
 293
 294
 295 /**
 296  * 2. Displays the main search form
 297  */
 298 echo "\n";
 299 $searched          = (isset($original_search_str))
 300                    ? htmlspecialchars($original_search_str)
 301                    : '';
 302 if (empty($search_option)) {
 303     $search_option = 1;
 304 }
 305 ?>
 306 <!-- Display search form -->
 307 <p align="center">
 308     <b><?php echo $strSearchFormTitle; ?></b>
 309 </p>
 310
 311 <a name="db_search"></a>
 312 <form method="post" action="db_search.php3" name="db_search">
 313     <input type="hidden" name="lang" value="<?php echo $lang; ?>" />
 314     <input type="hidden" name="convcharset" value="<?php echo $convcharset; ?>" />
 315     <input type="hidden" name="server" value="<?php echo $server; ?>" />
 316     <input type="hidden" name="db" value="<?php echo $db; ?>" />
 317
 318     <table>
 319     <tr>
 320         <td>
 321             <?php echo $strSearchNeedle; ?>&nbsp;
 322         </td>
 323         <td>
 324             <input type="text" name="search_str" size="30" value="<?php echo $searched; ?>" />
 325         </td>
 326     </tr>
 327     <tr>
 328         <td colspan="2">&nbsp;</td>
 329     </tr>
 330     <tr>
 331         <td valign="top">
 332             <?php echo $strSearchType; ?>&nbsp;
 333         </td>
 334         <td>
 335             <input type="radio" id="search_option_1" name="search_option" value="1"<?php if ($search_option == 1) echo ' checked="checked"'; ?> />
 336             <label for="search_option_1"><?php echo $strSearchOption1; ?></label>&nbsp;*<br />
 337             <input type="radio" id="search_option_2" name="search_option" value="2"<?php if ($search_option == 2) echo ' checked="checked"'; ?> />
 338             <label for="search_option_2"><?php echo $strSearchOption2; ?></label>&nbsp;*<br />
 339             <input type="radio" id="search_option_3" name="search_option" value="3"<?php if ($search_option == 3) echo ' checked="checked"'; ?> />
 340             <label for="search_option_3"><?php echo $strSearchOption3; ?></label><br />
 341             <input type="radio" id="search_option_4" name="search_option" value="4"<?php if ($search_option == 4) echo ' checked="checked"'; ?> />
 342             <label for="search_option_4"><?php echo $strSearchOption4 . '</label> ' . PMA_showDocuShort('R/e/Regexp.html'); ?><br />
 343             <br />
 344             *&nbsp;<?php echo $strSplitWordsWithSpace . "\n"; ?>
 345         </td>
 346     </tr>
 347     <tr>
 348         <td colspan="2">&nbsp;</td>
 349     </tr>
 350     <tr>
 351         <td valign="top">
 352             <?php echo $strSearchInTables; ?>&nbsp;
 353         </td>
 354         <td>
 355 <?php
 356 if ($num_tables > 1) {
 357     $i = 0;
 358
 359     echo '            <select name="table_select[]" size="6"  multiple="multiple">' . "\n";
 360     while ($i < $num_tables) {
 361         if (!empty($unselectall)) {
 362             $is_selected = '';
 363         }
 364         else if ((isset($table_select) && PMA_isInto($tables[$i], $table_select) != -1)
 365                 || (!empty($selectall))
 366                 || (isset($onetable) && $onetable == $tables[$i])) {
 367             $is_selected = ' selected="selected"';
 368         }
 369         else {
 370             $is_selected = '';
 371         }
 372
 373         echo '                <option value="' . htmlspecialchars($tables[$i]) . '"' . $is_selected . '>' . htmlspecialchars($tables[$i]) . '</option>' . "\n";
 374         $i++;
 375     } // end while
 376     echo '            </select>' . "\n";
 377     ?>
 378             <br />
 379             <a href="db_search.php3?<?php echo $url_query; ?>&amp;selectall=1#db_search" onclick="setSelectOptions('db_search', 'table_select[]', true); return false;"><?php echo $strSelectAll; ?></a>
 380             &nbsp;/&nbsp;
 381             <a href="db_search.php3?<?php echo $url_query; ?>&amp;unselectall=1#db_search" onclick="setSelectOptions('db_search', 'table_select[]', false); return false;"><?php echo $strUnselectAll; ?></a>
 382     <?php
 383 }
 384 else {
 385     echo "\n";
 386     echo '            ' . htmlspecialchars($tables[0]) . "\n";
 387     echo '            <input type="hidden" name="table" value="' . htmlspecialchars($tables[0]) . '" />' . "\n";
 388 } // end if... else...
 389
 390 echo"\n";
 391 ?>
 392         </td>
 393     </tr>
 394
 395     <tr>
 396         <td colspan="2">&nbsp;</td>
 397     </tr>
 398     <tr>
 399         <td colspan="2"><input type="submit" name="submit_search" value="<?php echo $strGo; ?>" /></td>
 400     </tr>
 401     </table>
 402 </form>
 403
 404
 405 <?php
 406 /**
 407  * Displays the footer
 408  */
 409 echo "\n";
 410 require('./footer.inc.php3');
 411 ?>