Devlog

Pagination refers to splitting up a large result set into multiple pages. Probably the most recognized use of pagination is that displayed by Google when you perform a search. As long as there have been applications, there has been a need to paginate. Everything from long documents to search results, it's something almost every app you use has in common. Considering just how common pagination is, it's the one thing that seems to trip up many amateur programmers. Today I'm going to go over how to create a simple pagination feature in PHP with data collected from a MySQL database. You can download the sources and example database at the end of the post.

The Basics

Let's start with just displaying the content on the screen in a table. Throughout this post I am going to be using a table called 'users' with fields for an ID, a first name, last name and email address. Here's some basic code that will just output these details into a (very long) table:

<?php
	$res = mysql_query("
		SELECT *
		FROM users
	");
?>
<table cellspacing="1" cellpadding="0" width="100%">
	<thead>
		<th>ID</th>
		<th>First Name</th>
		<th>Last Name</th>
		<th>Email</th>
	</thead>
	<tbody>
		<?php while ($user = mysql_fetch_assoc($res)): ?>
			<tr>
				<td><?php echo $user['id']; ?></td>
				<td><?php echo $user['first']; ?></td>
				<td><?php echo $user['last']; ?></td>
				<td><?php echo $user['email']; ?></td>
			</tr>
		<?php endwhile; ?>
	</tbody>
</table>

This is a pretty simple piece of code. We run a MySQL query to fetch all the users in the system -- we've all done that before, right? Of course the problem is when there are just too many users to list all at once. In the case of my example database, there are 200 records.

Introducing: LIMIT

A feature of any SQL database (that follows standards, anyway) is the ability to tell the server where to start returning rows from, and how many rows to return. Let's first modify the query a bit so it looks like this:

$res = mysql_query("
	SELECT *
	FROM users
	LIMIT 5
");

The LIMIT 5 makes the database return a maximum of 5 rows. So instead of returning all 200 of the rows in my example database, only the first 5 are returned -- that would be users with ID's 1-5.

In addition to just limiting the number of rows to return, you can also provide an offset. The offset is how many rows to skip before returning the result set. For example, let's modify the query one more time:

$res = mysql_query("
	SELECT *
	FROM users
	LIMIT 10, 5
");

The first number is the offset, and the second number is the row count. LIMIT 10, 5 means "Get 5 rows starting after the 10th row". In other words, rows 11-15.

So the magic behind pagination is this LIMIT clause. The rest of the work is just some basic PHP to display a page selector, and a bit of math to get the offset and row count from the page number.

The PHP Side Of Things...

Before we start, let's outline what information we need available:

• The number of rows to display per page. This is usually just a configuration setting. We'll hard code this value directly into our script.
• The current page the user is viewing. This is simple, it's just the users current page or '1' if they have no page. We'll pass this along in the query string.
• The number of total rows the user can view. We need this number so we can calculate how many pages we will have to show in the page selector.
• The total number of pages.
• The offset to plug into the LIMIT clause, based on the values for per-page and the current page.

The only "hard parts" are getting the number of total rows, calculating how many pages are required to show them all, and calculating the offset for use in the LIMIT clause. Here's how I did it:

$per_page = 10;
$cur_page = isset($_REQUEST['p']) ? (int)$_REQUEST['p'] : 1;

$res = mysql_query("
	SELECT COUNT(*)
	FROM users
");
$num_rows = mysql_result($res, 0, 0);

$total_pages = ceil($num_rows / $per_page);

if ($cur_page <1 || $cur_page> $total_pages) {
	$cur_page = 1;
}

$offset = (($cur_page - 1) * $per_page);

Now that we have the code, let's go over it:

• First we set $per_page to a hard-coded value. In this case, it is 10. In the future you might want to make this user-configurable, but for now lets keep it simple.
• Next is getting the current page by setting it to the 'p' incoming variable (for example, index.php?p=10). If there is no 'p' variable, then default to the first page. Also note that I have casted the value to an integer, we don't want the user messing around with the query string and providing some string.
• Then we get the total amount of rows in the database. A simple COUNT(*) gives us the number we're after. After running the query it's just a matter of sticking the count into the variable $num_rows. Note that if you introduce some kind of searching into your scripts, the same WHERE and JOIN clauses will need to be present in this query as well as your search query. $num_rows is meant to contain the number of total rows that the user will want to view, not just the total number of rows in the table.
• To get the total number of pages, we use some simple math. The number of pages will be the total number of rows divided by the number of rows per page. Note the use of the ceil() function here. We want to round up the number to ensure that even a page with only 3 rows will still be viewable.
• Then we do a check to make sure $cur_page is correct. It needs to be at least 1, and at most $total_pages. This check is here to ensure the user doesn't try something like 99999999999.
• Finally, we calculate $offset that will be used in the LIMIT clause of our query. The offset is simply $cur_page minus 1, multiplied by the number of rows per page. We have to subtract 1 from the current page, otherwise it would always be a page ahead.

Now that we have the numbers, we can alter our data-getting query and plug them into the LIMIT clause:

$res = mysql_query("
	SELECT *
	FROM users
	LIMIT $offset, $per_page
");

Tada! We're all done! Well, that part at least. The only part left is to generate the links to the other pages.

Generating The Page Links

There are several different styles of page links. I prefer the kind that have a first/last and prev/next links to the side of the actual page links, and the current page should be surrounded by 5 pages to the left/right. For example, if I'm on page 15, there will be links to pages 9-14 on the left and pages 16-20 on the right. This is the style that I'm going to demonstrate here.

Undoubtedly there are numerous ways to generate such a navigation. Here's how I did it:

<?php
	$page_pad = 5;

	$min_page = max(1, $cur_page - $page_pad);
	$max_page = min($total_pages, $cur_page + $page_pad);

	$show_prev = $cur_page> 1 ? $cur_page - 1 : false;
	$show_next = $cur_page <$total_pages ? $cur_page + 1 : false;
?>
<div class="page_nav">
	<ul>
		<li><a href="?p=1">&laquo; First</a></li>
		<?php if ($show_prev): ?><li><a href="?p=<?php echo $show_prev; ?>">&lt;Prev</a></li><?php endif; ?>

		<?php for ($page = $min_page; $page <= $max_page; $page++): ?>
		<li <?php if ($page == $cur_page): ?>class="cur_page"<?php endif; ?>><a href="?p=<?php echo $page; ?>"><?php echo $page; ?></a></li>
		<?php endfor; ?>

		<?php if ($show_next): ?><li><a href="?p=<?php echo $show_next; ?>">Next&gt;</a></li><?php endif; ?>
		<li><a href="?p=<?php echo $total_pages; ?>">Last &raquo;</a></li>
	</ul>
</div>

• First we set a $page_pad setting that is used to determine how many pages to either side of the current page should be shown. I think 5 is an appropriate number.
• Next we need to determine the minimum and maximum page numbers that will be shown, based on the current page and how many pages to pad it with. We can't simply do the current page minus the padding for the minimum or the current page plus the padding for the maximum. This would make for the possibility of negative pages being displayed (for example, page 1 minus 5 padding) and pages that are over the total pages. To solve this issue we can use the min() and max() functions to make sure the $min_page and $max_page never go over the acceptable values.
• Then we just need some simple logic to determine if the previous and next pages should be display, and if so, what their page numbers are.
• The next bit is outputting the HTML for all the paged links

There you have it! Pretty simple, isn't it?

Conclusion

I hope I've helped some new programmer out there with this post. I had just gotten tired of so many people asking me how to add pagination to their apps and not having any definitive source to point them to. Not exactly the most advanced stuff, but still something I though needed to be covered.

You can download the full source code here. The package contains a PHP file, a CSS file and an SQL file that contains the test 'users' table and its data. The result looks like this: