PHP Recordset Paging

In this tutorial, we first look at creating a page that displayed the results of a database query, and showing all results returned to the user.

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.

 


Overview

In this tutorial, we first look at creating a page that displayed the results of a database query, and showing all results returned to the user. We then look at the MySQL LIMIT command, which returns only certain records from the results obtained by a query, meaning you only get the records you are actually going to use, creating faster and more efficient queries. We use the LIMIT command to adapt our existing code so that it showed the results in pages of 5 records to a page.

We then create a dynamic navigation bar, which allows the users to quickly move backwards and forwards between pages. Finally we alter the code so that if results of a search are being shown, the search parameters are preserved as the user moves from page to page.

 

Table of Content:

  • What is Recordset Paging?
  • Step 1 - Creating some Example Data
  • Step 2 - Creating a Database Connection File
  • Step 3 - Creating Code to Create and Display a Recordset
    • 3.1 Creating the PHP code to read records from the Database
    • 3.2 Creating the HTML and PHP to display the Results
    • 3.3 Testing the Page
  • Step 4 - Adding Recordset Paging
    • 4.1 - The MySQL LIMIT command
    • 4.2 Adapting the existing code
    • 4.3 Creating a Dynamic Navigation Bar
    • 4.4 Testing the Complete Page
    • 4.5 - Using the Recordset Pages with the Results of a Search
  • Summary

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.

Gareth Downes-Powell

Gareth Downes-PowellGareth has a range of skills, covering many computer and internet related subjects. He is proficient in many different languages including ASP and PHP, and is responsible for the setup and maintenance of both Windows and Linux servers on a daily basis.


In his daily web development work he uses the complete range of Macromedia software, including Dreamweaver MX, Flash MX, Fireworks MX and Director to build a number of websites and applications. Gareth has a close relationship with Macromedia, and as a member of Team Macromedia Dreamweaver, he has worked closely in the development of Dreamweaver, and was a beta tester for Dreamweaver MX.


On a daily basis he provides support for users in the Macromedia forums, answering questions and providing help on a range of different web related subjects. He has also written a number of free and commercial extensions for Dreamweaver MX, to further extend its capabilities using its native JavaScript API’s or C++.


As a web host, Gareth has worked with a range of different servers and operating systems, with the Linux OS as his personal favourite. Most of his development work is done using a combination of Linux, Apache and MySQL and he has written extensively about setting up this type of system, and also running Apache and MySQL under Windows.

See All Postings From Gareth Downes-Powell >>

Reviews

nice tutorial

May 24, 2009 by ijaz khattak

dear sir

i am facing a prblem in my project, if u can help me in that i will mail it t you.

thanks 

You must me logged in to write a review.