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