PHP Script Example - FULLTEXT Search of MySQL Database Table
In the following FULLTEXT search query processing example:
- $opts['tb'] is the table
- `link` is text for an A HREF tag in the displayed results
- `heading` is the page heading or title of the content
- `content` is the page content
Basically, we're querying `heading` and `content` columns for the search `phrase`
posted by your form. The form is not shown here. Results are returned in an A HREF
tag with the appropriate page id, plus a snippet of the content. Better examples
can probably be found elsewhere, and this is a basic example of a MySQL FULLTEXT search.
<?php
$phrase = array_key_exists('phrase', @$_POST) ? trim(stripslashes(strip_tags(@$_POST['phrase']))) : '';
if(empty($phrase)){
echo "\n".'<p>No search term entered.</p>';
}else{
// SQL injection prevention (double quote mark is allowed here but should probably be disallowed)
$phrase = strtr($phrase, ',/\*&()$%^@~`?;', ' ');
$phrase = trim($phrase);
$phrase = str_replace('#180', '', $phrase);
echo "\n".'<p>Searching for <i>'.htmlspecialchars($phrase).'</i>... </p>';
$key = 'text_index';
// Beware of the re-definition of $s in this query.
$phrase = html_entity_decode($phrase, ENT_QUOTES);
if(!($res = @mysql_query(
'SELECT `page_id` AS s, `link` AS c, `heading`, `content`, `hidden`
FROM `'.$opts['tb'].'`
WHERE `hidden` <> "1" AND
MATCH(`heading`, `content`)
AGAINST ("'.$phrase.'")
LIMIT 10'
))){
echo "\n".'<p>No match for: <i class="red">'.$phrase.'</i></p>';
}else{
if(@mysql_num_rows($res) == 0){
echo "\n".'<p>The search engine cannot find the page you are looking for.</p>';
echo "\n".'<p>This could be because it could not find any pages containing <i>'.$phrase.'</i>';
echo "\n".'<p>Alternatively, it might have found too many pages, and could not decide which one you wanted.</p>';
}else{
$i = 0;
while($row = mysql_fetch_array($res)){
$s = substr(stristr(strip_tags($row['content']), $phrase), 0, 120);
if($s == ''){
$s = substr(strip_tags($row["content"]), 0, 120);
}
$i++;
echo "\n".'<p>'.$i.'.) <a href="results.php?page_id='.$row['s'].'">'.htmlentities($row['c']).' - '.htmlentities($row['heading']).'</a> ... '.$s.'...</p>';
}
}
}
// Save searches to monitor user activity
$phrase_qry = sprintf("INSERT INTO vs_searches (page_id, phrase, ip) VALUES ('', '%s', '%s')", addslashes($phrase), addslashes($ua['ip']));
if(!($phrase_res = @mysql_query($phrase_qry))){
$opts['error_msg'] .= 'Insert failed for search phrase'."\n".$phrase_qry."\n";
}
}
?>
www.hockinson.com