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.