Home arrow MySQL arrow 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.

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

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.



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