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'] . ""; // 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 "Previous | Next"; // Echo the links.

So now we have it all

Previous | Next"; // 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

	?>

   					&limit=< ?= $limit ?>" class="link">Next

	< ?
			}elseif($row1->licenseId < = $offset1 && $offset != "0"){ // if its the last page it will only give previoous

	?>

    				 &limit=< ?= $limit ?>" class="link">Previous
     < ?
			}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
    ?>
           			 &limit=< ?= $limit ?>" class="link">Previous |  &limit=< ?= $limit ?>" class="link">Next

	< ?
			}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