db_search.php3

   1 <?php
   2 /* $Id$ */
   3 // vim: expandtab sw=4 ts=4 sts=4:
   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($table) . ' FROM ' . PMA_backquote($GLOBALS['db']);
  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         $automatic_wildcard   = (($search_option <3) ? '%' : '');
  89
  90         for ($i = 0; $i < $search_wds_cnt; $i++) {
  91             // Elimines empty values
  92             if (!empty($search_words[$i])) {
  93                 for ($j = 0; $j < $tblfields_cnt; $j++) {
  94                     $thefieldlikevalue[] = $tblfields[$j]
  95                                          . ' ' . $like_or_regex
  96                                          . ' \''
  97                                          . $automatic_wildcard
  98                                          . $search_words[$i]
  99                                          . $automatic_wildcard . '\'';
 100                 } // end for
 101
 102                 $fieldslikevalues[]      = ($search_wds_cnt > 1)
 103                                          ? '(' . implode(' OR ', $thefieldlikevalue) . ')'
 104                                          : implode(' OR ', $thefieldlikevalue);
 105                 unset($thefieldlikevalue);
 106             } // end if
 107         } // end for
 108
 109         $implode_str  = ($search_option == 1 ? ' OR ' : ' AND ');
 110         $sqlstr_where .= ' ' . implode($implode_str, $fieldslikevalues);
 111         unset($fieldslikevalues);
 112
 113         // Builds complete queries
 114         $sql['select_fields'] = $sqlstr_select . $sqlstr_fieldstoselect . $sqlstr_from . $sqlstr_where;
 115         $sql['select_count']  = $sqlstr_select . ' COUNT(*) AS count' . $sqlstr_from . $sqlstr_where;
 116         $sql['delete']        = $sqlstr_delete . $sqlstr_from . $sqlstr_where;
 117
 118         return $sql;
 119     } // end of the "PMA_getSearchSqls()" function
 120
 121
 122     /**
 123      * Strip slashes if necessary
 124      */
 125     if (get_magic_quotes_gpc()) {
 126         $search_str               = stripslashes($search_str);
 127         if (isset($table)) {
 128             $table                = stripslashes($table);
 129         }
 130         else if (isset($table_select)) {
 131             $table_select_cnt     = count($table_select);
 132             reset($table_select);
 133             for ($i = 0; $i < $table_select_cnt; $i++) {
 134                 $table_select[$i] = stripslashes($table_select[$i]);
 135             } // end for
 136         } // end if... else if...
 137     } // end if
 138
 139
 140     /**
 141      * Displays the results
 142      */
 143     if (!empty($search_str) && !empty($search_option)) {
 144
 145         $original_search_str = $search_str;
 146         $search_str          = PMA_sqlAddslashes($search_str, TRUE);
 147
 148         // Get the true string to display as option's comment
 149         switch ($search_option) {
 150             case 1:
 151                 $option_str = ' (' . $strSearchOption1 . ')';
 152                 break;
 153             case 2:
 154                 $option_str = ' (' . $strSearchOption2 . ')';
 155                 break;
 156             case 3:
 157                 $option_str = ' (' . $strSearchOption3 . ')';
 158                 break;
 159             case 4:
 160                 $option_str = ' (' . $strSearchOption4 . ')';
 161                 break;
 162         } // end switch
 163
 164         // If $table is defined or if there is only one table in $table_select
 165         // set $onetable to the table's name (display is different if there is
 166         // only one table).
 167         //
 168         // Recall:
 169         //  $tables is an array with all tables in database $db
 170         //  $num_tables is the size of $tables
 171         if (isset($table)) {
 172             $onetable           = $table;
 173         }
 174         else if (isset($table_select)) {
 175             $num_selectedtables = count($table_select);
 176             if ($num_selectedtables == 1) {
 177                 $onetable       = $table_select[0];
 178             }
 179         }
 180         else if ($num_tables == 1) {
 181             $onetable           = $tables[0];
 182         }
 183         else {
 184             for ($i = 0; $i < $num_tables; $i++) {
 185                 $table_select[] = $tables[$i];
 186             }
 187             $num_selectedtables = $num_tables;
 188         } // end if... else if... else
 189         ?>
 190 <br />
 191
 192         <?php
 193         $url_sql_query = PMA_generate_common_url($db)
 194                    . '&amp;goto=db_details.php3'
 195                    . '&amp;pos=0'
 196                    . '&amp;is_js_confirmed=0';
 197
 198         // Only one table defined in an variable $onetable
 199         if (isset($onetable)) {
 200             // Displays search string
 201             echo '    ' . sprintf($strSearchResultsFor, htmlspecialchars($original_search_str), $option_str) . "\n";
 202             echo '    <br />' . "\n";
 203
 204             // Gets the SQL statements
 205             $newsearchsqls = PMA_getSearchSqls($onetable, $search_str, $search_option);
 206
 207             // Executes the "COUNT" statement
 208             $local_query   = $newsearchsqls['select_count'];
 209             $res           = @PMA_mysql_query($local_query)  or PMA_mysqlDie('', $local_query, FALSE, $err_url);
 210             if ($res) {
 211                 $res_cnt   = PMA_mysql_result($res, 0, 'count');
 212                 mysql_free_result($res);
 213             } else {
 214                 $res_cnt   = 0;
 215             } // end if... else ...
 216             $num_search_result_total = $res_cnt;
 217
 218             echo '    <!-- Search results in table ' . $onetable . ' (' . $res_cnt . ') -->' . "\n"
 219                  . '    <br />' . "\n"
 220                  . '    <table><tr><td>' . sprintf($strNumSearchResultsInTable, $res_cnt, htmlspecialchars($onetable)) . "</td>\n";
 221
 222             if ($res_cnt > 0) {
 223                    echo '<td>' . PMA_linkOrButton('sql.php3?' . $url_sql_query
 224                     . '&amp;sql_query=' .urlencode($newsearchsqls['select_fields']),
 225                     $strBrowse, '') .  "</td>\n";
 226
 227                    echo '<td>' . PMA_linkOrButton('sql.php3?' . $url_sql_query
 228                     . '&amp;sql_query=' .urlencode($newsearchsqls['delete']),
 229                     $strDelete, $newsearchsqls['delete']) .  "</td>\n";
 230
 231             } // end if
 232             echo '</tr></table>' . "\n";
 233         } // end only one table
 234
 235         // Several tables defined in the array $table_select
 236         else if (isset($table_select)) {
 237             // Displays search string
 238             echo '    ' . sprintf($strSearchResultsFor, htmlspecialchars($original_search_str), $option_str) . "\n";
 239             echo '    <ul>' . "\n";
 240
 241             $num_search_result_total = 0;
 242             for ($i = 0; $i < $num_selectedtables; $i++) {
 243                 // Gets the SQL statements
 244                 $newsearchsqls = PMA_getSearchSqls($table_select[$i], $search_str, $search_option);
 245
 246                 // Executes the "COUNT" statement
 247                 $local_query   = $newsearchsqls['select_count'];
 248                 $res           = @PMA_mysql_query($local_query)  or PMA_mysqlDie('', $local_query, FALSE, $err_url);
 249                 if ($res) {
 250                     $res_cnt   = PMA_mysql_result($res, 0, 'count');
 251                     mysql_free_result($res);
 252                 } else {
 253                     $res_cnt   = 0;
 254                 } // end if... else ...
 255                 $num_search_result_total += $res_cnt;
 256
 257                 echo '        <!-- Search results in table ' . $table_select[$i] . ' (' . $res_cnt . ') -->' . "\n"
 258                      . '        <li>' . "\n"
 259                      . '            <table><tr><td>' . sprintf($strNumSearchResultsInTable, $res_cnt, htmlspecialchars($table_select[$i])) . "</td>\n";
 260
 261                 if ($res_cnt > 0) {
 262                    echo '<td>' . PMA_linkOrButton('sql.php3?' . $url_sql_query
 263                     . '&amp;sql_query=' .urlencode($newsearchsqls['select_fields']),
 264                     $strBrowse, '') .  "</td>\n";
 265
 266                    echo '<td>' . PMA_linkOrButton('sql.php3?' . $url_sql_query
 267                     . '&amp;sql_query=' .urlencode($newsearchsqls['delete']),
 268                     $strDelete, $newsearchsqls['delete']) .  "</td>\n";
 269
 270                 } // end if
 271
 272                 echo '        </tr></table></li>' . "\n";
 273             } // end for
 274
 275             echo '    </ul>' . "\n";
 276             echo '    <p>' . sprintf($strNumSearchResultsTotal, $num_search_result_total) . '</p>' . "\n";
 277         } // end several tables
 278
 279         echo "\n";
 280         ?>
 281 <hr width="100%">
 282         <?php
 283     } // end if (!empty($search_str) && !empty($search_option))
 284
 285 } // end 1.
 286
 287
 288 /**
 289  * 2. Displays the main search form
 290  */
 291 echo "\n";
 292 $searched          = (isset($original_search_str))
 293                    ? htmlspecialchars($original_search_str)
 294                    : '';
 295 if (empty($search_option)) {
 296     $search_option = 1;
 297 }
 298 ?>
 299 <!-- Display search form -->
 300 <p align="center">
 301     <b><?php echo $strSearchFormTitle; ?></b>
 302 </p>
 303
 304 <a name="db_search"></a>
 305 <form method="post" action="db_search.php3" name="db_search">
 306     <?php echo PMA_generate_common_hidden_inputs($db); ?>
 307
 308     <table>
 309     <tr>
 310         <td>
 311             <?php echo $strSearchNeedle; ?>&nbsp;
 312         </td>
 313         <td>
 314             <input type="text" name="search_str" size="30" value="<?php echo $searched; ?>" />
 315         </td>
 316     </tr>
 317     <tr>
 318         <td colspan="2">&nbsp;</td>
 319     </tr>
 320     <tr>
 321         <td valign="top">
 322             <?php echo $strSearchType; ?>&nbsp;
 323         </td>
 324         <td>
 325             <input type="radio" id="search_option_1" name="search_option" value="1"<?php if ($search_option == 1) echo ' checked="checked"'; ?> />
 326             <label for="search_option_1"><?php echo $strSearchOption1; ?></label>&nbsp;*<br />
 327             <input type="radio" id="search_option_2" name="search_option" value="2"<?php if ($search_option == 2) echo ' checked="checked"'; ?> />
 328             <label for="search_option_2"><?php echo $strSearchOption2; ?></label>&nbsp;*<br />
 329             <input type="radio" id="search_option_3" name="search_option" value="3"<?php if ($search_option == 3) echo ' checked="checked"'; ?> />
 330             <label for="search_option_3"><?php echo $strSearchOption3; ?></label><br />
 331             <input type="radio" id="search_option_4" name="search_option" value="4"<?php if ($search_option == 4) echo ' checked="checked"'; ?> />
 332             <label for="search_option_4"><?php echo $strSearchOption4 . '</label> ' . PMA_showMySQLDocu('Regexp', 'Regexp'); ?><br />
 333             <br />
 334             *&nbsp;<?php echo $strSplitWordsWithSpace . "\n"; ?>
 335         </td>
 336     </tr>
 337     <tr>
 338         <td colspan="2">&nbsp;</td>
 339     </tr>
 340     <tr>
 341         <td valign="top">
 342             <?php echo $strSearchInTables; ?>&nbsp;
 343         </td>
 344         <td>
 345 <?php
 346 if ($num_tables > 1) {
 347     $i = 0;
 348
 349     echo '            <select name="table_select[]" size="6"  multiple="multiple">' . "\n";
 350     while ($i < $num_tables) {
 351         if (!empty($unselectall)) {
 352             $is_selected = '';
 353         }
 354         else if ((isset($table_select) && PMA_isInto($tables[$i], $table_select) != -1)
 355                 || (!empty($selectall))
 356                 || (isset($onetable) && $onetable == $tables[$i])) {
 357             $is_selected = ' selected="selected"';
 358         }
 359         else {
 360             $is_selected = '';
 361         }
 362
 363         echo '                <option value="' . htmlspecialchars($tables[$i]) . '"' . $is_selected . '>' . htmlspecialchars($tables[$i]) . '</option>' . "\n";
 364         $i++;
 365     } // end while
 366     echo '            </select>' . "\n";
 367     ?>
 368             <br />
 369             <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>
 370             &nbsp;/&nbsp;
 371             <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>
 372     <?php
 373 }
 374 else {
 375     echo "\n";
 376     echo '            ' . htmlspecialchars($tables[0]) . "\n";
 377     echo '            <input type="hidden" name="table" value="' . htmlspecialchars($tables[0]) . '" />' . "\n";
 378 } // end if... else...
 379
 380 echo"\n";
 381 ?>
 382         </td>
 383     </tr>
 384
 385     <tr>
 386         <td colspan="2">&nbsp;</td>
 387     </tr>
 388     <tr>
 389         <td colspan="2"><input type="submit" name="submit_search" value="<?php echo $strGo; ?>" /></td>
 390     </tr>
 391     </table>
 392 </form>
 393
 394
 395 <?php
 396 /**
 397  * Displays the footer
 398  */
 399 echo "\n";
 400 require('./footer.inc.php3');
 401 ?>