PHP Tutorial - Building next/prev links R R




By: Richard (July 16, 2002)
Jan 27 2022

When you issue a query to retrieve records from your database, there is no telling how many records will match your search. Assuming you have a million records and 18,000 records match a particular query, it'll be crazy to try to dump all 18,000 records onto a user's screen. You can limit the search to, say 50 records, and display all 50 at once. However, you limit your users' options by doing this. The record he's looking for may be record number 51.

Our goal then is to return as many records as we want, and break the results up into pages. That way we will be able to display only a chosen number of records on each page. I use mysql database, so the code in this tutorial will show queries on a mysql database. Adapting the code to connect to a different database is a trivial task.

Also, all codes will be illustrated using PHP. If you use a different language for scripting, you will need to modify the examples presented here.

Assuming you already know how to connect to mysql using php, a typical query will be

SELECT * FROM table_name

This query will return the maximum number of records that match our query. In this case, every record in the database will be returned, since we're using a wildcard (*). How do we select only a certain number of records? E.g. how do we select only 25 records?

SELECT * FROM table_name LIMIT 25

When you add LIMIT xx to your query, mysql will search the database and stop searching as soon as it has xx records. So in our query SELECT * FROM table_name LIMIT 25, mysql will return only 25 records, no matter how many records match our query.

What if we want mysql to return 25 records, but start retrieving records from the 100th record? In other words, instead of returning record number 1 to number 25 as is the case above, we want mysql to return the 100th to 125th record.

SELECT * FROM table_name LIMIT 100,25

Take note of the term in red. It's telling mysql to skip records 0 to 99, and begin getting records from number 100. The term in red is known as offset.

I'm sure you've seen this picture thousands of times on websites

Previous 1 2 3 4 5 6 7 8 9 10 Next

The general format of our query is

SELECT * FROM table_name LIMIT offset,limit

Each time we query the database, we just need to tell mysql the offset, and the correct set of records will be returned starting from $offset. Assuming we have a php script called result.php, that contains a mysql query, we can pass $limit and $offset to the mysql query like this

http://www.yourdomain.com/result.php?offset=0&limit=25

This part of the url, ?offset=0&limit=25, is called a query string. The query string provides us with the values of $offset and $limit, and those values are available for result.php to use anytime. I hope you won't confuse mysql query with query string - they are two different things. Here is a typical mysql query:

SELECT * FROM table_name LIMIT $offset,$limit

This of course, translates into:

SELECT * FROM table_name LIMIT 0,25

The next time we query the database, we want our query to be

SELECT * FROM table_name LIMIT 25,25

After that the query should be

SELECT * FROM table_name LIMIT 50,25

And so forth. Therefore, our new offset is simply: current offset + limit.

$next_offset = $offset + $limit

With this understanding, we can create our query string as follows:

result.php?offset=$next_offset&limit=$limit

We must construct links to the next set of records and display them to look like this example, Previous 1 2 3 4 5 6 7 8 9 10 Next.

To do that, we need the total number of records that would have been returned without using LIMIT in our mysql query.

That is where the function FOUND_ROWS() comes in. It returns the number of rows that the query below would have returned, if it weren't restricted with LIMIT.

SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT $offset,$limit
SELECT FOUND_ROWS()

This new query is the same as the other queries we've looked at, except for the addition of SQL_CALC_FOUND_ROWS. SQL_CALC_FOUND_ROWS simply tells mysql to calculate the total number of results, without LIMIT clause. To obtain the result of this calculation, we use another select. The second SELECT will return a number indicating how many rows the first SELECT would have returned had it been written without the LIMIT clause. So, let's write our mysql query properly using php.

<?php
$server = "localhost";
$user = "yourUserName";
$pass = "yourPassword";
$connection = mysql_connect($server, $user, $pass) or die ("Unable to connect to database.");
$query = "SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT $offset,$limit";
$result = mysql_query($query) or die("Error in query");
$query = "SELECT FOUND_ROWS()";
$result_total = mysql_query($query) or die("Error in query");

$myrow = mysql_fetch_array($result);
$counter = mysql_result($result_total,0);
?>

$counter is the total number of records without the LIMIT clause. We can use this to calculate the total number of pages that'll be required to display all our records. Assuming $counter is 250 and $limit remains 25, we can calculate the total number of pages by dividing $counter by $limit.

$pages = ceil($counter / $limit);

If this division is a fraction, ceil() will round it up.

In our example above, it means $pages will be 250 / 25, which is equal to 10 pages. So we need 10 pages to show all our results, 25 records at a time. Our next/prev links will therefore look like this: Previous 1 2 3 4 5 6 7 8 9 10 Next

We can now display the numerical links, 1 2 3 4 5 6 7 8 9 10, The first page will have an offset of 0. If we're displaying 25 records per page, the table below shows what we're gonna have

Pageoffsetresults
101 - 25
22526 - 50
35051 - 75
47576 - 100
5100101 - 125
.........
You may notice in the table that the results overlap with the offset. Actually, there is no overlap if you think that php starts counting from from 0, not 1. So when offset is 0, we say that results 1 - 25 is displayed. But it is actually results 0 - 24.

So how do we translate the above table into php code? Let's do it with a FOR loop

for($i=1; $i <= $pages; $i++)
{
$next_offset = ($i-1)*$limit;
echo "<a href=\"result.php?offset=" . $next_offset;
echo "&limit=" . $limit . "\">" . $i . "</a> ";
}

This will give us hyperlinks to the pages as follows: 1 2 3 4 5 6 7 8 9 10 .

Why are we using the expression ($i-1)*$limit? Because the offset for each page is a multiple of $limit. So when $i is 1, $next_offset = (1-1)*25, which is 0. Of course, $offset for page 1 is 0. So when you click on a link for page 1, what you're clicking on is result.php?offset=0&limit=25.

What about page 2? Well, our offset should be 25, so let's see if we can arrive at that answer with our expression.

$next_offset = (2-1)*25, which equals 25. So yes we're correct again. When you click on a link for page 2, what you're clicking on is result.php?offset=25&limit=25.

Basically, that is all there is to creating next/prev links. The complete code is as follows:

<?php

$server = "localhost";
$user = "yourUserName";
$pass = "yourPassword";
$connection = mysql_connect($server, $user, $pass) or die ("Unable to connect to database.");
$limit = 25;

$query = "SELECT SQL_CALC_FOUND_ROWS * FROM table_name LIMIT $offset,$limit";
$result = mysql_query($query) or die("Error in query");
$query = "SELECT FOUND_ROWS()";
$result_total = mysql_query($query) or die("Error in query");

$counter = mysql_result($result_total,0);

$pages = ceil($counter / $limit);

for($i=1; $i <= $pages; $i++)
{
$next_offset = ($i-1)*$limit;
echo "<a href=\"result.php?offset=" . $next_offset . "&limit=" . $limit . "\">" . $i . "</a> ";
}

?>

If you're happy with just the numerical links 1 2 3 4 5 6 7 8 9 10, you can stop here. But if you're interested in building more functionality into your next/prev links, then continue reading.


The next addition is to include links to previous and next pages, like this Previous 1 2 3 4 5 6 7 8 9 10 Next.

To create Next link, we need $offset, which we already have as $next_offset. So let us expand our numerical links above

for($i=1; $i <= $pages; $i++)
{ echo "<a href=\"result.php?offset=" . $next_offset . "&limit=" . $limit . "\">" . $i . "</a> "; }
echo "<a href=\"result.php?offset=" . $next_offset . "&limit=" . $limit . "\">Next</a>";

This gives us

1 2 3 4 5 6 7 8 9 10 Next.

This will work as long as there is a next page to go to. But what happens when we get to the last page, and there is no other page to go to? In other words, what happens if the user is viewing page 10, in our example above? In that case, we still will have a link displayed for Next, but without a next page to go to. If the user clicks on that link, he will receive all kinds of mysql errors! We must find out when we've reached the last page, and therefore not display Next.

Think about it for a minute, if $next_offset - $counter > $limit, it means we've reached the last page. Let's expand our code to include this.

for($i=1; $i <= $pages; $i++)
{ echo "<a href=\"result.php?offset=" . $next_offset . "&limit=" . $limit . "\">" . $i . "</a> "; }
if( $next_offset - $counter < $limit ){
echo "<a href=\"result.php?offset=" . $next_offset . "&limit=" . $limit . "\">Next</a>"; }else{ echo ""; }

Next will only be shown if the page the user is viewing is not the last page.


Now we can add Previous.

So far we have

1 2 3 4 5 6 7 8 9 10 Next.

But we want

Previous 1 2 3 4 5 6 7 8 9 10 Next.

To get Previous to take a user back one page, we simply subtract $limit from $offset, and that will be our offset.

$prev_offset = $offset - $limit

We can expand our code to account for this.

echo " <a href=\"result.php?offset=" . $prev_offset . "&limit=" . $limit . "\">Previous</a>";
for($i=1; $i <= $pages; $i++)
{ echo "<a href=\"result.php?offset=" . $next_offset . "&limit=" . $limit . "\">" . $i . "</a> "; }
if( $next_offset - $counter < $limit ){
echo "<a href=\"result.php?offset=" . $next_offset . "&limit=" . $limit . "\">Next</a>"; }else{ echo ""; }

I'm sure you already see the problem with the code above. What happens if the user is currently viewing the first page of the results? It means there's no previous page to go to. In that case we need to know when we're on the first page, and not show Previous. This is very easy to do. If $offset == 0, it means we're on page 1. So let's throw that into our code also.

if( $offset > 0 ){
echo " <a href=\"result.php?offset=" . $offset - $limit . "&limit=" . $limit . "\">Previous</a>"; }

for($i=1; $i <= $pages; $i++)
{ echo "<a href=\"result.php?offset=" . $next_offset . "&limit=" . $limit . "\">" . $i . "</a> "; }
if( $next_offset - $counter < $limit ){
echo "<a href=\"result.php?offset=" . $next_offset . "&limit=" . $limit . "\">Next</a>"; }else{ echo ""; }


So there you have it! This should meet the needs of most people.

Here are things to think about:

  1. What happens if $counter is a huge number? Assuming a mysql query returns 50,000 records, it means if we display 25 records per page, we'll have a total of 2,000 links. Do we really want to display 2,000 numerical links on one page?

  2. How would you adapt the above code to display something like this

    Showing 50 - 75 of 200 records?

  3. How can you allow the user to select how many results to display per page? In other words, can the user change $limit? Think in terms of a dropdown html form as shown below.




Home |  Comments, suggestions?

Copyright © 2000-2002 Richie's Tutorials All rights reserved.