HomeMySQL Page 2 - Paginating Result Sets for a Search Engine Built with MySQL and PHP 5
Listing the full source code for the original 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.
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>
<?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.
MySQL 
/ 4 
Oracle Announces New MySQL Specialization
