Home arrow PHP arrow Page 2 - Using Relevance Rankings for Full Text and Boolean Searches with MySQL

Developing a basic MySQL-driven search engine - PHP

If you're a web developer who is searching for a step-by-step guide on how to quickly implement full text and Boolean searches with MySQL, then look no further. This group of articles might be what you need. Welcome to the second tutorial of the series that began with "Performing Full Text and Boolean Searches with MySQL."

TABLE OF CONTENTS:
  1. Using Relevance Rankings for Full Text and Boolean Searches with MySQL
  2. Developing a basic MySQL-driven search engine
  3. Determining the 50 percent threshold
  4. Building an additional example
By: Alejandro Gervasio
Rating: starstarstarstarstar / 7
June 13, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

To illustrate clearly how to retrieve different relevance rankings from MySQL when performing a full-text search against a specified database table, I'm going to use the same search engine that was built in the first article of the series. As you probably recall, it was composed of two simple source files.

The first file was responsible for displaying the pertinent web form on the browser for entering obviously diverse search strings. The second one was tasked with executing real full-text queries against a sample "USERS" database table.

Naturally, in this case I'm going to modify slightly the SELECT statement that returns the corresponding database results to handle the aforementioned relevance rankings. However, as you'll see for yourself in the next few lines, the rest of the search application will remain nearly the same.

Having explained how this practical example will be developed, I'm going to create the mentioned "USERS" database table by specifying the corresponding full-text indexes for it. This simple process is demonstrated by the SQL statement below:

CREATE TABLE users
(
  id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL,
  firstname VARCHAR(64),
  lastname VARCHAR(64),
  email VARCHAR(64)
  comments TEXT
  FULLTEXT(firstname,lastname,comments)
);

As you can see, the definition for the prior database table specifies that three fields of it, that is "firstname,""lastname" and "comments" respectively, will be created as full-text indexes via the FULLTEXT command that you learned in the previous tutorial of the series. So far, so good, right?

Now, the next step consists of populating the above table with some trivial records, like the ones shown below:

("users" database table)

Id firstname lastname         email                                      comments

1  Alejandro Gervasio alejandro@domain.com MySQL is great for building a search engine
2  John        Williams  john@domain.com          PHP is a server side scripting language
3  Susan      Norton   susan@domain.com        JavaScript is good to manipulate documents
4  Julie         Wilson   julie@domain.com           MySQL is the best open source database server

Having already inserted some primitive data into the previous database table, it's time to show the signatures of the two files that integrate this MySQL-driven search engine. These files look like this:

(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>Working with relevance results</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>Working with relevance results</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>

(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, MATCH(firstname,lastname,comments) AGAINST('$searchterm') AS
relevance FROM users");
  
if(!$result->countRows()){
    
echo 'No results were found.';
  
}
  
else{
    
echo '<h2>Users returned are the following:</h2>';
    
while($row=$result->fetchRow()){
      
echo '<p>Name: '.$row['firstname'].' Relevance: '.$row
['relevance'].'</p>';
    
}
  
}
}

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

Despite the rather lengthy signature that corresponds to the last PHP file, you should pay attention particularly to the way that the pertinent search query has been constructed:

$result=$db->query("SELECT firstname, MATCH
(firstname,lastname,comments) AGAINST('$searchterm') AS
relevance FROM users");

In this case, I used the already familiar MATCH and AGAINST commands (covered in the preceding article of the series) to return from the sample "USERS" table a relevance ranking, depending on the search terms entered in the respective search form. However, the functionality of this ranking will be better understood if I show you some results outputted by the previous PHP file, according to the search term entered in the mentioned web form.

That being said, here are the corresponding database results:

// displays the following entering 'Alejandro' search term
/*
Users returned are the following:

Name: Alejandro Relevance: 1.0167628961849

Name: John Relevance: 0

Name: Susan Relevance: 0

Name: Julie Relevance: 0
*/ 

// displays the following entering 'Susan' search term
/*
Name: Alejandro Relevance: 0

Name: John Relevance: 0

Name: Susan Relevance: 1.0277009445163

Name: Julie Relevance: 0
*/

// displays the following entering 'John' search term
/*
Users returned are the following:

Name: Alejandro Relevance: 0 

Name: John Relevance: 1.0277009445163

Name: Susan Relevance: 0

Name: Julie Relevance: 0
*/

// displays the following entering 'Julie' search term
/*
Users returned are the following:

Name: Alejandro Relevance: 0 

Name: John Relevance: 0

Name: Susan Relevance: 0

Name: Julie Relevance: 1.0167628961849
*/

As you can see, the above list of examples shows in a friendly fashion how to retrieve some relevance rankings in accordance with diverse search terms entered in the search form. Here, it's clear to see that this ranking value is a positive decimal value, and obviously varies in consonance with the inputted search string. Quite simple, right?

Okay, at this point I believe that the previous results should give you a better idea of how to return relevance values using full-text searches. So what is the next step that must be taken on this educational journey?

Well, since I assume that you're interested in learning a bit more about how MySQL handles relevance rankings, in the following section I'm going to show you a concrete example to illustrate how to work the so-called "50%" threshold.

Does this sound complex to you? Fear not, since it's much simpler than you think! Just keep reading to learn more on this topic.



 
 
>>> More PHP Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

PHP ARTICLES

- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: