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

Determining the 50 percent threshold - 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

As I stated in the section that you just read, it's important to know how MySQL handles different relevance rankings. This leads me straight into introducing the concept of a feature called the 50% threshold.

Basically, this means that if a search word is present in more than 50 percent (hence its name) of the table rows searched, then these rows simply will be discarded from the corresponding results.

So, if you consider together the rows removal process performed via the aforementioned 50% threshold, in addition to the elimination of noisy words, then you'll have a clear idea of how MySQL tries to discard from the very beginning search terms with low relevance, in this way accelerating noticeably the execution of search queries.

Now that you have learned a bit of the theory surrounding the 50% threshold, let me show you a concrete example that demonstrates how a certain search term that is present in more than 50% of the existing database rows is automatically discarded by MySQL from the corresponding results.

To illustrate how this database row removal process works, I'm going to use the same source files that were shown in the previous section, so this specific example can be more easily grasped.

That being said, here are the source files in question:

(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>Testing the MySQL 50% threshold</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>Testing the MySQL 50% threshold</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();
}
?>

So far, so good. Since the definition of the above source files should be very familiar to you, pay strong attention to the results outputted by the previous search query if the search term "mysql" is entered in the corresponding web form.

// PHP file displays the following output
/*
Users returned are the following:

Name: Alejandro Relevance: 0

Name: John Relevance: 0

Name: Susan Relevance: 0

Name: Julie Relevance: 0
*/

As you can see, MySQL has quickly removed the previous search term from the respective database results, since it was present in two table rows. Now, are you starting to grasp the logic behind the 50% threshold? I bet you are!

All right, at this point I think you understand how MySQL removes diverse search terms based on the 50% threshold algorithm. Thus, it's time to move on and read the last section of this tutorial, where I'm going to set up an additional example to further clarify the concept that surrounds the implementation of the aforementioned 50% threshold.

To see how this final example will be built, click on the link below and keep reading.



 
 
>>> 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: