Ooer.com - Chris Neale

Paging Through A Resultset

One of the more common querys that people have when they first start out writing dynamic web sites is how to accomplish pagination. Very few web sites have all the content listed on a single page, so its useful to be able to have listings span multiple pages, and use simple forward and back navigation to flip through, rather like the pages of a book. This is called pagination, or paging.

There are two main ways to do pagination. The first is by using the LIMIT function in SQL to limit the rows returned by the database to the ones that should be displayed to the user. The second method is by using the mysql_data_seek() function to jump to the middle of a resultset. In this article we will be looking at the first method as it is more robust and can cope with large datasets. The second method uses just one database query (unlike the first that uses two), but requires all the database results to be returned and thus uses more memory.

To start off we need to set up some variables, and check some user input variables. If the user input is empty we need to set it to some default values.


<?php

$records_per_page = 10;

$page = ($_GET['page'] > 0) ? $_GET['page'] : 1;

?>

The next step is to find out how many pages of things there are in the database. To do this we have to query the database and return a count of how many records there are in total.


<?php

$sql = "select count(item_id) as total from items";

$result = mysql_query($sql,$connection);

$record = mysql_fetch_object($result);

$total = $record->total;

$num_of_pages = ceil($total/$records_per_page);

?>

In order to make sure the user does not do beyond the range of the number of pages in the database we should check whether $page is valid. The range available is from page 1 to $num_of_pages.


<?php

$page = ($page <= 0) ? 1 : $page;

$page = ($page > $num_of_pages) ? $num_of_pages : $page;

?>

We can now draw the previous and next buttons. We also want our users to be able to jump directly to a particular page, so we need to generate number links too. The links take the form of the script name with a $page GET variable attached.


<?php

$html[] = "<a href=\"page.php?page=".$page-1."\">Prev</a>";

for ($x=1;$x<=$num_of_pages;$x++) {

$html[] = "<a href=\"page.php?page=".$x."\">$x</a>";

}

$html[] = "<a href=\"page.php?page=".$page+1."\">Prev</a>";

?>

Now that we have an array of HTML links we simply implode() it and echo the resulting string to the page.


<?php

echo implode(" | ",$html);

?>

The reason for using an array of html and the implode function is that it neatly seperates each link with whatever text we want. The SQL to get the results for the page the user is on is the same as an ordinary query, with the exception of including a limit statement at the end.


<?php

$sql = "select * from items limit ".(($page-1)*$records_per_page).",".records_per_page;

?>

Comments

Comments are not currently being accepted for this article.

Sidebar

Published:

10/01/2007

Views:

2500

Author:

Chris Neale

Labels:

mysql, pagination, paging, php

Print:

View print version