PHP Tutorials and code

PHP Tutorial : Results Pagination

Jun 15, 2009 Complete Scripts

Among the most common tasks for a php CMS writer is to handle pagination.

Pagination is needed when there are many entries in the database and they can not be shown in a single page of results. A pagination navigation bar gives the user the ability to see a smaller list of entries.

Pagination is not as easy as it seems, expecially when dealing with seo friendly urls or multi language sites. However here’s a solution for pagination in php. Simple and up to the point:

First, we calculate the total amount of entries. Then the numbers of pages needed to show everthing. And, of course, we need to know how many results are to be displayed per page. Finally, we’ll print out the entries and links for next, previous and each individual page number.

MySql LIMIT clause accepts either one or two values. In this example we use two so it returns values between -say- 10 and 20. If you only use one value, as in LIMIT 10, it will return the first 10 results.

The second value is a quantity, not an index!
In other words, don’t do LIMIT 0,10 then LIMIT 10,20 then LIMIT 20,30: it won’t work. Instead, use LIMIT 0,10, LIMIT 10,10, LIMIT 20,10.

<?php
//include database connection (check previous posts to get this one)
>include('dbConnection.php');
//get the number of total rows
$query = "SELECT * FROM TableName";
$result = mysql_query($query);
// Number of records found
$num_record = mysql_num_rows($result);
// Number of results per page
$display = 5;

if(isset($_GET['page'])) {
$currentPage = $_GET['page'];
}else{
$currentPage = 1;
}
//last page - we use ceil because page range is 1 to Max, this way page "2.5" becomes page 3
$lastPage = ceil($num_record/$display);
//limit in the query thing
$limitQ = 'LIMIT ' .($currentPage - 1) * $display .',' .$display;
//normal query and print results
$query = "SELECT * FROM TableName $limitQ";
$result = mysql_query($query);
//here you do your loop like
>while($row=@mysql_fetch_object($result)) {

print "$row->FieldName";
}
//pagination navigation (links)

//previous
>if ($currentPage == 1) {

print "Prev ";
} else {

print "<a href=pagename.php?page=1>First page</a> ";
$previousPage = $currentPage-1;

print "<a href=pagename.php?page=$previousPage>Previous</a>";
}

print " { Page $currentPage of $lastPage } ";
//for next pages links
>if ($currentPage== $lastPage) {

print "Next last";
} else {
$nextPage = $currentPage+1;

print " <a href=pagename.php?page=$nextPage>NEXT</a> ";

print " <a href=pagename.php?page=$lastPage>LAST</a> ";
}
?>

Now you can use this pagination to split your mysql results into multiple webpages, but there are better ways to handle this problem, as we’ll see in the future: keep in mind that right now we are reading all values every time, and this may burden our script. A better way would be to pre-count the number of results and then select only those we are going to show.

Also, for something like a comment page, an ajax pagination approach would probably be better. Again, we’ll get back at this in another tutorial -as always drop a comment if there is something you’d like to see explained more in depth.

Tags: paginate, pagination, php links, php mysql limit, php navigation, php paginate, php paginate entries, php paginate results, php pagination, php results pagination, results pagination, set a number of results per page, split mysql results into page