PHP Script Example - FULLTEXT Search of MySQL Database Table

In the following FULLTEXT search query processing example:

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($phraseENT_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), 0120);

            if(
$s == ''){

               
$s substr(strip_tags($row["content"]), 0120);

            }

            
$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";

   }

}

?>
CREATE TABLE IF NOT EXISTS `test` (
  `id` smallint(5) unsigned NOT NULL DEFAULT '0',
  `heading` varchar(255) NOT NULL,
  `content` text NOT NULL,
  UNIQUE KEY `idx_id` (`id`),
  FULLTEXT KEY `idx_search` (`heading`,`content`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

PHP Form Generator

www.hockinson.com