Imagine that you have a recordset containing 100 records, for example. Usually you wouldn't want to display all 100 records at once, as it's far too much information for a web site visitor to take in at once, and it can also make your web pages slow to load. Instead, it's much more desirable to be able to show the user 10 records at a time for example, and let them move back and forth between pages. A perfect example of this is a search engine such as Google, although it can be used on any web site that uses dynamic data.

Advertisement DMXzone Paginator PHP

Add the coolest page navigation to your site and make browsing through large lists of items or tables easy and quick. Choose from 22 different styles, used by many sites such as Digg, Yahoo, Flickr and much more, to fit perfectly with your design and display the total number of items.

All paging styles are fully CSS based, so you can always fine tune the colors and borders to your site design.

 

3.3 Testing the Page

At this stage, you can now test the page on your server, and when the page is run you should see output similar to that in Figure 2 below.

You will see that all 17 records in the example_date table are displayed, and are ordered in descending date order.

Figure 2 - Example output from show_all_records.php

Now that we have a page that reads and displays records from the database, we're next going to adapt the page and introduce a recordset paging system.

Step 4 - Adding Recordset Paging

As you can see from Figure 2 above, currently all the records in the recordset are shown at once. We're now going to adapt the system to show the results in "pages", with each page showing 5 records at a time. We'll also create a navigation bar so that the user can move backwards and forwards between the pages of results.

Before we start creating the code, we're going to look at the MySQL LIMIT command which makes creating this sort of system much easier.

4.1 - The MySQL LIMIT command

If we take a look at a standard SQL SELECT statement, for example:

SELECT * FROM tableName

This would return all records from tableName. In the case of our example_data table, as we've seen it returns all 17 records. In a recordset paging system, it would be wasteful of server resources to read in all records as we only need a small group of records, in the case of this example, 5 records at a time.

MySQL has a special SQL command, LIMIT, which is ideal for use with recordset paging. The command uses the following format:

SELECT * FROM tableName LIMIT offset, number of rows

The offset parameter starts from 0, and tells MySQL at which record it should start returning results. The number of rows parameter tells MySQL how many records to return.

As an example, we'll take our example_data table which has 17 records, and we will read pages in 5 records at a time. Because there are 17 records, there will be 4 pages, and we can use the following SQL statements. Remember that offset starts at 0, so we need to subtract 1 from the record number.

Page	Records	SQL
1	0  4	SELECT * FROM tableName LIMIT 0,5
2	5  9	SELECT * FROM tableName LIMIT 5,5
3	10  14	SELECT * FROM tableName LIMIT 10,5
4	15  16	SELECT * FROM tableName LIMIT 15,5

Using LIMIT means that the only records that are returned from the result of the query are the ones we're actually going to use, which means that queries are faster and more efficient on the server resources.

We can now start adapting our existing code to use the MySQL LIMIT function.