Paginating Result Sets for a Search Engine Built with MySQL and PHP 5 - Adding pagination capabilities to the initial search engine (Page 3 of 4 )
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).'"><<</a> ';
}
for($i=1;$i<=$numPages;$i++){
$output.=$i!=$page?'<a href="'.$_SERVER['PHP_SELF'].'?
page='.$i.'">'.$i.'</a> ':$i.' ';
}
if($page<$numPages){
$output.=' <a href="'.$_SERVER['PHP_SELF'].'?&page='.
($page+1).'">>></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.
Next: Maintaining the value of a given search term across different web pages >>
More MySQL Articles
More By Alejandro Gervasio
