Implementing Full Text and Boolean Searches for a Search Engine Built with AJAX - Implementing full-text and Boolean searches
(Page 4 of 4 )
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!
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
/ 7 
