Paginating Result Sets for a Search Engine Built with MySQL and PHP 5

If you’re a PHP developer looking for an approachable guide on how to build an expandable search engine with MySQL and PHP 5, then this series of articles might be quite useful to you. Welcome to the second installment of the series that began with "Building a Search Engine with MySQL and PHP 5." These tutorials will show you how to create a fully functional search application by using the capabilities provided by the MySQL/PHP 5 team.

Introduction

Now, after introducing the subject of this series, let me briefly recapitulate the topics that were covered in the preceding article, so you can grasp more easily the new features that I plan to add to the core structure of this MySQL-driven search application.

As you’ll possibly recall, in the first article of the series I went through the development of the different modules that comprise this customizable search engine. I created a simple front-end for entering distinct search terms, and gave the definition of some basic PHP classes for performing searches against one or more selected databases.

Naturally, as you might have supposed by this time, my purpose here is to provide you with the required source files that allow you to implement an internal search engine that can be incorporated into any existing (or future) web site. However, due to the expandable structure exposed by this search application, you’ll be able to add to it many other features that might be required to fit your personal needs.

And speaking of additional features, in this second tutorial of the series I’m going to show you how to add some crucial characteristics to the previously-developed search engine. These include the implementation of paginated results and the ability to perform Boolean searches.

By the end of this article, you’ll be equipped with the necessary supporting material to implement a highly improved search engine within your own PHP 5-based applications. So, are you ready to continue this educational journey? Let’s get started!

{mospagebreak title=Listing the full source code for the original search engine}

As usual with many of my articles on PHP development, before I start adding some additional features to this MySQL-based search engine, first I’d like to list its complete source code as it was initially created in the first tutorial of the series. Doing so will give you a better idea of how the improvements that I plan to incorporate into the application can be linked to its original structure.

This being said, here are the source files that comprise this MySQL-driven search application:

(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>MySQL-based Search Engine</title>
<link href="default.css" rel="stylesheet" type="text/css" media="screen" />
<script language="javascript" type="text/javascript">
window.onload=function(){
   if(document.getElementById && document.getElementsByTagName &&
document.createElement){
     var sfield=document.getElementsByTagName(‘form’)[0].elements
[0];
     if(!sfield){return};
     sfield.onfocus=function(){this.value=”};
     sfield.onblur=function(){
       if(!this.value){this.value=’Enter your search term here’};
     }
   }
}
</script>
</head>
<body>
  <h1>MySQL-based Search Engine</h1>
  <div class="maincontainer">
    <form method="get" action="processform.php">
      <input type="text" name="searchterm" title="Enter your
search term here" value="Enter your search term here"
class="searchbox" />
      <input type="submit" name="search" title="Search Now!
"value="Search" class="searchbutton" />
    </form>
  </div>
</body>
</html>

(definition of "default.css" file)

body{
            background: #ccc;
            margin: 0;
            padding: 0;
}

h1{
            width: 375px;
            padding: 10px;
            margin-left: auto;
            margin-right: auto;
            background: #339;
            font: normal 18px Arial, Helvetica, sans-serif;
            color: #fff;
            border: 1px solid #000;
            text-align: center;
}

h2{
            font: bold 18px  Arial, Helvetica, sans-serif;
            color: #339;
}

p{
            font: normal 10pt Arial, Helvetica, sans-serif;
            color: #000;
}

a:link,a:visited{
            font: normal 10pt Arial, Helvetica, sans-serif;
            color: #00f;
            text-decoration: none;
}

a:hover{
            color: #f00;
            text-decoration: underline;
}

.maincontainer{
            width: 375px;    
            padding: 10px;
            margin-left: auto;
            margin-right: auto;
            background: #f0f0f0;
            border: 1px solid #000;
}

.rowcontainer{
            padding: 10px;
            margin-bottom: 10px;
            background: #ccf;
}

.searchbox{
            width: 200px;
            font: normal 12px Arial, Helvetica, sans-serif;
            color: #000;
}

.searchbutton{
            width: 80px;
            font: bold 12px Arial, Helvetica, sans-serif;
            color: #000;      
}

(definition of "mysql.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’);
     }
   }
}
?>

(definition for "processform.php" file)

<?php
// include MySQL-processing classes
require_once ‘mysql.php';
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,comments FROM
users WHERE MATCH(firstname,lastname,comments) AGAINST
(‘$searchterm’)");
   if(!$result->countRows()){
     echo ‘<div class="maincontainer"><h2>No results were found.
Go back and try a new search.</h2></div>’."n";
   }
   else{
     // display search results
     echo ‘<div class="maincontainer"><h2>Your search criteria
returned ‘.$result->countRows().’ results.</h2>’."n";
     while($row=$result->fetchRow()){
       echo ‘<div class="rowcontainer"><p><strong>First Name:
</strong>’.$row['firstname'].'<p><p><strong>Last Name:
</strong>’.$row['lastname'].'</p><p><strong>Comments:
</strong>’.$row['comments'].'</p></div>’."n"; 
     }
   }
   echo ‘</div>';
}
catch(Exception $e){
   echo $e->getMessage();
   exit();
}
?>

After analyzing the signature of the previous source files, it’s quite probable that you’ll recall more clearly how this search application was initially developed. As you can see, the application is composed of four core files. The first one is responsible for displaying the web form for entering different search terms, and the second one simply adds some basic styles to the respective front-end.

The last two files are the workhorses of the search engine, since they’re tasked with performing the pertinent SELECT queries against one or more specified databases and returning the corresponding results.

