Do you have a lot of MySQL results that are displayed on a single page? These results could consist of virtually anything to include news articles, member listings, products or whatever you want. Scrolling down through tons of results can be a real drag. In this tutorial we're going to cover how to create some very simple code to create page numbers and limit the number of results per page that you have. If you are curious to what page numbering is, take a look at Figure 1.1, or look at the bottom of these tutorial pages.

Preparing a Test Database

For our learning purposes, we're going to create a test database or a test table in your database and populate it with 102 results to perform our tests with.

Let's begin by using your favorite MySQL management utility and create a table in your database and name it "pages". If you are looking for a good MySQL management tool, check out SQLyog.

We'll set this table up with the following columns:

Here's the SQL code for this table, in case you prefer to create your table this way:

Now that our table is created, let's populate it by using a custom PHP script to fill in this information for us. Take a look at this next script:

The first portion of this script includes a file named "db.php" which is a simple file to include globally throughout your website for your MySQL connection. If you do not understand what this file should contain, check out this code example:

PHP Example: (!)
<?
// Create database connection and select database
mysql_select_db('tutorial', mysql_pconnect('localhost',

'root','password')) or die (mysql_error());
?>

The next portion of this script is a simple for loop that will insert 102 rows into the database. Each "title" field will contain an incremented title such as: Item 1, Item 2, Item 3 and so on.

After you run the script in your web browser and verify that your tables were populated with 102 rows, let's move on to the pagination code!

Page Numbering Code

The next file that we are going to build is named "pages.php". This file contains the page numbering code and it also contains your results per page formatted to your desires. Let's take a look at it now!

PHP Example: (!)
<?php

// Database Connection
include 'db.php';

// If current page number, use it
// if not, set one!

if(!isset($_GET['page'])){
    $page = 1;
} else {
    $page = $_GET['page'];
}

// Define the number of results per page
$max_results = 10;

// Figure out the limit for the query based
// on the current page number.
$from = (($page * $max_results) - $max_results); 

// Perform MySQL query on only the current page 

number's results

$sql = mysql_query("SELECT * FROM pages LIMIT $from, 

$max_results");

while($row = mysql_fetch_array($sql)){
    // Build your formatted results here.
    echo $row['title']."<br />";
}

// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT COUNT(*) 

as Num FROM pages"),0);

// Figure out the total number of pages. Always round up 

using ceil()
$total_pages = ceil($total_results / $max_results);

// Build Page Number Hyperlinks
echo "<center>Select a Page<br />";

// Build Previous Link
if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."?page=$prev\">

<<Previous</a> ";
}

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF'].

"?page=$i\">$i</a> ";
    }
}

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF'].

"?page=$next\">Next>></a>";
}
echo "</center>";
?>

Alright, it looks like I have some explaining to do! Let's break the code down piece by piece. By the time you are done with this tutorial, you'll be a pro!

The first portion of our code example starts the PHP engine and includes our db.php file for the database connection.

Next, we are going to determine if the $page varible (the actual page number being displayed) is set, if it is not, we'll set it to "1".

Now that we have our page number figured out, we'll go ahead and define some limits to how many results we are going to display per page. In this next example, we are going to display 10 results per page. This number is very important to set because we are going to use it in mulitple places.

After we have our maximum results per page defined ($max_results), we are going to figure out which offset to use in the MySQL SELECT query for the LIMIT clause.

The previous code example defines the value of $from by taking the current page number ($page) and multiplying it by the maximum number of results ($max_results) and then subtracting the value of $max_results from the product of $page * $max_results. This sounds kind of confusing, but let's take a look at it like this:

If we were on page 1 and the maximum results were 10. The number of $from would be 10 before we subtract the $max_results from it.

This number is only used in the LIMIT clause of our query, so you have to understand how that LIMIT clause works. It works by LIMIT FROM, OFFSET. So, in our case, on the first page, we want to LIMIT 0, 10 and the second page, we want LIMIT 10, 10 and so on. Trust me, it works!

Our next task is to perform the MySQL query since we have our LIMIT clause values figured out from the previous code examples. In this query, we are going to LIMIT $from, $max_results which is explained in the previous paragraph.

PHP Example: (!)
// Perform MySQL query on only the current page 

number's results

$sql = mysql_query("SELECT * FROM pages LIMIT $from, 

$max_results");

Next, we are going to use a while loop to loop through the results from the mysql_fetch_array. This is where you want to format your results with the applicable information for your query.

If you would throw a close tage (?>) on this code right now and run it your browser, you should see the first 10 results displayed formatted to your liking. However, we need to build the page numbering code!

We are going to have to figure out how many rows are in the database. Using mysql_result and the COUNT function in MySQL, we can perform a very efficient query on the table to figure out the rows. The result of $total_results would be the number of rows found in your query.

PHP Example: (!)
// Figure out the total number of results in DB:
$total_results = mysql_result(mysql_query("SELECT 

COUNT(*) as Num FROM pages"),0);

Now we need to figure out how many pages are going to be displayed by our page numbering code. In this next code example, we will be using the $total_results and dividing it by $max_results to get a number. However, you have to think logically. If you have 101 results in your database, the product of this multiplication problem would be 10.1. So, let's round this number upwards using the ceil() function to make the portion from our quotient from our division problem would be 11 in this case.

PHP Example: (!)

// Figure out the total number of pages. Always 

round up using ceil()
$total_pages = ceil($total_results / $max_results);

Ok, now we have a total number of pages to work with. Let's display the words "Select a Page" above the page numbers:

Now, we are going to build a link that says "<< Previous" if we are on any page besides the first page. We do this by using an IF statement to figure out if the current page is greater than 1. If it is, then we will display the link. We also create a $prev value which is the current page minus 1 to take the user to the previous page number in the sequence.

PHP Example: (!)

// Build Previous Link
if($page > 1){
    $prev = ($page - 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."

?page=$prev\"><<Previous</a> ";
}

Now the bulk of it. We are going to use another one of those crazy for loops that will create all of our page numbers and hyperlinks for us. In this for loop, we are basically setting the rules that would read something like this:

While we are looping through this for loop, we are going to determine if $page is equal to the value of $i in the current looping sequence. If $page is equal to $i, then we are not going to display a hyperlink, but we will display the number of the page, symbolizing what page we are on. If $page is not equal to $i, we are going to create a hyperlink to get to those pages. This is all achieved through the IF portion of the IF ELSE statement below.

PHP Example: (!)

for($i = 1; $i <= $total_pages; $i++){
    if(($page) == $i){
        echo "$i ";
        } else {
            echo "<a href=\"".$_SERVER['PHP_SELF']."

?page=$i\">$i</a> ";
    }
}

After our page numbers are all generated, we will create a "Next >>" page link which will take the user to the next page in the sequence, from the one they are currently viewing.

PHP Example: (!)

// Build Next Link
if($page < $total_pages){
    $next = ($page + 1);
    echo "<a href=\"".$_SERVER['PHP_SELF']."

?page=$next\">Next>></a>";
}

Let's close up our HTML and shut the PHP engine off by issuing the close tag. We're done!

Test it out!

Give your script a run and see if everything works. It should unless you goofed something up!

As you see, pagination should not be that hard. It just takes some brain power to plan the different elemenst involved in using it. This tutorial was written to keep it as simple as possible. In the future, we'll be writing a tutorial to cover the page numbering of extremely large results that will be able to chop out the middle portion of the page numbers so that you do not have a page number sequence of 30 or 40 page links long!

 Credit: http://phpfreaks.com/

---