Paginating MySQL Data with PHP - Conclusion
( Page 5 of 5 )

And that is all there is to it. The complete script using our example database will look like this (In this article we have named this file result.php, this is significant in echoing for the next prev and numbered links):

<?
// Set Script Variables
$DB_Host="localhost";
$DB_Name="MyDataBase";
$DB_User="MyUserName";
$DB_Pass="MyPassword";
$Per_Page=10;
// 
Open MySQL Connection
$Connection=mysql_connect($DB_Host, $DB_User, $DB_Pass);
// 
Run The Query Without a Limit to get Total result
$SQL="SELECT COUNT(*) AS Total 
FROM Products WHERE Description
           LIKE '%".$_REQUEST['Keyword']."%'";
$SQL_Result=mysql_db_query($DB_Name, 
$SQL);
$SQL_Result_Array=mysql_fetch_array($SQL_Result);
$Total=$SQL_Result_Array['Total'];
// 
Create a new SELECT Query with the ORDER BY 
// clause and without the COUNT(*)
$SQL="SELECT 
* FROM Products WHERE Description 
         LIKE '%".$_REQUEST['Keyword']."%' 
ORDER BY ProductID";
// Append a LIMIT clause to the SQL statement
if (empty($_GET['Result_Set']))
 
  {
   $Result_Set=0;
   $SQL.=" LIMIT $Result_Set, $Per_Page";
   }else
   {
 
  $Result_Set=$_GET['Result_Set'];
   $SQL.=" LIMIT $Result_Set, $Per_Page";
 
  }
// Run The Query With a Limit to get result
$SQL_Result=mysql_db_query($DB_Name, 
$SQL);
$SQL_Rows=mysql_num_rows($SQL_Result);
// Display Results using a for loop
for 
($a=0; $a < $SQL_Rows; $a++)
    {
    $SQL_Array=mysql_fetch_array($SQL_Result);
 
   $Product=$SQL_Array['Name'];
    $Description=$SQL_Array['Description'];
  
  echo "$Product - $Description<BR><BR>";
    }
// Create Next / Prev Links 
and $Result_Set Value
if ($Total>0)
   {
   if ($Result_Set<$Total && 
$Result_Set>0)
      {
      $Res1=$Result_Set-$Per_Page;
      echo "<A HREF="test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword']."">
 
    <;<; Previous Page</A> ";
      }
   // Calculate and Display Page 
# Links
   $Pages=$Total / $Per_Page;
   if ($Pages>1)
      {
      for ($b=0,$c=1; 
$b < $Pages; $b++,$c++)
          {
          $Res1=$Per_Page * $b;
       
   echo 
 "<A HREF="test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword']."">
 
        $c</A> n";
          }
      }
   if ($Result_Set>=0 && $Result_Set<$Total)
 
     {
      $Res1=$Result_Set+$Per_Page;
      if ($Res1<$Total)
         
{
         echo " <A HREF="test.php?Result_Set=$Res1&Keyword=".$_REQUEST['Keyword']."">
 
       Next Page >></A>";
         }
      }
   }
// Close Database Connection
mysql_close($Connection);
?>

To create the database table we have used in this article you can use the following MySQL:

CREATE TABLE Products (
ProductID int(10) auto_increment NOT NULL PRIMARY KEY,
Name 
varchar(100),
Description text
);

>>> More PHP Articles          >>> More By Adrian Portsmouth