So far, so good right? At this point the core structure of this MySQL-based search application should be familiar to you. Thus, considering this condition, it’s time to learn how to incorporate into the search engine the ability to paginate all of the results returned after performing a concrete search.

Want to see how this will be achieved? Click on the link that appears below and keep reading.

{mospagebreak title=Adding pagination capabilities to the initial search engine}

In consonance with the concepts deployed in the previous section, it’d be highly desirable to provide the search application with the ability to paginate all the results returned by a specific search query. Certainly this is a feature present on many modern web sites that include an internal search engine.

Therefore, taking into account this important requirement, I’m going to modify slightly the signature of the "MySQL" and "Result" PHP classes that you saw in the prior section, so they can paginate the results returned by a search query.

Now, the respective definitions for the aforementioned classes are as follows:

(definition of "mysql.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,$query);
   }
   public function escapeString($value){
     return mysql_escape_string($value);
   }
}
// define ‘Result’ class
class Result {
   private $mysql;
   private $result;
   private $query;
   private $rowTemplate=’default.tpl';
   private $numRecs=4;
   public function __construct($mysql,$result,$query){
     $this->mysql=$mysql;
     $this->result=$result;
     $this->query=$query;
   }
   // 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’);
     }
   }
   public function countFields(){
     if(!$fields=mysql_num_fields($this->result)){
       throw new Exception(‘Error counting fields.’);
     }
     return $fields;
   }
   public function fetchPagedRows($page){
     $numPages=ceil($this->countRows()/$this->numRecs);
     if(empty($page)||$page>$numPages){
       $page=1;
     }
     $result=$this->mysql->query($this->query.’ LIMIT ‘.($page-1)
*$this->numRecs.’,’.$this->numRecs);
     $output=”;
     while($row=$result->fetchRow()){
       $rowTemplate=file_get_contents($this->rowTemplate);
       foreach($row as $key=>$value){
         $rowTemplate=str_replace
(‘{‘.$key.’}’,$value,$rowTemplate); 
       }
       $output.=$rowTemplate;
     }
     $output.='<p>';
     if($page>1){
       $output.='<a href="’.$_SERVER['PHP_SELF'].’?&page=’.
($page-1).’">&lt;&lt;</a>&nbsp;';
     }
     for($i=1;$i<=$numPages;$i++){
       $output.=$i!=$page?'<a href="’.$_SERVER['PHP_SELF'].’?
page=’.$i.’">’.$i.'</a>&nbsp;':$i.’&nbsp;';
     }
     if($page<$numPages){
       $output.=’&nbsp;<a href="’.$_SERVER['PHP_SELF'].’?&page=’.
($page+1).’">&gt;&gt;</a>';
     }
     $output.='</p>';
     return $output;
   }
}
?>

As you can see, I introduced a few simple modifications to the above "MySQL" and "Result" PHP classes, with the purpose of providing them with the capacity for paginating database results. Of course, the most important change that I made with reference to these classes was the definition of two new methods, called "countFields()" and "fetchPagedRows()" respectively, which obviously belong to the respective "Result" class.

Also, it should be noticed that the logic implemented by the "fetchPagedRows()" method has much in common with many other database record paginating systems built in PHP, so I believe that you shouldn’t have major problems understanding how this method works.

All right, at this stage I introduced some minor changes to the respective "MySQL" and "Result" PHP classes that were listed in the previous section to provide them with the capacity for paginating the corresponding results returned by a specific search query.

However, there’s a small issue here surrounding the implementation of this improved search application. As you may have noticed, if a hypothetical user performs a search using this engine, the application will display the paginated results along with the corresponding page links; but the problem that comes up here is that whenever the user clicks on any of these links, the search engine will not be able to retrieve again the respective database results, since the entered search term, stored on the $_GET['searchterm'] super global array will no longer exist. Pretty ugly, right?

Nonetheless, there are many way to address this issue. In this case I’m going to use a simple session mechanism to maintain the value of the respective search term across the different pages generated by the page links.

This session mechanism will be implemented by the means of a separate session handling class, whose signature will be shown in the following section of this tutorial. Clink on the link below and keep reading.

{mospagebreak title=Maintaining the value of a given search term across different web pages}

As I expressed in the section that you just read, the last step that I’m going to take for the moment will consist of building a basic session handling class. This class will come in useful for maintaining the value of a given search string across the different web pages generated by the page links that you learned before.

With the session handling class, each time an user performs a search against one or more selected databases, the search terms will be kept when clicking on the page links. Quite simple, isn’t it?

That being said, here is the signature of this brand new session handling class: 

class SessionHandler{
   public function __construct(){
     session_start();
   }
   public function setVariable($value=’default’,$varname=’default’){
     $_SESSION[$varname]=$value;
   }
   public function getVariable($varname=’default’){
     if(!$_SESSION[$varname]){
       return false;
     }
     return $_SESSION[$varname];
   }
   public function destroy(){
     session_start();
     session_unset();
     session_destroy();
   }
}

As you can see, the above session handler presents a few basic methods for registering and deregistering session variables, in addition to destroying a complete session. This class is the one that I’m going to use in conjunction with the others defined earlier to implement a fully functional searching application. However, the completion of this MySQL-driven search engine will be done in the last tutorial of the series.

Final thoughts

In this second article of the series I explained in detail how to add result pagination capabilities to the existing MySQL-based search engine by incorporating some basic methods into the "Result" class that was developed in the first tutorial. I also defined a brand new session handling class for maintaining the value of a given search string across different web pages.

Thus, the next logical step will consist of putting all these classes to work together, in this way completing the development of this expandable search application. Naturally, this will be done in the last part of the series, so you don’t have any excuses to miss it!

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan