Home arrow PHP arrow Page 5 - Paginating MySQL Data with PHP

Conclusion - PHP

Have a large MySQL dataset that you would like to display on your website, but don't want to bog down your users' connections with large HTML renderings? Try paginating your data.

  1. Paginating MySQL Data with PHP
  2. Getting Started
  3. Making a Connection
  4. Limiting the Dataset
  5. Conclusion
By: Adrian Portsmouth
Rating: starstarstarstarstar / 53
March 27, 2003

print this article


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

blog comments powered by Disqus
escort Bursa Bursa escort Antalya eskort


- Hackers Compromise PHP Sites to Launch Attac...
- Red Hat, Zend Form OpenShift PaaS Alliance
- PHP IDE News
- BCD, Zend Extend PHP Partnership
- PHP FAQ Highlight
- PHP Creator Didn't Set Out to Create a Langu...
- PHP Trends Revealed in Zend Study
- PHP: Best Methods for Running Scheduled Jobs
- PHP Array Functions: array_change_key_case
- PHP array_combine Function
- PHP array_chunk Function
- PHP Closures as View Helpers: Lazy-Loading F...
- Using PHP Closures as View Helpers
- PHP File and Operating System Program Execut...
- PHP: Effects of Wrapping Code in Class Const...

Developer Shed Affiliates


Dev Shed Tutorial Topics: