Implementing Full Text and Boolean Searches for a Search Engine Built with AJAX

As you’ll soon see, improving the existing search engine to allow users to perform full-text and Boolean searches is only a matter of coding the appropriate SQL statements inside the SELECT command that you saw before, in addition to working with a database that uses a MyISAM engine.

These requirements obey a simple rule: if you’re using a MySQL database which has been defined to work with an InnoDB engine, full-text searches won’t be supported. As a consequence, this feature won’t be present in the search engine in question. Therefore, you must first make sure you’re using the correct database type before implementing these handy capabilities.

All right, having spotted quickly the differences between the mentioned database engines, take a look at the following PHP script. It provides the original search application with full-text and Boolean capacities:

// define 'MySQL' class
class MySQL{
    private $conId;
    private $host;
    private $user;
    private $password;
    private $database;
    private $result;
    const OPTIONS=4;
    public function __construct($options=array()){
       
if(count($options)!=self::OPTIONS){
            throw new Exception('Invalid number of connection 

parameters');
        }
        foreach($options as $parameter=>$value){
if(!$value){
                 throw new Exception('Invalid parameter 

'.$parameter);
         }
           
$this->{$parameter}=$value;
}
$this->connectDB();
    }
    // connect to MySQL
    private function connectDB(){
     
if(!$this->conId=mysql_connect($this->host,

$this->user,$this->password)){
            throw new Exception('Error connecting to the server');
        }
       
if(!mysql_select_db($this->database,$this->conId)){
throw new Exception('Error selecting database');
        }
    }
    // run query
    public function query($query){
       
if(!$this->result=mysql_query($query,$this->conId)){
throw new Exception('Error performing query '.$query);
        }
        return new Result($this,$this->result);
    }
}
// define 'Result' class
class Result {
    private $mysql;
    private $result;
    public function __construct(&$mysql,$result){
       
$this->mysql=&$mysql;
       
$this->result=$result;
    }
    // fetch row
    public function fetchRow(){
    return mysql_fetch_assoc($this->result);
    }
    // count rows
    public function countRows(){
       
if(!$rows=mysql_num_rows($this->result)){
return '0';
        }
        return $rows;
    }
    // count affected rows
    public function countAffectedRows(){
       
if(!$rows=mysql_affected_rows($this->mysql->conId)){
            throw new Exception('Error counting affected rows');
        }
        return $rows;
    }
    // get ID from last-inserted row
    public function getInsertID(){
       
if(!$id=mysql_insert_id($this->mysql->conId)){
        throw new Exception('Error getting ID');
        }
        return $id;
    }
    // seek row
    public function seekRow($row=0){
        if(!is_int($row)||$row<0){
      throw new Exception('Invalid result set offset');
        }
       
if(!mysql_data_seek($this->result,$row)){
              throw new Exception('Error seeking data');
        }
    }
}
try{
     // connect to MySQL
    $db=new MySQL(array('host'=>'host','user'=>'user',

'password'=>'password','database'=>'database'));
   
$searchterm=mysql_escape_string($_GET['searchterm'])
  
$result=$db->query("SELECT *, MATCH(title,content) AGAINST

('$searchterm' IN BOOLEAN MODE) as relevance 
FROM pages WHERE MATCH(title,content) AGAINST('$searchterm' IN BOOLEAN 

MODE)");
echo '<h2>'.$result->countRows().' records matched your search 

criteria.</h2>';
echo '<ul>';
$searchterms=explode('',$searchterm);
 
while($row=$result->fetchRow()){
       
foreach($searchterms as $searchterm){
            $row['contentl']=str_replace($searchterm,

'<strong>'.$searchterm.'</strong>',$row['contentl']);
}
echo '<li><a  
  
href="http://www.mywebsite.com/index.php?id='.$row['id'].'">

'.$row['title'].'</a><p>'.$row['content'].'</p></li>';
}
echo '</ul>';
}
catch(Exception $e){
    echo $e->getMessage();
    exit();
} 

With reference to the above code snippet, the line that actually performs full-text and Boolean searches is the following:

$result=$db->query("SELECT *, MATCH(title,content) AGAINST
('$searchterm' IN
BOOLEAN MODE) as relevance FROM pages WHERE MATCH(title,content)
AGAINST
('$searchterm' IN BOOLEAN MODE)");

Of course, I’m not going to explain in detail how each of these statements works, since the topic is out of the scope of this article. However, if you’re quite familiar with SQL, then you’ll find these commands easy to understand.

Finally, please make sure that you’re working with a database that uses the MyISAM engine, and create your database table so that it can work with full-text indexes by running something similar to the following SQL statements:

CREATE TABLE Pages 

( 

id INT(4) AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(20), 

content LONGTEXT,   

FULLTEXT(content) 

);

In this particular example, I defined a full-text index for the “content” field that belongs to the sample “Pages” database table, but multiple indexes are also supported. Now, assuming that all the changes that I introduced were correctly implemented, you have at your disposal an AJAX-based search engine that supports full-text and Boolean searches. Wasn’t that great?

Final thoughts

We’ve come to the end of this series. Hopefully, after reading these three articles, you’ll have a clearer idea of how to create an expandable search engine with AJAX, which can be included in any existing website with minor problems. As usual, see you in the next web development tutorial!