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