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


