MySQL + PHP Simple Pagination

Posted by Pete | Posted in PHP Tutorials | Posted on 11-10-2009-05-2008

0

Pagination is used to allow you to split up rows in a MySQL table into multiple pages. In this tutorial we will show you how you can make a simple version.

So lets get started first we need to sort out the variables. We will need 2 variables offset and limit.

/**
 * SORT OUT VARIABLES
 */
$offset = mysql_escape_string(strip_tags($_GET['offset'])); // Protect the variables in the url from injection
$limit = mysql_escape_string(strip_tags($_GET['limit'])); // Protect the variables in the url from injection
 
if(empty($limit)){ // Check if $limit is empty or not.
	$limit = "10"; // If $limit is empty set it to 10.
}
if(empty($offset) || $offset < 0){ // Check if $offset is empty or is smaller than 0
	$offset = "0"; // If $offset is empty set it to 0.
}

So now weve got the variables sorted, Lets now echo the table and the information inside. In this example I am just using a basic table with 2 columns, column1 and column2 and table name of “table”.

/**
 * RETRIEVE THE TABLE AND ECHO ANY INFORMATION IN IT.
 */
 
$result = mysql_query("SELECT * FROM `table` LIMIT " . $limit . " OFFSET " . $offset); // Select from the table as usual but adding the limit and offset paramaters.
while($r=mysql_fetch_array($result)) // Go through row by row
{
   echo $r['column1'] . " " . $r['column2'] . "<br />"; // Echo the row and the data inside.
}

Now we have the limited the information displayed lets create the previous and next links.

/**
 * CREATE THE LINKS
 */
$previous = $offset - $limit; // Workout offset for previous link
$next = $offset + $limit; // Workout offset for next link
 
if($previous < 0){ // Check if either $previous is smaller than 0
	$previous = "0"; // Set $previous to 0
}
 
echo "<a href='?offset=".$previous."&limit=".$limit."'>Previous</a> | <a href='?offset=".$next."&limit=".$limit."'>Next</a>"; // Echo the links.

So now we have it all

<?php
/**
 * SORT OUT VARIABLES
 */
$offset = mysql_escape_string(strip_tags($_GET['offset'])); // Protect the variables in the url from injection
$limit = mysql_escape_string(strip_tags($_GET['limit'])); // Protect the variables in the url from injection
 
if(empty($limit)){ // Check if $limit is empty or not.
	$limit = "10"; // If $limit is empty set it to 10.
}
if(empty($offset) || $offset < 0){ // Check if $offset is empty or is smaller than 0
	$offset = "0"; // If $offset is empty set it to 0.
}
 
 
/**
 * RETRIEVE THE TABLE AND ECHO ANY INFORMATION IN IT.
 */
 
$result = mysql_query("SELECT * FROM `table` LIMIT " . $limit . " OFFSET " . $offset); // Select from the table as usual but adding the limit and offset paramaters.
while($r=mysql_fetch_array($result)) // Go through row by row
{
   echo $r['column1'] . " " . $r['column2'] . "<br />"; // Echo the row and the data inside.
}
 
/**
 * CREATE THE LINKS
 */
$previous = $offset - $limit; // Workout offset for previous link
$next = $offset + $limit; // Workout offset for next link
 
if($previous < 0){ // Check if either $previous is smaller than 0
	$previous = "0"; // Set $previous to 0
}
 
echo "<a href='?offset=".$previous."&limit=".$limit."'>Previous</a> | <a href='?offset=".$next."&limit=".$limit."'>Next</a>"; // Echo the links.
?>

So that’s it. If you have any questions as usual leave a comment. Thanks

Read More

Write a comment

You must be logged in to post a comment.

Tutorials Written By Peter Kelly