Building the Web Form - PHP

When a database-driven web site grows past a certain size, it requires an internal search engine. If it is a very big site, it may be desirable for visitors to be able to use full-text searches and Boolean operators to find the information they need. This article, the first of a three-part series, explains why and shows you how to work with full-text and Boolean searches using MySQL and PHP 5.

TABLE OF CONTENTS:

By: Alejandro Gervasio

Poor Best

Rating:

/ 6

June 06, 2007

print this article

SEARCH DEV SHED

TOOLS YOU CAN USE

Now, having filled in the sample "USERS" table with the previous records, it's time to build the corresponding web form for entering search terms:

(definition of form.htm file)

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-
8859-1" />
<title>Example of simple MySQL search engine</title>
<style type="text/css">
body{
   padding: 0;
   margin: 0;
   background: #fff;
}

h1{
   font: bold 16px Arial, Helvetica, sans-serif;
   color: #000;
   text-align: center;
}

p{
font: bold 11px Tahoma, Arial, Helvetica, sans-serif;
color: #000;
}

#formcontainer{
   width: 40%;
   padding: 10px;
   margin-left: auto;
   margin-right: auto;
   background: #6cf;
}
</style>
</head>
<body>
<h1>Example of simple MySQL search engine</h1>
<div id="formcontainer">
    <form action="search.php" method="get">
      <p>Enter search term here : <input type="text"
name="searchterm" title="Enter search term here" /><input
type="submit" name="search" value="Search Now!" /></p>
    </form>
</div>
</body>
</html>

And finally, here is the signature of the PHP file that performs full-text searches against the "USERS" database table that you saw before:

(definition of search.php file)

<?php
// 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);
     }
     public function escapeString($value){
       return mysql_escape_string($value);
     }
}

// 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 false;
     }
     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 form 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=$db->escapeString($_GET['searchterm']);
   $result=$db->query("SELECT firstname, lastname from users WHERE MATCH(firstname,lastname,comments) AGAINST('$searchterm')");
   if(!$result->countRows()){
     echo 'No results were found.';
   }
   else{
     echo '<h2>Articles returned are as following:</h2>';
   while($row=$result->fetchRow()){
       echo '<p>Title: '.$row['title'].' Author: '.$row['author'].' Description: '.$row['content'].'</p>';
     }
   }
}

catch(Exception $e){
echo $e->getMessage();
exit();
}
?>

As shown above, the previous PHP file includes two new SQL statements in the SELECT query, called "MATCH" and "AGAINST" to perform full-text searches against the previously defined "USERS" table. In this case, the "MATCH" command is utilized to return a relevance value, which is determined by combining the search string in question, the words contained in the indexed table fields and finally the number of rows included in the search.

All right, now that you know how to code a SELECT statement that performs a full-text search against the prior sample "USERS" database table, let me show you what happens if the search string "JavaScript" is entered in the respective web form.

The result returned by the query is shown below:

Users returned are the following:
First Name: Susan Last Name: Norton

Apparently, the database table row retrieved after performing a full-text search is very similar to using a conventional "LIKE" statement. However, this is only a first impression, since actually the query has been performed faster, due to the specification of the respective full-text indexes.

Besides, it's important to note here that the use of a "MATCH" command returns a relevance ranking, but this crucial feature will be covered in great detail in the next part of the series.

In the meantime, feel free to test all the hands-on examples shown in this article, so you can acquire a better grounding in how to implement full-text searches with MySQL. It's going to instructive, believe me!

Final thoughts

In this first tutorial of the series, I walked you through the basics of using full-text searches with MySQL. As I said previously, the subject has many other features that need to be covered in detail, such as working with relevance rankings and Boolean operators, but all these topics will be discussed in the next tutorial.

Now that you've been warned, you won't want to miss it!