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

Page	offset	results
1	0	1 - 25
2	25	26 - 50
3	50	51 - 75
4	75	76 - 100
5	100	101 - 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> ";
}

?>