Subscribe to Tutorial Feed

Flash and PHP Bible

The Flash and PHP Bible has been released! The book can be found on Amazon or wherever fine books are sold in your area.

The Flash and PHP Bible has a forum for quick support.

Scriptplayground » tutorials » php » Simple Pagination Using PHP

Simple Pagination Using PHP

Learn how to add pagination to your PHP projects.

Special thanks to Jhecht for submitting this article to Scriptplayground.

All around the world you see a million tutorials on pagination. Mind you, I'm not saying that these are bad tutorials. In fact, some of them are generally pretty good. However, my only issue with almost every current pagination tutorial using PHP and MySQL is that they usually involve 2 separate queries to the database, which is something i don't think should be used as it takes just a bit longer than is necessary. The way i do it may not be too much better, but i like it more than the double queried methods that are generally associated with most places today.

Here is what i do, in plain and simple English:

  • 1. Run a query on the database, storing the result in a variable.
  • 2. Create an array which will hold all of the values returned by the database
  • 3. Figure out which page we are currently on to decide where in the array we begin, and then calculate the ending iteration(number) to stop at.
  • 4. Loop through the array, checking first if the given index is an array. If it is, continue on doing what we need to do with the information, if not, break the loop.

Ok so let's assume we have a table named Posts. The columns in Posts don't really matter, but let's assume again that there are a few like: post_id, post_title,post_author, post_text,post_date. In our code, we want the latest posts first, so our query to MySQL(or any other database for that matter) is gonna look something like this:

$sql = "SELECT * FROM Posts ORDER BY post_date DESC";

And so we have our query. Now, we just need to run it on the database. I'll assume you know how to connect to MySQL or any other database, but just in case I'll show how to do it.

$connection = mysql_connect("localhost", "User", "Password") or die(mysql_error());
//Connection to the database. Error out if the connection fails for 
//some reason(bad password, bad host, whatever the case is)
@mysql_select_db('database_name');
//Select the database
$sql = "SELECT * FROM Posts ORDER BY post_date DESC";
$ans= mysql_query($sql,$connection) or die(mysql_error());
//Error out if the query fails(bad syntax in mysql);

Not that hard, right? Ok, now we get into the weirder side of things. Now what we need to do is declare an array. This will be the array we loop through to figure out which results are shown. Then we append the array to add the values into it in separate iterations. Our first result will be in iteration 0, second in iteration 1, third in iteration 2 and so on. Then we loop through the MySQL result resource stored in $ans until there are no more results left. When we add that to our file, our code now looks like this:

$connection = mysql_connect("localhost", "User", "Password") or die(mysql_error());
//Connection to the database. Error out if the connection fails for 
//some reason(bad password, bad host, whatever the case is)
@mysql_select_db('database_name');
//Select the database
$sql = "SELECT * FROM Posts ORDER BY post_date DESC";
$ans= mysql_query($sql,$connection) or die(mysql_error());
//Error out if the query fails(bad syntax in mysql);
$results = array();
//Results container
while($row = @mysql_fetch_assoc($ans)){
	$results[]=$row;
}

Again, not that bad, right? Ok next we have to determine which page we are on. For us, we're going to use a variable given to us through the URI itself. We shall name our variable "page", just for simplicity's sake. Now we have to do a check to see if it's present, this is done through the $_GET variable available in PHP in versions greater than 4.1.0. Versions prior to that use $HTTP_GET_VARS. If it's not set at all, we assume they want the first result set displayed(IE, the first page). In PHP code, that looks something like this:

$connection = mysql_connect("localhost", "User", "Password") or die(mysql_error());
//Connection to the database. Error out if the connection fails for 
//some reason(bad password, bad host, whatever the case is)
@mysql_select_db('database_name');
//Select the database
$sql = "SELECT * FROM Posts ORDER BY post_date DESC";
$ans= mysql_query($sql,$connection) or die(mysql_error());
//Error out if the query fails(bad syntax in mysql);
$results = array();
//Results container
while($row = @mysql_fetch_assoc($ans)){
	$results[]=$row;
}
$page = (isset($_GET['page'])) ? intval($_GET['page']) : 1;
//If the index 'page' is set within the array, return the integer 
//value of it, if not, just return 1(the first page)

Now, we need to figure out how many rows we want displayed per page. I usually like the number 6, so I'll assume that. Now, since the first iteration starts at 0 in an array, we have an issue. Our page starts at 1. So what do we do? We subtract 1 from the current page. Ok, but what if we're on page 2? well then we should technically start at iteration 6, since iterations 0-5 are taken care of on the first page. Well then we just have to multiple by the number of results we want per page(2-1 = 1*6 = 6). Now we just want the results up 6 results to be displayed. That means we add 6 to the beginning iteration and voila. That , now looks something like this:

