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 require_once('./libraries/common.lib.php');
   9
  10 /**
  11  * Gets some core libraries and send headers
  12  */
  13 require('./libraries/db_details_common.inc.php');
  14 // If config variable $cfg['Usedbsearch'] is on FALSE : exit.
  15 if (!$cfg['UseDbSearch']) {
  16     PMA_mysqlDie($strAccessDenied, '', FALSE, $err_url);
  17 } // end if
  18 $url_query .= '&amp;goto=db_search.php';
  19 $url_params['goto'] = 'db_search.php';
  20
  21 /**
  22  * Get the list of tables from the current database
  23  */
  24 $tables     = PMA_DBI_get_tables($GLOBALS['db']);
  25 $num_tables = count( $tables );
  26
  27 /**
  28  * Displays top links
  29  */
  30 $sub_part = '';
  31 require('./libraries/db_details_links.inc.php');
  32
  33
  34 /**
  35  * 1. Main search form has been submitted
  36  */
  37 if (isset($_REQUEST['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         // here, I think we need to still use the COUNT clause, even for
 120         // VIEWs, anyway we have a WHERE clause that should limit results
 121         $sql['select_count']  = $sqlstr_select . ' COUNT(*) AS count' . $sqlstr_from . $sqlstr_where;
 122         $sql['delete']        = $sqlstr_delete . $sqlstr_from . $sqlstr_where;
 123
 124         return $sql;
 125     } // end of the "PMA_getSearchSqls()" function
 126
 127
 128     /**
 129      * Displays the results
 130      */
 131     if (!empty($_REQUEST['search_str']) && !empty($_REQUEST['search_option'])) {
 132
 133         $original_search_str = $_REQUEST['search_str'];
 134         $search_str          = PMA_sqlAddslashes($_REQUEST['search_str'], TRUE);
 135
 136         // Get the true string to display as option's comment
 137         switch ($_REQUEST['search_option']) {
 138             case 1:
 139                 $option_str = ' (' . $strSearchOption1 . ')';
 140                 $search_option = 1;
 141                 break;
 142             case 2:
 143                 $option_str = ' (' . $strSearchOption2 . ')';
 144                 $search_option = 2;
 145                 break;
 146             case 3:
 147                 $option_str = ' (' . $strSearchOption3 . ')';
 148                 $search_option = 3;
 149                 break;
 150             case 4:
 151                 $option_str = ' (' . $strSearchOption4 . ')';
 152                 $search_option = 4;
 153                 break;
 154         } // end switch
 155
 156         $this_url_params = array(
 157             'db'    => $GLOBALS['db'],
 158             'goto'  => 'db_details.php',
 159             'pos'   => 0,
 160             'is_js_confirmed' => 0,
 161         );
 162
 163         // Displays search string
 164         echo '<br />' . "\n"
 165             .'<table class="data">' . "\n"
 166             .'<caption class="tblHeaders">' . "\n"
 167             .sprintf($strSearchResultsFor,
 168                 htmlspecialchars($original_search_str), $option_str) . "\n"
 169             .'</caption>' . "\n";
 170
 171         $num_search_result_total = 0;
 172         $odd_row = true;
 173
 174         foreach ( $_REQUEST['table_select'] as $each_table ) {
 175             // Gets the SQL statements
 176             $newsearchsqls = PMA_getSearchSqls($each_table,
 177                 $search_str, $search_option);
 178
 179             // Executes the "COUNT" statement
 180             $res_cnt = PMA_DBI_fetch_value($newsearchsqls['select_count']);
 181             $num_search_result_total += $res_cnt;
 182
 183             echo '<tr class="' . ( $odd_row ? 'odd' : 'even' ) . '">'
 184                 .'<td>' . sprintf($strNumSearchResultsInTable, $res_cnt,
 185                     htmlspecialchars($each_table)) . "</td>\n";
 186
 187             if ($res_cnt > 0) {
 188                 $this_url_params['sql_query'] = $newsearchsqls['select_fields'];
 189                 echo '<td>' . PMA_linkOrButton(
 190                         'sql.php' . PMA_generate_common_url($this_url_params),
 191                         $strBrowse, '') .  "</td>\n";
 192
 193                 $this_url_params['sql_query'] = $newsearchsqls['delete'];
 194                 echo '<td>' . PMA_linkOrButton(
 195                         'sql.php' . PMA_generate_common_url($this_url_params),
 196                         $strDelete, $newsearchsqls['delete']) .  "</td>\n";
 197
 198             } else {
 199                 echo '<td>&nbsp;</td>' . "\n"
 200                     .'<td>&nbsp;</td>' . "\n";
 201             }// end if else
 202             $odd_row = ! $odd_row;
 203             echo '</tr>' . "\n";
 204         } // end for
 205
 206         echo '</table>' . "\n";
 207
 208         if ( count($_REQUEST['table_select']) > 1 ) {
 209             echo '<p>' . sprintf($strNumSearchResultsTotal,
 210                 $num_search_result_total) . '</p>' . "\n";
 211         }
 212     } // end if (!empty($search_str) && !empty($search_option))
 213
 214 } // end 1.
 215
 216
 217 /**
 218  * 2. Displays the main search form
 219  */
 220 echo "\n";
 221 $searched          = (isset($original_search_str))
 222                    ? htmlspecialchars($original_search_str)
 223                    : '';
 224 if (empty($search_option)) {
 225     $search_option = 1;
 226 }
 227 ?>
 228 <a name="db_search"></a>
 229 <form method="post" action="db_search.php" name="db_search">
 230 <?php echo PMA_generate_common_hidden_inputs($GLOBALS['db']); ?>
 231 <fieldset>
 232     <legend><?php echo $strSearchFormTitle; ?></legend>
 233
 234     <table class="formlayout">
 235     <tr><td><?php echo $strSearchNeedle; ?></td>
 236         <td><input type="text" name="search_str" size="60"
 237                 value="<?php echo $searched; ?>" /></td>
 238     </tr>
 239     <tr><td align="right" valign="top">
 240             <?php echo $strSearchType; ?></td>
 241         <td><input type="radio" id="search_option_1" name="search_option"
 242                 value="1"<?php if ($search_option == 1) echo ' checked="checked"'; ?> />
 243             <label for="search_option_1">
 244                 <?php echo $strSearchOption1; ?></label><sup>1</sup><br />
 245             <input type="radio" id="search_option_2" name="search_option"
 246                 value="2"<?php if ($search_option == 2) echo ' checked="checked"'; ?> />
 247             <label for="search_option_2">
 248                 <?php echo $strSearchOption2; ?></label><sup>1</sup><br />
 249             <input type="radio" id="search_option_3" name="search_option"
 250                 value="3"<?php if ($search_option == 3) echo ' checked="checked"'; ?> />
 251             <label for="search_option_3">
 252                 <?php echo $strSearchOption3; ?></label><br />
 253             <input type="radio" id="search_option_4" name="search_option"
 254                 value="4"<?php if ($search_option == 4) echo ' checked="checked"'; ?> />
 255             <label for="search_option_4">
 256                 <?php echo $strSearchOption4; ?></label>
 257             <?php echo PMA_showMySQLDocu('Regexp', 'Regexp'); ?><br />
 258             <br />
 259             <sup>1</sup><?php echo $strSplitWordsWithSpace; ?></td>
 260     </tr>
 261     <tr><td align="right" valign="top">
 262             <?php echo $strSearchInTables; ?></td>
 263         <td rowspan="2">
 264 <?php
 265 echo '            <select name="table_select[]" size="6"  multiple="multiple">' . "\n";
 266 foreach ( $tables as $each_table ) {
 267     if ( isset($_REQUEST['unselectall'])) {
 268         $is_selected = '';
 269     } elseif ( ! isset($_REQUEST['table_select'])
 270           || in_array($each_table, $_REQUEST['table_select'])
 271           || isset($_REQUEST['selectall']) ) {
 272         $is_selected = ' selected="selected"';
 273     } else {
 274         $is_selected = '';
 275     }
 276
 277     echo '                <option value="' . htmlspecialchars($each_table) . '"'
 278         . $is_selected . '>'
 279         . htmlspecialchars($each_table) . '</option>' . "\n";
 280 } // end while
 281 echo '            </select>' . "\n";
 282 $strDoSelectAll = '<a href="db_search.php?' . $url_query . '&amp;selectall=1#db_search"'
 283                 . ' onclick="setSelectOptions(\'db_search\', \'table_select[]\', true); return false;">' . $strSelectAll . '</a>'
 284                 . '&nbsp;/&nbsp;'
 285                 . '<a href="db_search.php?' . $url_query . '&amp;unselectall=1#db_search"'
 286                 . ' onclick="setSelectOptions(\'db_search\', \'table_select[]\', false); return false;">' . $strUnselectAll . '</a>';
 287 ?>
 288         </td>
 289     </tr>
 290     <tr><td align="right" valign="bottom">
 291             <?php echo $strDoSelectAll; ?></td></tr>
 292     </tr>
 293     </table>
 294 </fieldset>
 295 <fieldset class="tblFooters">
 296     <input type="submit" name="submit_search" value="<?php echo $strGo; ?>"
 297         id="buttonGo" />
 298 </fieldset>
 299 </form>
 300
 301
 302 <?php
 303 /**
 304  * Displays the footer
 305  */
 306 echo "\n";
 307 require_once('./libraries/footer.inc.php');
 308 ?>