MySQL + PHP Simple Pagination

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

1

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

Comments posted (1)

Rob Adshead says... on 10-03-2010

I was working on this script for my own website and I made the pages more advanced

< ?php
 
    $offset = mysql_escape_string(strip_tags($_GET['offset']));
    $limit = mysql_escape_string(strip_tags($_GET['limit']));
 
    if(empty($limit)){
        $limit = "25";
    }
    if(empty($offset) || $offset < 0){
        $offset = "0";
    }
 
 
    $result = mysql_query("SELECT * FROM `table` LIMIT " . $limit . " OFFSET " . $offset);
    while($row = mysql_fetch_object($result))
    {
		?>
 
        	options
 
    	< ?
    }
 
    $previous = $offset - "1"; 
    $next = $offset + "1";
 
    if($previous < 0){
        $previous = "0";
    }
 
			$sql = mysql_query("SELECT * FROM licenses");
			$sql1 = mysql_query("SELECT table FROM licenses ORDER BY id DESC LIMIT 1");
			$row1 = mysql_fetch_object($sql1);
			$rows = mysql_num_rows($sql); 
 
			$offset1 = $offset + $limit;
 
			if($rows > $limit && $offset == "0"){ // if its the first page it only gives next if more pages are avaiable
 
	?>
 
   					<a href="?offset=<?= $next ? rel="nofollow">&limit=< ?= $limit ?>" class="link">Next</a>
 
 
	< ?
			}elseif($row1->licenseId < = $offset1 && $offset != "0"){ // if its the last page it will only give previoous
 
	?>
 
    				 <a href="?offset=<?= $previous ? rel="nofollow">&limit=< ?= $limit ?>" class="link">Previous</a>
     < ?
			}elseif($rows > $limit && $offset != "0"){ // if its in the middle and theres pages to the left and right of the current page it gives the option for both
    ?> 
           			 <a href="?offset=<?= $previous ? rel="nofollow">&limit=< ?= $limit ?>" class="link">Previous</a> |  <a href="licenses.php?offset=<?= $next ? rel="nofollow">&limit=< ?= $limit ?>" class="link">Next</a>
 
	< ?
			}else{ // if theres no pages appart from the 1 it doesn't give any options
	?>
    < ?
			}
			?>

Write a comment

You must be logged in to post a comment.

Tutorials Written By Peter Kelly