$connection = mysql_connect("localhost", "User", "Password") or die(mysql_error());
//Connection to the database. Error out if the connection fails for 
//some reason(bad password, bad host, whatever the case is)
@mysql_select_db('database_name');
//Select the database
$sql = "SELECT * FROM Posts ORDER BY post_date DESC";
$ans= mysql_query($sql,$connection) or die(mysql_error());
//Error out if the query fails(bad syntax in mysql);
$results = array();
//Results container
while($row = @mysql_fetch_assoc($ans)){
	$results[]=$row;
}
$page = (isset($_GET['page'])) ? intval($_GET['page']) : 1;
//If the index 'page' is set within the array, return the integer 
//value of it, if not, just return 1(the first page)
$begin_iteration = ($page-1)*6;
$end_iteration = $begin_iteration+6;
//Voila, our beginning and ending iterations. Pretty simple

Next we just use a for() loop to get all of the iterations we want, checking first if the index given is an array. If it is, continue on with the loop until we run out of results. If not, break the loop to stop execution.

$connection = mysql_connect("localhost", "User", "Password") or die(mysql_error());
//Connection to the database. Error out if the connection fails for 
//some reason(bad password, bad host, whatever the case is)
@mysql_select_db('database_name');
//Select the database
$sql = "SELECT * FROM Posts ORDER BY post_date DESC";
$ans= mysql_query($sql,$connection) or die(mysql_error());
//Error out if the query fails(bad syntax in mysql);
$results = array();
//Results container
while($row = @mysql_fetch_assoc($ans)){
	$results[]=$row;
}
$page = (isset($_GET['page'])) ? intval($_GET['page']) : 1;
//If the index 'page' is set within the array, return the integer 
//value of it, if not, just return 1(the first page)
$begin_iteration = ($page-1)*6;
$end_iteration = $begin_iteration+6;
//Voila, our beginning and ending iterations. Pretty simple
for($i = $begin_iteration;$i<$end_iteration;$i++){
	if(@is_array($results[$i])){
		echo "Title:".$results[$i]['post_title']."<br />\n";
		echo "Contents:".$results[$i]['post_text']."<br />\n";
		//Echo the title and the contents. Replace this with whatever your code requires.
	}else{
		break;
		//if the index of $results[i] isn't defined, break the loop 
		//and continue with the rest of the page
	}

}

Voila. Displays all the page results in your database. However, i bet you're wondering "Well how do i get ppl to go to the next/previous pages and how do i know when to stop?" Well, simple answer really. We take the size of the array(and thusly how many results there are, no?) and divide it by how many submissions in a page we want, then we take the answer and run it through ceil, which basically rounds up to the next integer number. Then we just loop through the numbers with our pages(you can do whatever you may want, like create a next/ previous button, but that requires a bit more code). When added, our file now looks like this:

$connection = mysql_connect("localhost", "User", "Password") or die(mysql_error());
//Connection to the database. Error out if the connection fails for 
//some reason(bad password, bad host, whatever the case is)
@mysql_select_db('database_name');
//Select the database
$sql = "SELECT * FROM Posts ORDER BY post_date DESC";
$ans= mysql_query($sql,$connection) or die(mysql_error());
//Error out if the query fails(bad syntax in mysql);
$results = array();
//Results container
while($row = @mysql_fetch_assoc($ans)){
	$results[]=$row;
}
$page = (isset($_GET['page'])) ? intval($_GET['page']) : 1;
//If the index 'page' is set within the array, return the integer 
//value of it, if not, just return 1(the first page)
$begin_iteration = ($page-1)*6;
$end_iteration = $begin_iteration+6;
//Voila, our beginning and ending iterations. Pretty simple

Next we just use a for() loop to get all of the iterations we want, checking first if the index given is an array. If it is, continue on with the loop until we run out of results. If not, break the loop to stop execution.

$connection = mysql_connect("localhost", "User", "Password") or die(mysql_error());
//Connection to the database. Error out if the connection fails for 
//some reason(bad password, bad host, whatever the case is)
@mysql_select_db('database_name');
//Select the database
$sql = "SELECT * FROM Posts ORDER BY post_date DESC";
$ans= mysql_query($sql,$connection) or die(mysql_error());
//Error out if the query fails(bad syntax in mysql);
$results = array();
//Results container
while($row = @mysql_fetch_assoc($ans)){
	$results[]=$row;
}
$page = (isset($_GET['page'])) ? intval($_GET['page']) : 1;
//If the index 'page' is set within the array, return the integer 
//value of it, if not, just return 1(the first page)
$begin_iteration = ($page-1)*6;
$end_iteration = $begin_iteration+6;
//Voila, our beginning and ending iterations. Pretty simple
for($i = $begin_iteration;$i<$end_iteration;$i++){
	if(@is_array($results[$i])){
		echo "Title:".$results[$i]['post_title']."<br />\n";
		echo "Contents:".$results[$i]['post_text']."<br />\n";
		//Echo the title and the contents. Replace this with whatever your code requires.
	}else{
		break;
		//if the index of $results[i] isn't defined, break the loop and 
		//continue with the rest of the page
	}

}

