Tutorials
PHP Basic Pagination
Basic Pagination
As a web developer, you will often be tasked to display large amounts of data to the user in some kind of easy to read format. Let's say for instance you have a list of employees in your database, and you want to be able to list them on your web page. If you only have a dozen or so employees, it's no big deal to just make a simple loop and display them all on the same page, right? Well what happens when you have 50 employees? 100? 1,000? Suddenly listing all of them on the same page doesn't sound so hot.
Pulling out all that data at the same time can leave your user tapping his fingers on the desk wondering what the frak is taking so long, and when he finally does get his info, it's a whole frakking novel on one page! Can you imagine going down to the bookstore and picking up a book and instead of the story being divided up by pages, it's all on one really long page? I heard a rumor they used to do that back in the ancient days. I think they were called scrolls or something, I dunno.
Well anyways, it makes way more sense to break up your list into page-sized chunks, and only query your database one chunk at a time. This drastically reduces server processing time and page load time, as well as gives your user smaller pieces of info to digest, so he doesn't choke on whatever crap you're trying to feed him. The act of doing this is called pagination.
A basic pagination routine seems long and scary at first, but once you close your eyes, take a deep breath, and look at each piece of the script individually, you will find it's actually pretty easy stuff. In fact, in my experience over the years of helping out on the forums, peoples' hardest problem about pagination is figuring out what it's called in the first place! But since we've got that part sorted out, the rest should be a piece of cake, right? :)
First things first, you need a table with some data in your database to work with. Now I'm not going to go into the details of how to setup a database or how to make a table etc.. if you are really at that point of things, then this tutorial isn't really for you. It really doesn't matter what kind of data you have, so if you have an existing table full of info you want to use, you can just plug and chug your table/column info into the script. But for the purposes of this tutorial I will use a table called 'numbers' and it will have two columns one called 'number' type int and the other one called 'id' type int, auto-incremented.
If you opted to make a new table with that info, here's a quick and dirty script to get it populated:
<?php
$conn = mysql_connect('localhost','dbusername','dbpassword') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR);
for ($x = 0; $x < 106; $x++) {
$number = rand(100,999);
$sql = "INSERT INTO numbers (number, id) VALUES ($number, '')";
$query = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
}
?>Long story short: make one hundred six rows of random 3 digit numbers. Why 106? It's just a random number, and in the pagination script, we will make each page hold 10 rows, and I wanted the last page to show something less than 10 rows.
Why 3 digits? Why 6 rows at the end??? 3 digits and 6 rows were random numbers I chose and has nothing to do with the mark of the beast or the end of the world....or does it....???
Okay before anything else, here is the code in all it's full and basking glory, because there's nothing more I hate about tutorials than how people like to break them all into itty bitty little chunks and you have to copy and paste, copy and paste, copy and paste. It's aggravating....and yet it's somewhat contrary to the theme of this tutorial. Isn't it ironic?
<?php
// database connection info
$conn = mysql_connect('localhost','dbusername','dbpass') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR);
// find out how many rows are in the table
$sql = "SELECT COUNT(*) FROM numbers";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];
// number of rows to show per page
$rowsperpage = 10;
// find out total pages
$totalpages = ceil($numrows / $rowsperpage);
// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
// cast var as int
$currentpage = (int) $_GET['currentpage'];
} else {
// default page num
$currentpage = 1;
} // end if
// if current page is greater than total pages...
if ($currentpage > $totalpages) {
// set current page to last page
$currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
// set current page to first page
$currentpage = 1;
} // end if
// the offset of the list, based on current page
$offset = ($currentpage - 1) * $rowsperpage;
// get the info from the db
$sql = "SELECT id, number FROM numbers LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
// echo data
echo $list['id'] . " : " . $list['number'] . "<br />";
} // end while
/****** build the pagination links ******/
// range of num links to show
$range = 3;
// if not on page 1, don't show back links
if ($currentpage > 1) {
// show << link to go back to page 1
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
// get previous page num
$prevpage = $currentpage - 1;
// show < link to go back to 1 page
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end if
// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
// if it's a valid page number...
if (($x > 0) && ($x <= $totalpages)) {
// if we're on current page...
if ($x == $currentpage) {
// 'highlight' it but don't make a link
echo " [<b>$x</b>] ";
// if not current page...
} else {
// make it a link
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
} // end else
} // end if
} // end for
// if not on last page, show forward and last page links
if ($currentpage != $totalpages) {
// get next page
$nextpage = $currentpage + 1;
// echo forward link for next page
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
// echo forward link for lastpage
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>Okay so there's the code. Basically the idea is to:
- Find out how many rows you have in your table
- Find out how many pages you want to make, based on how many rows per page you want to show
- Find out what page we're on
- Pull out just the rows of the current page
- Display the info
- Make some links to go to the first page, previous page, some pages around the current page, the next page, and the last page.
There's comments aplenty in the code, so you may be able to figure out what's going on based on that, but let's go ahead and break it down for some further commentary. Moving on...
// database connection info
$conn = mysql_connect('localhost','dbusername','dbpass') or trigger_error("SQL", E_USER_ERROR);
$db = mysql_select_db('dbname',$conn) or trigger_error("SQL", E_USER_ERROR);Just a couple of lines of code to connect to your database, obviously.
// find out how many rows are in the table
$sql = "SELECT COUNT(*) FROM numbers";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);
$r = mysql_fetch_row($result);
$numrows = $r[0];We need to find out how many rows are in the table so we can figure out how many pages we are going to have. So we do a basic count(*) query and assign that number to $numrows.
// number of rows to show per page $rowsperpage = 10; // find out total pages $totalpages = ceil($numrows / $rowsperpage);
$rowsperpage is where we decide how many rows per page we want to have. To find out the total pages we will have, we take the number of rows ($numbrows) and divide that by the rows per page ($rowsperpage). Since there's no such thing as a half a page (unless your dog chews it up), we will round up, using ceil().
// get the current page or set a default
if (isset($_GET['currentpage']) && is_numeric($_GET['currentpage'])) {
// cast var as int
$currentpage = (int) $_GET['currentpage'];
} else {
// default page num
$currentpage = 1;
} // end ifWhen you click on one of the page navigation links, the target page is passed through the URL via the GET method. So here we want to grab the target page number. We want to first check to see if it's even there and if it is number, because if this is the first time someone loads the page, it won't be set. Or someone could manually change the value in the URL.
If it is there and it is numeric, we are going to force it to be type int. What this means is if someone were to change the page number in the URL to like 9.75, it will truncate it to 9, because there's no such thing as page 9.75 unless you're Harry Potter.
If the value fails to be set or is not numeric, we default to page 1.
// if current page is greater than total pages...
if ($currentpage > $totalpages) {
// set current page to last page
$currentpage = $totalpages;
} // end if
// if current page is less than first page...
if ($currentpage < 1) {
// set current page to first page
$currentpage = 1;
} // end ifThe next thing we want to do is check to make sure it's a valid page number. We don't want users trying manually enter in page 401 in our 400 page book, just because they hate the ending and refuse to believe it ended that way omg what were we thinking nono there's GOT to be another page stuck together or something right??? So yeah... if they enter a negative number or some number higher than our total pages, it defaults to 1 or $totalpages, respectively.
// the offset of the list, based on current page $offset = ($currentpage - 1) * $rowsperpage;
Since we have our list of 106 rows, and we want to only grab 10 specific rows of our target page, we need to find the "offset." That is, for example, page 8 is not going to start at the top of the list. It's going to start on row 80. Actually, it's going to start on row 70, since computers like to start counting at zero, not one. So we take our current page (let's say page 8), subtract 1 from it, and then multiply by our rows per page (10). Again, computers start at zero, so:
page 1 of our list will be rows 0-9.
page 2 of our list will be rows 10-19.
etc...
// get the info from the db
$sql = "SELECT id, number FROM numbers LIMIT $offset, $rowsperpage";
$result = mysql_query($sql, $conn) or trigger_error("SQL", E_USER_ERROR);Now we run our query. It's a basic query telling sql to look at our numbers table, start at our offset, and give us 10 rows, starting at that offset. Since our table has 106 rows, if we are on the last page, it will give us the last 6 rows.
// while there are rows to be fetched...
while ($list = mysql_fetch_assoc($result)) {
// echo data
echo $list['id'] . " : " . $list['number'] . "<br />";
} // end whileNext we have a basic loop to display the rows of the current page. Nothing fancy like tables or divs or anything like that, as that is not the point of this tutorial. Next we look at building the navigation bar...
/****** build the pagination links ******/
// if not on page 1, don't show back links
if ($currentpage > 1) {
// show << link to go back to page 1
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=1'><<</a> ";
// get previous page num
$prevpage = $currentpage - 1;
// show < link to go back to 1 page
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$prevpage'><</a> ";
} // end ifThe first thing we want to do is make some "<< <" links. "<<" brings us back to page 1. "<" takes us to the previous page. If we are on page 1, then there's no reason to show the links, so our condition checks to see if we are on some other page besides page 1.
The first echo makes the "<<", passing page 1 as the target page. Since the "<" link targets the previous page, we make a variable that simply subtracts 1 from the current page, and make the link.
// range of num links to show
$range = 3;
// loop to show links to range of pages around current page
for ($x = ($currentpage - $range); $x < (($currentpage + $range) + 1); $x++) {
// if it's a valid page number...
if (($x > 0) && ($x <= $totalpages)) {
// if we're on current page...
if ($x == $currentpage) {
// 'highlight' it but don't make a link
echo " [<b>$x</b>] ";
// if not current page...
} else {
// make it a link
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$x'>$x</a> ";
} // end else
} // end if
} // end forIn this chunk of code, we make the range of page links display. The idea is that if we're on page 8, it will show:
<< < 5 6 7 [8] 9 10 11 > >>
We start with a range ($range). The range covers how many numbers to the left and to the right of the current page (not total numbers to be displayed). The range in the code is 3, so if we are on page 8, it shows 5 6 7 on the left, 8 in the middle, 9 10 11 on the right. The loop starts at the current page minus 3, and iterates to the current page plus 3.
Inside the loop, we first check to make sure that the current iteration is a valid page number, because for instance, if we are on page 1, we do not want to show links to page -2 -1 0. The next thing we do is check to see if the current iteration of the loop is on the current page. If it is, we want to 'highlight' it by making it bold with brackets around it to make it stand out a little. We also do not want to make it a link, because it's just silly to make a link to the same page you are on. I can just imagine poorly written page raker or spider bots getting stuck in an infinite loop from that, which is kinda funny, except it's your bandwidth they are eating up.
Anyways, if it's a valid page number, and it's not the current page, then make a link of that page number. The end.
// if not on last page, show forward and last page links
if ($currentpage != $totalpages) {
// get next page
$nextpage = $currentpage + 1;
// echo forward link for next page
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$nextpage'>></a> ";
// echo forward link for lastpage
echo " <a href='{$_SERVER['PHP_SELF']}?currentpage=$totalpages'>>></a> ";
} // end if
/****** end build pagination links ******/
?>This chunk of code works exactly like the first chunk of code that made the "<< <" links, except the opposite. If we're not on the last page, make the "> >>" links. Take the current page and add 1 to it to make the ">" link. Target page for the ">>" link is simply the $totalpages var.
Well, there you have it. That's really all there is to it. There are plenty of extra bells and whistles you can add on to this, like having links to sort your data by each column, etc.. the sky's the limit! Perhaps I will eventually publish some sort of "Expanded Pagination" tutorial.
Until then, Happy coding!
Crayon Violent