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

In consonance with the concepts stated in the previous section, what I’ll do in the next few lines will consist essentially of showing the definitions for a pair of MySQL wrapping classes. These classes are tasked with performing real searches on the selected database (remember that in the previous tutorial, I used a sample database table called “pages”), in addition to sending back to the client the eventual results.

Having explained how the server module of the application works, below I listed its complete source code. Please have a look at it:

// 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 * FROM pages WHERE contents LIKE 

'%$searchterm%' ORDER BY id ASC");
  
echo '<h2>'.$result->countRows().' records matched your search 

criteria.</h2>';
    echo '<ul>';
while($row=$result->fetchRow()){
       
$row['centercol']=str_replace

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

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

As you’ll probably recall, the two classes shown above, together with the simple script that follows them in order, are all the PHP code required for getting this search engine working seamlessly. The only thing that needs stressing here is the use of a LIKE statement to fetch all the records from the sample “pages” database table which match the search term entered in the corresponding web form.

If you’re quite familiar with SQL, then you’ll realize that this method can be used only with a limited amount of data, and that it doesn’t support the implementation of Boolean searches, at least directly.

Therefore, in the following section I’ll show you how to modify the existing SQL code that returns the search results. The modified code will add full-text and Boolean search capabilities to the original application.

Do you want to know how this will be achieved? Go ahead and read the next few lines. I’ll be there, waiting for you.