2 /* vim: set expandtab sw=4 ts=4 sts=4: */
4 * Handles Database Search
8 if (! defined('PHPMYADMIN')) {
13 * Class to handle database search
32 private $_tables_names_only;
39 private $_searchTypes;
41 * Already set search type
46 private $_criteriaSearchType;
48 * Already set search type's description
53 private $_searchTypeDescription;
55 * Search string/regexp
60 private $_criteriaSearchString;
62 * Criteria Tables to search in
67 private $_criteriaTables;
69 * Restrict the search to this column
74 private $_criteriaColumnName;
79 * @param string $db Database name
81 public function __construct($db)
84 // Sets criteria parameters
85 $this->_setSearchParams();
89 * Sets search parameters
93 private function _setSearchParams()
95 $this->_tables_names_only
= $GLOBALS['dbi']->getTables($this->_db
);
97 $this->_searchTypes
= array(
98 '1' => __('at least one of the words'),
99 '2' => __('all words'),
100 '3' => __('the exact phrase'),
101 '4' => __('as regular expression'),
104 if (empty($_REQUEST['criteriaSearchType'])
105 ||
! is_string($_REQUEST['criteriaSearchType'])
106 ||
! array_key_exists(
107 $_REQUEST['criteriaSearchType'],
111 $this->_criteriaSearchType
= 1;
112 unset($_REQUEST['submit_search']);
114 $this->_criteriaSearchType
= (int) $_REQUEST['criteriaSearchType'];
115 $this->_searchTypeDescription
116 = $this->_searchTypes
[$_REQUEST['criteriaSearchType']];
119 if (empty($_REQUEST['criteriaSearchString'])
120 ||
! is_string($_REQUEST['criteriaSearchString'])
122 $this->_criteriaSearchString
= '';
123 unset($_REQUEST['submit_search']);
125 $this->_criteriaSearchString
= $_REQUEST['criteriaSearchString'];
128 $this->_criteriaTables
= array();
129 if (empty($_REQUEST['criteriaTables'])
130 ||
! is_array($_REQUEST['criteriaTables'])
132 unset($_REQUEST['submit_search']);
134 $this->_criteriaTables
= array_intersect(
135 $_REQUEST['criteriaTables'], $this->_tables_names_only
139 if (empty($_REQUEST['criteriaColumnName'])
140 ||
! is_string($_REQUEST['criteriaColumnName'])
142 unset($this->_criteriaColumnName
);
144 $this->_criteriaColumnName
= PMA_Util
::sqlAddSlashes(
145 $_REQUEST['criteriaColumnName'], true
151 * Builds the SQL search query
153 * @param string $table The table name
155 * @return array 3 SQL queries (for count, display and delete results)
157 * @todo can we make use of fulltextsearch IN BOOLEAN MODE for this?
159 * DatabaseInterface::freeResult
160 * DatabaseInterface::fetchAssoc
166 private function _getSearchSqls($table)
169 $sqlstr_select = 'SELECT';
170 $sqlstr_delete = 'DELETE';
172 $sqlstr_from = ' FROM '
173 . PMA_Util
::backquote($GLOBALS['db']) . '.'
174 . PMA_Util
::backquote($table);
175 // Gets where clause for the query
176 $where_clause = $this->_getWhereClause($table);
177 // Builds complete queries
179 $sql['select_columns'] = $sqlstr_select . ' * ' . $sqlstr_from
181 // here, I think we need to still use the COUNT clause, even for
182 // VIEWs, anyway we have a WHERE clause that should limit results
183 $sql['select_count'] = $sqlstr_select . ' COUNT(*) AS `count`'
184 . $sqlstr_from . $where_clause;
185 $sql['delete'] = $sqlstr_delete . $sqlstr_from . $where_clause;
191 * Provides where clause for building SQL query
193 * @param string $table The table name
195 * @return string The generated where clause
197 private function _getWhereClause($table)
200 $allColumns = $GLOBALS['dbi']->getColumns($GLOBALS['db'], $table);
201 $likeClauses = array();
202 // Based on search type, decide like/regex & '%'/''
203 $like_or_regex = (($this->_criteriaSearchType
== 4) ?
'REGEXP' : 'LIKE');
204 $automatic_wildcard = (($this->_criteriaSearchType
< 3) ?
'%' : '');
205 // For "as regular expression" (search option 4), LIKE won't be used
206 // Usage example: If user is searching for a literal $ in a regexp search,
207 // he should enter \$ as the value.
208 $this->_criteriaSearchString
= PMA_Util
::sqlAddSlashes(
209 $this->_criteriaSearchString
,
210 ($this->_criteriaSearchType
== 4 ?
false : true)
212 // Extract search words or pattern
213 $search_words = (($this->_criteriaSearchType
> 2)
214 ?
array($this->_criteriaSearchString
)
215 : explode(' ', $this->_criteriaSearchString
));
217 foreach ($search_words as $search_word) {
218 // Eliminates empty values
219 if (/*overload*/mb_strlen($search_word) === 0) {
222 $likeClausesPerColumn = array();
223 // for each column in the table
224 foreach ($allColumns as $column) {
225 if (! isset($this->_criteriaColumnName
)
226 ||
/*overload*/mb_strlen($this->_criteriaColumnName
) == 0
227 ||
$column['Field'] == $this->_criteriaColumnName
229 // Drizzle has no CONVERT and all text columns are UTF-8
230 $column = ((PMA_DRIZZLE
)
231 ? PMA_Util
::backquote($column['Field'])
232 : 'CONVERT(' . PMA_Util
::backquote($column['Field'])
234 $likeClausesPerColumn[] = $column . ' ' . $like_or_regex . ' '
236 . $automatic_wildcard . $search_word . $automatic_wildcard
240 if (count($likeClausesPerColumn) > 0) {
241 $likeClauses[] = implode(' OR ', $likeClausesPerColumn);
244 // Use 'OR' if 'at least one word' is to be searched, else use 'AND'
245 $implode_str = ($this->_criteriaSearchType
== 1 ?
' OR ' : ' AND ');
246 if ( empty($likeClauses)) {
247 // this could happen when the "inside column" does not exist
248 // in any selected tables
249 $where_clause = ' WHERE FALSE';
251 $where_clause = ' WHERE ('
252 . implode(') ' . $implode_str . ' (', $likeClauses)
255 return $where_clause;
259 * Displays database search results
261 * @return string HTML for search results
263 public function getSearchResults()
266 // Displays search string
267 $html_output .= '<br />'
268 . '<table class="data">'
269 . '<caption class="tblHeaders">'
271 __('Search results for "<i>%s</i>" %s:'),
272 htmlspecialchars($this->_criteriaSearchString
),
273 $this->_searchTypeDescription
277 $num_search_result_total = 0;
279 // For each table selected as search criteria
280 foreach ($this->_criteriaTables
as $each_table) {
281 // Gets the SQL statements
282 $newsearchsqls = $this->_getSearchSqls($each_table);
283 // Executes the "COUNT" statement
284 $res_cnt = $GLOBALS['dbi']->fetchValue($newsearchsqls['select_count']);
285 $num_search_result_total +
= $res_cnt;
286 // Gets the result row's HTML for a table
287 $html_output .= $this->_getResultsRow(
288 $each_table, $newsearchsqls, $odd_row, $res_cnt
290 $odd_row = ! $odd_row;
292 $html_output .= '</table>';
293 // Displays total number of matches
294 if (count($this->_criteriaTables
) > 1) {
295 $html_output .= '<p>';
296 $html_output .= sprintf(
298 '<b>Total:</b> <i>%s</i> match',
299 '<b>Total:</b> <i>%s</i> matches',
300 $num_search_result_total
302 $num_search_result_total
304 $html_output .= '</p>';
310 * Provides search results row with browse/delete links.
313 * @param string $each_table One of the tables on which search was performed
314 * @param array $newsearchsqls Contains SQL queries
315 * @param bool $odd_row For displaying contrasting table rows
316 * @param integer $res_cnt Number of results found
318 * @return string HTML row
320 private function _getResultsRow($each_table, $newsearchsqls, $odd_row, $res_cnt)
322 $this_url_params = array(
323 'db' => $GLOBALS['db'],
324 'table' => $each_table,
325 'goto' => 'db_sql.php',
327 'is_js_confirmed' => 0,
329 // Start forming search results row
330 $html_output = '<tr class="noclick ' . ($odd_row ?
'odd' : 'even') . '">';
331 // Displays results count for a table
332 $html_output .= '<td>';
333 $html_output .= sprintf(
335 '%1$s match in <strong>%2$s</strong>',
336 '%1$s matches in <strong>%2$s</strong>', $res_cnt
338 $res_cnt, htmlspecialchars($each_table)
340 $html_output .= '</td>';
341 // Displays browse/delete link if result count > 0
343 $this_url_params['sql_query'] = $newsearchsqls['select_columns'];
344 $browse_result_path = 'sql.php' . PMA_URL_getCommon($this_url_params);
345 $html_output .= '<td><a name="browse_search" class="ajax" href="'
346 . $browse_result_path . '" onclick="loadResult(\''
347 . $browse_result_path . '\',\'' . $each_table . '\',\''
350 'db' => $GLOBALS['db'], 'table' => $each_table
353 . ');return false;" >'
354 . __('Browse') . '</a></td>';
355 $this_url_params['sql_query'] = $newsearchsqls['delete'];
356 $delete_result_path = 'sql.php' . PMA_URL_getCommon($this_url_params);
357 $html_output .= '<td><a name="delete_search" class="ajax" href="'
358 . $delete_result_path . '" onclick="deleteResult(\''
359 . $delete_result_path . '\' , \''
361 __('Delete the matches for the %s table?'),
362 htmlspecialchars($each_table)
364 . '\');return false;">'
365 . __('Delete') . '</a></td>';
367 $html_output .= '<td> </td>'
370 $html_output .= '</tr>';
375 * Provides the main search form's html
377 * @return string HTML for selection form
379 public function getSelectionForm()
381 $html_output = '<a id="db_search"></a>';
382 $html_output .= '<form id="db_search_form"'
384 . ' method="post" action="db_search.php" name="db_search">';
385 $html_output .= PMA_URL_getHiddenInputs($GLOBALS['db']);
386 $html_output .= '<fieldset>';
387 // set legend caption
388 $html_output .= '<legend>' . __('Search in database') . '</legend>';
389 $html_output .= '<table class="formlayout">';
390 // inputbox for search phrase
391 $html_output .= '<tr>';
392 $html_output .= '<td>' . __('Words or values to search for (wildcard: "%"):')
394 $html_output .= '<td><input type="text"'
395 . ' name="criteriaSearchString" size="60"'
396 . ' value="' . htmlspecialchars($this->_criteriaSearchString
) . '" />';
397 $html_output .= '</td>';
398 $html_output .= '</tr>';
399 // choices for types of search
400 $html_output .= '<tr>';
401 $html_output .= '<td class="right vtop">' . __('Find:') . '</td>';
402 $html_output .= '<td>';
404 '1' => __('at least one of the words')
405 . PMA_Util
::showHint(
406 __('Words are separated by a space character (" ").')
408 '2' => __('all words')
409 . PMA_Util
::showHint(
410 __('Words are separated by a space character (" ").')
412 '3' => __('the exact phrase'),
413 '4' => __('as regular expression') . ' '
414 . PMA_Util
::showMySQLDocu('Regexp')
416 // 4th parameter set to true to add line breaks
417 // 5th parameter set to false to avoid htmlspecialchars() escaping
418 // in the label since we have some HTML in some labels
419 $html_output .= PMA_Util
::getRadioFields(
420 'criteriaSearchType', $choices, $this->_criteriaSearchType
, true, false
422 $html_output .= '</td></tr>';
423 // displays table names as select options
424 $html_output .= '<tr>';
425 $html_output .= '<td class="right vtop">' . __('Inside tables:') . '</td>';
426 $html_output .= '<td rowspan="2">';
427 $html_output .= '<select name="criteriaTables[]" size="6"'
428 . ' multiple="multiple">';
429 foreach ($this->_tables_names_only
as $each_table) {
430 if (in_array($each_table, $this->_criteriaTables
)) {
431 $is_selected = ' selected="selected"';
435 $html_output .= '<option value="' . htmlspecialchars($each_table) . '"'
437 . str_replace(' ', ' ', htmlspecialchars($each_table))
440 $html_output .= '</select>';
441 $html_output .= '</td></tr>';
442 // Displays 'select all' and 'unselect all' links
443 $alter_select = '<a href="#" '
444 . 'onclick="setSelectOptions(\'db_search\','
445 . ' \'criteriaTables[]\', true); return false;">'
446 . __('Select All') . '</a> / ';
447 $alter_select .= '<a href="#" '
448 . 'onclick="setSelectOptions(\'db_search\','
449 . ' \'criteriaTables[]\', false); return false;">'
450 . __('Unselect All') . '</a>';
451 $html_output .= '<tr><td class="right vbottom">'
452 . $alter_select . '</td></tr>';
453 // Inputbox for column name entry
454 $html_output .= '<tr>';
455 $html_output .= '<td class="right">' . __('Inside column:') . '</td>';
456 $html_output .= '<td><input type="text" name="criteriaColumnName" size="60"'
458 . (! empty($this->_criteriaColumnName
)
459 ?
htmlspecialchars($this->_criteriaColumnName
)
462 $html_output .= '</tr>';
463 $html_output .= '</table>';
464 $html_output .= '</fieldset>';
465 $html_output .= '<fieldset class="tblFooters">';
466 $html_output .= '<input type="submit" name="submit_search" value="'
467 . __('Go') . '" id="buttonGo" />';
468 $html_output .= '</fieldset>';
469 $html_output .= '</form>';
470 $html_output .= '<div id="togglesearchformdiv">'
471 . '<a id="togglesearchformlink"></a></div>';
477 * Provides div tags for browsing search results and sql query form.
479 * @return string div tags
481 public function getResultDivs()
483 $html_output = '<!-- These two table-image and table-link elements display'
484 . ' the table name in browse search results -->';
485 $html_output .= '<div id="table-info">';
486 $html_output .= '<a class="item" id="table-link" ></a>';
487 $html_output .= '</div>';
488 // div for browsing results
489 $html_output .= '<div id="browse-results">';
490 $html_output .= '<!-- this browse-results div is used to load the browse'
491 . ' and delete results in the db search -->';
492 $html_output .= '</div>';
493 $html_output .= '<br class="clearfloat" />';
494 $html_output .= '<div id="sqlqueryform">';
495 $html_output .= '<!-- this sqlqueryform div is used to load the delete'
496 . ' form in the db search -->';
497 $html_output .= '</div>';
498 $html_output .= '<!-- toggle query box link-->';
499 $html_output .= '<a id="togglequerybox"></a>';