I found somewhere this code but I didn't remember now to give you the link.
Pagination: Easy as PREV 1 2 3 NEXT (REVISED)
Alright. You've had enough, haven't you? Pouring countless hours into trying to figure out pagination. Yep, I feel your pain. Don't fret though, this tutorial will get you through it without too many bruises--it's easier than you think.
For those of you who don't know, pagination (in the Web world) is usually dividing a large list of items into several pages, and then providing a nifty link for it. You usually see pagination link displayed like this:
PREV 1 2 3 4 5 NEXT
Now pagination could be done easily with plain HTML: Just add X amount of items to a page, make a new page, add more items, and finally inter-link the pages. As easy as this seems, this method will start to become a burden once you have over 100+ items you want to list. The manual labor of adding the code would be simply mundane, not to mention the logistics of inter-linking all the pages--imagine if you had over 10,000 items, or if your list was updated regularly, or if it was sorted! You'd have to search through many pages to find out where your new entry fits. As you can see, static HTML pagination for large numbers, dynamic lists, or sorted lists can become a major pain in the... err... processor.
Despite the troubles of static pagination with HTML, dynamic pagination with PHP can be accomplished quite easy. Note, however, that you will need dynamic content to use this method of pagination. The dynamic content used in this tutorial is an imaginary MySQL table, but the concept can be used for other forms of dynamic content.
Now let's get started on the NEXT page (no pun intended... ok, ok, it was intended).
Here's were we start the code (the fun part). I'm going to split the code into sections so I can explain it easier. I will display the code in full--with minimal comments--at the end.
For this pagination example we're going to be listing all the users in our database. Since we're using a database, this first block of code is going to connect to our MySQL database.
<?php
// The first function connects to your database, the second selects a database
@mysql_connect($localhost, $user, $password) or die("ERROR--CAN'T CONNECT TO SERVER");
@mysql_select_db("database") or die("ERROR--CAN'T CONNECT TO DB");
/* Tip: The @ in front of these two functions will hide the username and
password if there is an error. The die() function will kill the script and show
an error statement if something goes wrong with the connect or select functions.
A mysql_connect() error usually means your username/password are wrong, and a
mysql_select_db() error usually means the database doesn't exist. */
That was easy, wasn't it? Yep. Next, we're going to tell the script: how many items we want displayed per page with the $limit variable (we want 25 per page); and we're going to count the amount of items with a simple MySQL query.
$limit = 25;
// Sets how many results shown per page
$query_count = "SELECT count(*) FROM table";
// Sets what we want to pull from the database
// count(*) is better for large databases (thanks Greg!)
$result_count = mysql_query($query_count);
// Pulls what we want from the database
$totalrows = mysql_num_rows($result_count);
// This counts the number of users
Moving along. The first two lines in this next block of code make up an if statement. It checks to see what page we're on; if it doesn't find a page (first load of this script) it automatically sets the page number to the first (1).
Then, the $limitvalue variable is used to find where to start pulling the data from when we use our LIMIT statement in the MySQL query. Basically it starts from where we last left off. So if we're on page one, it'll start with the first entry; if we're on page 2, it'll start with the 26th entry. The MySQL query in this block of code pulls all the data we need to display.
if(empty($page)){ // Checks if the $page variable is empty (not set)
$page = 1; // If it is empty, we're on page 1
}
$limitvalue = $page * $limit - ($limit);
// Ex: (2 * 25) - 25 = 25 <- data starts at 25
$query = "SELECT * FROM table LIMIT $limitvalue, $limit";
$result = mysql_query($query) or die("Error: " . mysql_error());
// Selects all the data from table.
// mysql_error() will print an error if one occurs.
/* Tip: The MySQL LIMIT value syntax is as follows:
LIMIT $row_to_start_at, $how_many_rows_to_return
*/
Phew! On the next page, we'll print the results on the screen.
This next block o' code is very useful. It's not vital for the script to work, but it helps people viewing your page. All it does is print a notice to the screen if there is no data to return.
if(mysql_num_rows($result) == 0){
echo("Nothing to Display!");
}
// This reads the number of rows returned
// from the result above.
/* Tip: You could probably use if($totalrows == 0) for the if statement;
however, reading the actual $result from the data you'll be printing to the
screen is more accurate, and is a surefire way of preventing certain errors. */
A lot of people usually wonder how to alternate background colors for their tables when they return a list of items. In this block of code we'll be setting a variable that will help us alternate the colors.
We also are going to start a loop that will display all data. For this example, we'll be printing all the usernames and their user ID number. We're going to start a table OUTSIDE of this loop that way all the data will be INSIDE the table.
$bgcolor = "#E0E0E0"; // light gray
/* Sets up one of the background colors. The color stated here will be
displayed second */
echo("<table>");
// Just a simple table
while($row = mysql_fetch_array($result)){
/* This starts the loop (a while loop). What this does is returns a row of data
to the $row array. Each time the loop restarts, the next row of data is used.
So, each pass of the loop returns a different row of data. */
// Start The Background Check
if($bgcolor == "#E0E0E0"){
$bgcolor = "#FFFFFF";
}else{
$bgcolor = "#E0E0E0";
}
/* Tip: The color you want to appear first on the list should be where the
#FFFFFF is listed. What this script does is checks to see if #E0E0E0 was used
last; if it was, then it'll use #FFFFFF. All you have to do is replace the
colors of your choice. */
Now we're reaThe output for this example is going to be very simple. Remember, you can change it to whatever you want, it's really straight foward. After we enter the output we want, we'll close the loop.
echo("<tr bgcolor=".$bgcolor.">n<td>");
// Here we start table row & table data
// Make sure your bgcolor is the $bgcolor variable
echo($row["users"]);
/* Tip: This is how we add the users field from our row. You can use any field
from your row: all you do is include the field's name inbetween the array
brackets. Ex: $row["field_name_here"] */
echo("</td>n<td>");
// Here we end the one section of table data, and start another.
echo($row["usersID"]);
// Prints the usersID field
echo("</td>n</tr>");
// Here we end the table data & table row
} /* This closes the loop. Anything after this bracket will display after the
data you've pulled from the database. */
echo("</table>");
/* While we're at it, let's close the table tag--we don't want other data
inside this table */
Now we come to the hardest part of this tutorial. I saved the best for last! These next blocks of code will display the links for the pagination.
First, we're going to setup the PREV link. If we're on page one, we won't display PREV as a link, just text.
if($page != 1){
$pageprev = $page--;
// Fancy way of subtracting 1 from $page
echo("<a href=\"$PHP_SELF&page=$pageprev\">PREV".$limit."</a> ");
/* Tip: It is a good idea NOT to use $PHP_SELF in this link. It may work,
but to be 99.9% sure that it will, be sure to use the actual name of the file
this script will be running on. Also, the adds a space to the end of
PREV, and gives some room between the numbers. */
}else
echo("PREV".$limit." ");
// If we're on page 1, PREV is not a link
I hope you're still with me here, and I hope you're holding on strong, because the code is about to get rough. Let's go to the next page.
dy to add how we want to output all the data. We'll do that on the next page.
$numofpages = $totalrows / $limit;
/* We divide our total amount of rows (for example 102) by the limit (25). This
will yield 4.08, which we can round down to 4. In the next few lines, we'll
create 4 pages, and then check to see if we have extra rows remaining for a 5th
page. */
for($i = 1; $i <= $numofpages; $i++){
/* This for loop will add 1 to $i at the end of each pass until $i is greater
than $numofpages (4.08). */
if($i == $page){
echo($i." ");
}else{
echo("<a href=\"$PHP_SELF&page=$i\">$i</a> ");
}
/* This if statement will not make the current page number available in
link form. It will, however, make all other pages available in link form. */
} // This ends the for loop
Depending on what your $limit is, and how many rows your database returns, you may or may not have rows with no page after the last block of code. This next block of code will fix that by checking for remaining rows.
if(($totalrows % $limit) != 0){
/* The above statement is the key to knowing if there are remainders, and it's
all because of the %. In PHP, C++, and other languages, the % is known as a
Modulus. It returns the remainder after dividing two numbers. If there is no
remainder, it returns zero. In our example, it will return 0.8 */
if($i == $page){
echo($i." ");
}else{
echo("<a href=\"$PHP_SELF&page=$i\">$i</a> ");
}
/* This is the exact statement that turns pages into link form that is used
above */
} // Ends the if statement
Now all we have left to do is add the NEXT button, and then we're done!
if(($totalrows - ($limit * $page)) > 0){
/* This statement checks to see if there are more rows remaining, meaning there
are pages in front of the current one. */
$pagenext = $page++;
// Fancy way of adding 1 to page
echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>");
/* Since there are pages remaining, this outputs NEXT in link form. */
}else{
echo("NEXT".$limit);
/* If we're on the last page possible, NEXT will NOT be displayed in link
form. */
}
mysql_free_result($result);
/* This line is not required, since MySQL will free the result after all
scripts have finished executing; however, it's a nice little backup. */
// The next line tells the server to stop parsing PHP
?>
There you have it! On the next page I will display the code in full without comments; and I'll include some final thoughts.
Here's the full code:
<?php
@mysql_connect($localhost, $user, $password) or die("ERROR--CAN'T CONNECT TO SERVER");
@mysql_select_db($database) or die("ERROR--CAN'T CONNECT TO DB");
$limit = 25;
$query_count = "SELECT count(*) FROM table";
$result_count = mysql_query($query_count);
$totalrows = mysql_num_rows($result_count);
if(empty($page)){
$page = 1;
}
$limitvalue = $page * $limit - ($limit);
$query = "SELECT * FROM table LIMIT $limitvalue, $limit";
$result = mysql_query($query) or die("Error: " . mysql_error());
if(mysql_num_rows($result) == 0){
echo("Nothing to Display!");
}
$bgcolor = "#E0E0E0"; // light gray
echo("<table>");
while($row = mysql_fetch_array($result)){
if ($bgcolor == "#E0E0E0"){
$bgcolor = "#FFFFFF";
}else{
$bgcolor = "#E0E0E0";
}
echo("<tr bgcolor=".$bgcolor.">n<td>");
echo($row["users"]);
echo("</td>n<td>");
echo($row["usersID"]);
echo("</td>n</tr>");
}
echo("</table>");
if($page != 1){
$pageprev = $page--;
echo("<a href=\"$PHP_SELF&page=$pageprev\">PREV".$limit."</a> ");
}else{
echo("PREV".$limit." ");
}
$numofpages = $totalrows / $limit;
for($i = 1; $i <= $numofpages; $i++){
if($i == $page){
echo($i." ");
}else{
echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");
}
}
if(($totalrows % $limit) != 0){
if($i == $page){
echo($i." ");
}else{
echo("<a href=\"$PHP_SELF?page=$i\">$i</a> ");
}
}
if(($totalrows - ($limit * $page)) > 0){
$pagenext = $page++;
echo("<a href=\"$PHP_SELF?page=$pagenext\">NEXT".$limit."</a>");
}else{
echo("NEXT".$limit);
}
mysql_free_result($result);
?>
I hope this tutorials helps you in your pagination process, whether you copy the whole damn thing or just use bits and pieces of it. Don't forget to replace the MySQL queries with queries of your own that relate to your own database.
Until next time,
<?php exit(); \ The Reverend ?>