$pages = ceil(sizeof($results) / 6);
//Number of pages
for($i = 1;$i<$pages;$i++) {
	"<a href=\"yourpage.php?page=$i\">$i</a>  "
}

And voila, that is more or less how I do pagination. One query, one array, a bit of math and common sense, and tada. I'm not saying there aren't other ways, but i prefer this one. Hope it's useful

This tutorial was written by Jhecht Falcon.

Follow Scriptplayground on Twitter (@scriptplay)

Comments: Simple Pagination Using PHP

 Morty Schmoopy  Thu Jun 12, 2008 2:22 pm  
If you are passing things through a query string, you can use the LIMIT parameter to control a fresh query per page instead of doing everything in an array.
 mkeefe  Thu Jun 12, 2008 2:35 pm  
I agree, I will inquire with the original author about a revamped version. Thanks for your input.
 RudiBela Web Design Malta  Mon Jun 16, 2008 10:14 am  
I agree worth Morty.

One other thing also hurts my left eye, which is the "SELCT * FROM.." part of the query. You should avoid using * in your web applications by any means. In any applications. Use columns by their names so that if one day the database schema changes, you will find eventually problematic columns easily.

Otherwise, the article is quite good.
 Unreal Media  Thu Nov 27, 2008 4:52 pm  
Simple? This is really complicated lol...
 Tamara  Fri Jan 23, 2009 12:12 pm  
For me the for loop at the end wasn't working.
I had to ad a echo and also change it in <=

 amit lewis  Sun Jan 25, 2009 2:05 am  
yes, just like tamara the loop didn't work for me either. I had to echo as well, but that left the last page out. the <= did work wonders though.

So for those who didn't get it yet. Here's explaining the solution better.

$pages = ceil(sizeof($results) / 6);
//Number of pages
for($i = 1;$i<=$pages;$i++)
echo "$i ";

That will work 100%
 amit lewis  Sun Jan 25, 2009 2:10 am  
the echo string i posted above for some reason didn't show in correctly, since oops html is not allowed. So let me correct it once again.

$pages = ceil(sizeof($results) / 6);
//Number of pages
for($i = 1;$i<=$pages;$i++)
echo "just use the a href line, while changing it to the url u desire."

Last but not the least, don't use the } at the end.
 Guest  Sat Mar 21, 2009 8:26 am  
This method looks easier on the mysql server. nice. sort of thing I like to do. cheers.
 renu  Tue May 5, 2009 2:07 am  
bit simple..thx a lot
 Jhecht  Tue May 12, 2009 3:26 am  
Forgot I had submitted this. Been awhile. I'll revamp this article since I think I've recently figured an easier way to do things. Will update later
 mkeefe  Mon May 25, 2009 4:10 pm  
@Jhecht - Sounds good, once its done we can post it.
 ahmed  Thu Aug 6, 2009 10:47 am  
hi everybody
what if pages are about 2000, is there any more smart way of listing those pages ?

thanks
 Rafael  Thu Oct 22, 2009 2:26 pm  
Works perfectly! Just missed "echo" and ";" in list number of pages....
 Rafael  Thu Oct 22, 2009 3:43 pm  
Hi
Not this listing the last page!

Replaced the number:

$pages = ceil(sizeof($results) / 6);

For:

($pages = ceil(sizeof($results) / 5);


Thanks

 Jasmin  Tue Dec 22, 2009 12:45 pm  
Got the pagination showing but when clicking to next page it shows no results ...any Ideas?
 Chetan  Wed May 19, 2010 7:26 pm  
Hi,
I am new to php I tried your script everything is working fine but if I sort the mysql data in DESC order my very 1st comment doesn't appear on last page and last page starts from 2nd comment. Any suggestion?
 mkeefe  Wed Jun 30, 2010 11:29 am  
@Jasmin - Any errors?
@Chetan - Have you print_r your results to ensure they are all there? If not I would start with that.
 cihip  Sat Jul 24, 2010 8:50 am  
Simple Pagination Using PHP for thanks..
 GTPixels  Wed Nov 10, 2010 7:54 am  
the solution is the following code:

$pages = ceil(sizeof($results) / 6);
//Number of pages
for($i = 1;$i<$pages;$i++) {
echo "$i ";
}
 exstill  Wed Dec 1, 2010 1:03 pm  
last page is not showing.. here's what i did with the last for loop.
$pages = ceil(sizeof($results) / 5); //Number of pages for($i = 1;$i<$pages;$i++) {
echo "". $i ."";
}

help me please.
 techie  Mon Jun 6, 2011 10:19 pm  
Good work
 Helen Neely  Thu Sep 29, 2011 10:30 am  
Thanks for this post, but I can't seem to get it working as the code is broken up into very many parts. Is there a place one can download a complete file?

Thanks again.
Add a comment
The ability to add new comments has been disabled in this archived version.

Loading
Main | Tutorials | Articles