Home arrow MySQL arrow Page 3 - Paginating Result Sets for a Search Engine Built with MySQL and PHP 5

Adding pagination capabilities to the initial search engine - MySQL

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.

TABLE OF CONTENTS:
  1. Paginating Result Sets for a Search Engine Built with MySQL and PHP 5
  2. Listing the full source code for the original search engine
  3. Adding pagination capabilities to the initial search engine
  4. Maintaining the value of a given search term across different web pages
By: Alejandro Gervasio
Rating: starstarstarstarstar / 4
August 01, 2007

print this article
SEARCH DEV SHED

TOOLS YOU CAN USE

advertisement

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.



 
 
>>> More MySQL Articles          >>> More By Alejandro Gervasio
 

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort
   

MYSQL ARTICLES

- Oracle Unveils MySQL 5.6
- MySQL Vulnerabilities Threaten Databases
- MySQL Cloud Options Expand with Google Cloud...
- MySQL 5.6 Prepped to Handle Demanding Web Use
- ScaleBase Service Virtualizes MySQL Databases
- Oracle Unveils MySQL Conversion Tools
- Akiban Opens Database Software for MySQL Use...
- Oracle Fixes MySQL Bug
- MySQL Databases Vulnerable to Password Hack
- MySQL: Overview of the ALTER TABLE Statement
- MySQL: How to Use the GRANT Statement
- MySQL: Creating, Listing, and Removing Datab...
- MySQL: Create, Show, and Describe Database T...
- MySQL Data and Table Types
- McAfee Releases Audit Plugin for MySQL Users

Developer Shed Affiliates

 


Dev Shed Tutorial Topics: