db_search.php

   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.php');
  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.php';
  18
  19
  20 /**
  21  * Get the list of tables from the current database
  22  */
  23 $tables     = PMA_DBI_get_tables($db);
  24 $num_tables = count($tables);
  25
  26
  27 /**
  28  * Displays top links
  29  */
  30 $sub_part = '';
  31 require('./db_details_links.php');
  32
  33
  34 /**
  35  * 1. Main search form has been submitted
  36  */
  37 if (isset($submit_search)) {
  38
  39     /**
  40      * Builds the SQL search query
  41      *
  42      * @param   string   the table name
  43      * @param   string   the string to search
  44      * @param   integer  type of search (1 -> 1 word at least, 2 -> all words,
  45      *                                   3 -> exact string, 4 -> regexp)
  46      *
  47      * @return  array    3 SQL querys (for count, display and delete results)
  48      *
  49      * @global  string   the url to return to in case of errors
  50      */
  51     function PMA_getSearchSqls($table, $search_str, $search_option)
  52     {
  53         global $err_url, $charset_connection;
  54
  55         // Statement types
  56         $sqlstr_select = 'SELECT';
  57         $sqlstr_delete = 'DELETE';
  58
  59         // Fields to select
  60         $res                  = PMA_DBI_query('SHOW ' . (PMA_MYSQL_INT_VERSION >= 40100 ? 'FULL ' : '') . 'FIELDS FROM ' . PMA_backquote($table) . ' FROM ' . PMA_backquote($GLOBALS['db']) . ';');
  61         while ($current = PMA_DBI_fetch_assoc($res)) {
  62             if (PMA_MYSQL_INT_VERSION >= 40100) {
  63                 list($current['Charset']) = explode('_', $current['Collation']);
  64             }
  65             $current['Field'] = PMA_backquote($current['Field']);
  66             $tblfields[]      = $current;
  67         } // while
  68         PMA_DBI_free_result($res);
  69         unset($current, $res);
  70         $tblfields_cnt         = count($tblfields);
  71
  72         // Table to use
  73         $sqlstr_from = ' FROM ' . PMA_backquote($GLOBALS['db']) . '.' . PMA_backquote($table);
  74
  75         // Beginning of WHERE clause
  76         $sqlstr_where    = ' WHERE';
  77
  78         $search_words    = (($search_option > 2) ? array($search_str) : explode(' ', $search_str));
  79         $search_wds_cnt  = count($search_words);
  80
  81         $like_or_regex   = (($search_option == 4) ? 'REGEXP' : 'LIKE');
  82         $automatic_wildcard   = (($search_option <3) ? '%' : '');
  83
  84         for ($i = 0; $i < $search_wds_cnt; $i++) {
  85             // Eliminates empty values
  86             // In MySQL 4.1, if a field has no collation we get NULL in Charset
  87             // but in MySQL 5.0.x we get ''
  88             if (!empty($search_words[$i])) {
  89                 for ($j = 0; $j < $tblfields_cnt; $j++) {
  90                     if (PMA_MYSQL_INT_VERSION >= 40100 && $tblfields[$j]['Charset'] != $charset_connection && $tblfields[$j]['Charset'] != 'NULL' && $tblfields[$j]['Charset'] != '') {
  91                         $prefix = 'CONVERT(_utf8 ';
  92                         $suffix = ' USING ' . $tblfields[$j]['Charset'] . ') COLLATE ' . $tblfields[$j]['Collation'];
  93                     } else {
  94                         $prefix = $suffix = '';
  95                     }
  96                     $thefieldlikevalue[] = $tblfields[$j]['Field']
  97                                          . ' ' . $like_or_regex . ' '
  98                                          . $prefix
  99                                          . '\''
 100                                          . $automatic_wildcard
 101                                          . $search_words[$i]
 102                                          . $automatic_wildcard . '\''
 103                                          . $suffix;
 104                 } // end for
 105
 106                 $fieldslikevalues[]      = ($search_wds_cnt > 1)
 107                                          ? '(' . implode(' OR ', $thefieldlikevalue) . ')'
 108                                          : implode(' OR ', $thefieldlikevalue);
 109                 unset($thefieldlikevalue);
 110             } // end if
 111         } // end for
 112
 113         $implode_str  = ($search_option == 1 ? ' OR ' : ' AND ');
 114         $sqlstr_where .= ' ' . implode($implode_str, $fieldslikevalues);
 115         unset($fieldslikevalues);
 116
 117         // Builds complete queries
 118         $sql['select_fields'] = $sqlstr_select . ' * ' . $sqlstr_from . $sqlstr_where;
 119         $sql['select_count']  = $sqlstr_select . ' COUNT(*) AS count' . $sqlstr_from . $sqlstr_where;
 120         $sql['delete']        = $sqlstr_delete . $sqlstr_from . $sqlstr_where;
 121
 122         return $sql;
 123     } // end of the "PMA_getSearchSqls()" function
 124
 125
 126     /**
 127      * Displays the results
 128      */
 129     if (!empty($search_str) && !empty($search_option)) {
 130
 131         $original_search_str = $search_str;
 132         $search_str          = PMA_sqlAddslashes($search_str, TRUE);
 133
 134         // Get the true string to display as option's comment
 135         switch ($search_option) {
 136             case 1:
 137                 $option_str = ' (' . $strSearchOption1 . ')';
 138                 break;
 139             case 2:
 140                 $option_str = ' (' . $strSearchOption2 . ')';
 141                 break;
 142             case 3:
 143                 $option_str = ' (' . $strSearchOption3 . ')';
 144                 break;
 145             case 4:
 146                 $option_str = ' (' . $strSearchOption4 . ')';
 147                 break;
 148         } // end switch
 149
 150         // If $table is defined or if there is only one table in $table_select
 151         // set $onetable to the table's name (display is different if there is
 152         // only one table).
 153         //
 154         // Recall:
 155         //  $tables is an array with all tables in database $db
 156         //  $num_tables is the size of $tables
 157         if (isset($table)) {
 158             $onetable           = $table;
 159         }
 160         else if (isset($table_select)) {
 161             $num_selectedtables = count($table_select);
 162             if ($num_selectedtables == 1) {
 163                 $onetable       = $table_select[0];
 164             }
 165         }
 166         else if ($num_tables == 1) {
 167             $onetable           = $tables[0];
 168         }
 169         else {
 170             for ($i = 0; $i < $num_tables; $i++) {
 171                 $table_select[] = $tables[$i];
 172             }
 173             $num_selectedtables = $num_tables;
 174         } // end if... else if... else
 175         ?>
 176 <br />
 177
 178         <?php
 179         $url_sql_query = PMA_generate_common_url($db)
 180                    . '&amp;goto=db_details.php'
 181                    . '&amp;pos=0'
 182                    . '&amp;is_js_confirmed=0';
 183
 184         // Only one table defined in an variable $onetable
 185         if (isset($onetable)) {
 186             // Displays search string
 187             echo '    ' . sprintf($strSearchResultsFor, htmlspecialchars($original_search_str), $option_str) . "\n";
 188             echo '    <br />' . "\n";
 189
 190             // Gets the SQL statements
 191             $newsearchsqls = PMA_getSearchSqls($onetable, $search_str, $search_option);
 192
 193             // Executes the "COUNT" statement
 194             $res                     = PMA_DBI_query($newsearchsqls['select_count']);
 195             $res_cnt                 = PMA_DBI_fetch_assoc($res);
 196             $res_cnt                 = $res_cnt['count'];
 197             PMA_DBI_free_result($res);
 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.php?' . $url_sql_query
 206                     . '&amp;sql_query=' .urlencode($newsearchsqls['select_fields']),
 207                     $strBrowse, '') .  "</td>\n";
 208
 209                    echo '<td>' . PMA_linkOrButton('sql.php?' . $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                 $res           = PMA_DBI_query($newsearchsqls['select_count']);
 230                 $res_cnt       = PMA_DBI_fetch_assoc($res);
 231                 $res_cnt       = $res_cnt['count'];
 232                 PMA_DBI_free_result($res);
 233                 unset($res);
 234                 $num_search_result_total += $res_cnt;
 235
 236                 echo '        <!-- Search results in table ' . $table_select[$i] . ' (' . $res_cnt . ') -->' . "\n"
 237                      . '        <li>' . "\n"
 238                      . '            <table><tr><td>' . sprintf($strNumSearchResultsInTable, $res_cnt, htmlspecialchars($table_select[$i])) . "</td>\n";
 239
 240                 if ($res_cnt > 0) {
 241                    echo '<td>' . PMA_linkOrButton('sql.php?' . $url_sql_query
 242                     . '&amp;sql_query=' .urlencode($newsearchsqls['select_fields']),
 243                     $strBrowse, '') .  "</td>\n";
 244
 245                    echo '<td>' . PMA_linkOrButton('sql.php?' . $url_sql_query
 246                     . '&amp;sql_query=' .urlencode($newsearchsqls['delete']),
 247                     $strDelete, $newsearchsqls['delete']) .  "</td>\n";
 248
 249                 } // end if
 250
 251                 echo '        </tr></table></li>' . "\n";
 252             } // end for
 253
 254             echo '    </ul>' . "\n";
 255             echo '    <p>' . sprintf($strNumSearchResultsTotal, $num_search_result_total) . '</p>' . "\n";
 256         } // end several tables
 257
 258         echo "\n";
 259         ?>
 260 <hr width="100%">
 261         <?php
 262     } // end if (!empty($search_str) && !empty($search_option))
 263
 264 } // end 1.
 265
 266
 267 /**
 268  * 2. Displays the main search form
 269  */
 270 echo "\n";
 271 $searched          = (isset($original_search_str))
 272                    ? htmlspecialchars($original_search_str)
 273                    : '';
 274 if (empty($search_option)) {
 275     $search_option = 1;
 276 }
 277 ?>
 278 <!-- Display search form -->
 279 <a name="db_search"></a>
 280 <form method="post" action="db_search.php" name="db_search">
 281     <?php echo PMA_generate_common_hidden_inputs($db); ?>
 282
 283     <table border="0" cellpadding="3" cellspacing="0">
 284     <tr>
 285         <th class="tblHeaders" align="center" colspan="2"><?php echo $strSearchFormTitle; ?></th>
 286     </tr>
 287     <tr><td colspan="2"></td></tr>
 288     <tr>
 289         <td bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
 290             <?php echo $strSearchNeedle; ?>&nbsp;<br />
 291         </td>
 292         <td bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
 293             <input type="text" name="search_str" size="60" value="<?php echo $searched; ?>" />
 294         </td>
 295     </tr>
 296     <tr><td colspan="2"></td></tr><tr>
 297         <td align="right" valign="top" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
 298             <?php echo $strSearchType; ?>&nbsp;
 299         </td>
 300         <td bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
 301             <input type="radio" id="search_option_1" name="search_option" value="1"<?php if ($search_option == 1) echo ' checked="checked"'; ?> /><label for="search_option_1"><?php echo $strSearchOption1; ?></label>&nbsp;*<br />
 302             <input type="radio" id="search_option_2" name="search_option" value="2"<?php if ($search_option == 2) echo ' checked="checked"'; ?> /><label for="search_option_2"><?php echo $strSearchOption2; ?></label>&nbsp;*<br />
 303             <input type="radio" id="search_option_3" name="search_option" value="3"<?php if ($search_option == 3) echo ' checked="checked"'; ?> /><label for="search_option_3"><?php echo $strSearchOption3; ?></label><br />
 304             <input type="radio" id="search_option_4" name="search_option" value="4"<?php if ($search_option == 4) echo ' checked="checked"'; ?> /><label for="search_option_4"><?php echo $strSearchOption4; ?></label><?php echo PMA_showMySQLDocu('Regexp', 'Regexp'); ?><br />
 305             <br />
 306             *&nbsp;<?php echo $strSplitWordsWithSpace . "\n"; ?>
 307         </td>
 308     </tr>
 309     <tr><td colspan="2"></td></tr>
 310     <tr>
 311         <td align="right" valign="top" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
 312             <?php echo $strSearchInTables; ?>&nbsp;
 313         </td>
 314         <td rowspan="2" bgcolor="<?php echo $cfg['BgcolorOne']; ?>">
 315 <?php
 316 $strDoSelectAll='&nbsp;';
 317 if ($num_tables > 1) {
 318     $i = 0;
 319
 320     echo '            <select name="table_select[]" size="6"  multiple="multiple">' . "\n";
 321     while ($i < $num_tables) {
 322         if (!empty($unselectall)) {
 323             $is_selected = '';
 324         }
 325         else if ((isset($table_select) && PMA_isInto($tables[$i], $table_select) != -1)
 326                 || (!empty($selectall))
 327                 || (isset($onetable) && $onetable == $tables[$i])) {
 328             $is_selected = ' selected="selected"';
 329         }
 330         else {
 331             $is_selected = '';
 332         }
 333
 334         echo '                <option value="' . htmlspecialchars($tables[$i]) . '"' . $is_selected . '>' . htmlspecialchars($tables[$i]) . '</option>' . "\n";
 335         $i++;
 336     } // end while
 337     echo '            </select>' . "\n";
 338     $strDoSelectAll = '<a href="db_search.php?' . $url_query . '&amp;selectall=1#db_search"'
 339                     . ' onclick="setSelectOptions(\'db_search\', \'table_select[]\', true); return false;">' . $strSelectAll . '</a>'
 340                     . '&nbsp;/&nbsp;'
 341                     . '<a href="db_search.php?' . $url_query . '&amp;unselectall=1#db_search"'
 342                     . ' onclick="setSelectOptions(\'db_search\', \'table_select[]\', false); return false;">' . $strUnselectAll . '</a>';
 343 }
 344 else {
 345     echo "\n";
 346     echo '            ' . htmlspecialchars($tables[0]) . "\n";
 347     echo '            <input type="hidden" name="table" value="' . htmlspecialchars($tables[0]) . '" />' . "\n";
 348 } // end if... else...
 349
 350 echo"\n";
 351 ?>
 352         </td>
 353     </tr><tr><td align="right" valign="bottom" bgcolor="<?php echo $cfg['BgcolorOne']; ?>"><?php echo $strDoSelectAll; ?></td></tr>
 354     <tr><td colspan="2"></td>
 355     </tr><tr>
 356         <td colspan="2" align="right" class="tblHeaders"><input type="submit" name="submit_search" value="<?php echo $strGo; ?>" id="buttonGo" /></td>
 357     </tr>
 358     </table>
 359 </form>
 360
 361
 362 <?php
 363 /**
 364  * Displays the footer
 365  */
 366 echo "\n";
 367 require_once('./footer.inc.php');
 368 ?>