There comes a time when a list of records output by a MySQL query becomes too long to realistically display on a single page, and the programmer wants to break it up into pages. It might be log activity, forum posts or whatever, but the principle is the same – we only want to view a manageable section of the entire record list.

Let’s assume we’re dealing with a set of forum posts in a thread. Your query would take a unique number for the thread ($threadId) and you would pull out a list of posts like this:

<php
 $postSQL="SELECT * FROM posts WHERE (THREAD=$threadId) ORDER BY ID";
 $posts = mysql_query($postSQL) or die(mysql_error());
 while($post=mysql_fetch_array($posts)) {
  // display posts here
 }
?>

This would return all of the posts for this thread in order of creation – ID being the master key for this table.

To split this into pages, we first need to decide how many posts we want per page. Once we have decided on this, we can see how many lines our mysql_query(), above, returns and calculate the number of pages. After that we can decide which page to show and calculate which posts go on that page.

Here’s the altered code:

<?php
 $postSQL="SELECT * FROM posts WHERE (THREAD=$threadId) ORDER BY ID";
 $posts = mysql_query($postSQL) or die(mysql_error());

 $postCount   =mysql_affected_rows();
 $postsPerPage=25;
 $pageCount   =ceil($postCount/$postsPerPage);
 $pageNum     =min(max(1, (int)$_GET['page']), $pageCount);
 $firstPost   =($pageNum-1)*$postsPerPage;
 $lastPost    =min($firstPost+$postsPerPage, $postCount);

 $posts = mysql_query("$postSQL LIMIT $firstPost, $postsPerPage") 
 or die(mysql_error());

 while($post=mysql_fetch_array($posts)) {
  // display posts here
 }
?>

As you can see, we call mysql_query() twice now – the first time to get the total number of posts and the second time to get the subset of those posts once we have decided which ones we want. The highlighted green section shows what has been added, and I’ll go through them here:

$postCount =mysql_affected_rows();

Records the total number of posts returned. We need this so we can calculate how many pages we have in total using…

$postsPerPage=25;

This is how many posts we want on a single page. You can store this in a table for easy altering, or allow it to be overwritten from the URL so that users can decide on their own page sizes.

$pageCount =ceil($postCount/$postsPerPage);

The number of pages is the number of posts divided by the number of posts per page, and rounded up, so ‘30 posts at 25 posts per page’ would return two pages.

$pageNum =min(max(1, (int)$_GET['page']), $pageCount);

Slightly more tricky to read, this line limits the number of the page to be displayed between 1 and the total number of pages, taking into account any page number specified in the URL with ‘&page=’, even if a value is not given.

$firstPost =($pageNum-1)*$postsPerPage;

We need to know the record position – not the ID – of the first post so that we can tell MySQL where to start counting from. This number starts at zero whereas your ID would start at one.

$lastPost =min($firstPost+$postsPerPage, $postCount);

We don’t actually need $lastPost for anything in this example, but it’s nice if you want to display a message along the lines of “Showing posts 25 to 50 of 376″. This value is the actual ID of the last post on the page, so it starts at one and never goes above $postCount.

$posts = mysql_query(“$postSQL LIMIT $firstPost, $postsPerPage”)
or die(mysql_error());

Finally we run your original SQL statement – here held in $postSQL – a second time but with our calculated limits applied.