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

Step 2 - Creating a Database Connection File

As always, we'll put the details needed to connect to the MySQL database in a separate include file, and then we'll add this file to each page that needs to access the database. This means in future if the database details change, they only need to be altered in the include file, rather than on every page which saves a lot of time and effort.

Open a new PHP page in Dreamweaver MX, and because were creating a PHP include file, switch into Code View and delete all the existing DMX generated code. Next, add the following block of code to the page, or download it as above.

<?php
// Database Connection Parameters
$dbHost = "localhost";
$dbUser = "your_username";
$dbPass = "your_password";
$dbName = "database_name";
?>

Code Block 1 - dbConnection.php Include File

Remember though, that you will need to edit the details in this file so that it contains your own MySQL username and password etc.

Save the file as dbConnection.php in a directory called includes.

Now that we have our database connection file, we can move on and display the data. First, we'll look at how to display the standard recordset with all records, and then we'll see how to adapt the code for recordset paging.

Step 3 - Creating Code to Create and Display a Recordset

First, we're going to create the standard PHP Code to read records from a database. Create a new PHP page in Dreamweaver MX, as save it as show_all_records.php.

3.1 Creating the PHP code to read records from the Database

Switch into code view, and add the following block of code to the very top of the page, above the <html> tag.

<?php
// Include Database Connection File
require_once("C:/webserver/includes/dbConnection.php");
// Open Database Connection
$dbLink = mysql_connect($dbHost, $dbUser, $dbPass);
if (!$dbLink){ die ("Database: Couldn`t connect to mySQL Server"); }
mysql_select_db($dbName, $dbLink) or die ("Database: Couldn`t open Database");
// Build SQL query
$sql  = "SELECT UNIX_TIMESTAMP(date) as dateTime,id,title,author ";
$sql .= "FROM example_data ";
$sql .= "ORDER BY UNIX_TIMESTAMP(date) DESC";
// Read in Query Results
$dbResult = mysql_query($sql, $dbLink) or die ("MySQL Error: " . mysql_error() );
$numRecords = mysql_num_rows($dbResult);
$recordset = "";
for($i=0;$i<$numRecords;$i++){
  $recordset[] = mysql_fetch_assoc($dbResult);
}  
// Close Database Connection 
mysql_close($dbLink);
?>

Code Block 2 - PHP Code to read records into a Recordset

As usual, we'll look at each block of code from Code Block 2 above individually.

// Include Database Connection File
require_once("C:/webserver/includes/dbConnection.php");

First, we add the dbConnection.php include file that we created earlier, which contains the database connection parameters.

// Open Database Connection
$dbLink = mysql_connect($dbHost, $dbUser, $dbPass);
if (!$dbLink){ die ("Database: Couldn`t connect to mySQL Server"); }
mysql_select_db($dbName, $dbLink) or die ("Database: Couldn`t open Database");

We then open a connection to the MySQL server using the PHP mysql_connect() command. We check that the connection was successful, and if it wasn't, we stop the code using the PHP die() command, as there's no point in continuing if we can't connect to the database.

Next we tell MySQL to change to the database that we want to use, with the PHP mysql_select_db() command, and again stop the code if the command fails.

// Build SQL query
$sql  = "SELECT UNIX_TIMESTAMP(date) as dateTime,id,title,author ";
$sql .= "FROM example_data ";
$sql .= "ORDER BY UNIX_TIMESTAMP(date) DESC";

The next step is to create the SQL query to read records from the database.  Note that we use the MySQL command UNIX_TIMESTAMP() passing it the datetime field called date. This changes the format of the data in the field from the datetime format yy-mm-dd hh:mm:ss to a UNIX timestamp which is an ordinary number (and represents the number of seconds since the Linux epoch dates). We can now order the data in descending order using this number, which means that the data is now sorted in order of date and then time. Turning the datetime field into a UNIX timestamp also means it can be used directly with other PHP time and date commands, and is much easier to manipulate.

 // Read in Query Results
$dbResult = mysql_query($sql, $dbLink) or die ("MySQL Error: " . mysql_error() );
$numRecords = mysql_num_rows($dbResult);
$recordset = "";
for($i=0;$i<$numRecords;$i++){
  $recordset[] = mysql_fetch_assoc($dbResult);
}  

We then run the SQL query we created using the PHP mysql_query() command, and again stop the code if the command fails. The mysql_num_rows() command is then used so that we know how many records have been returned by the query. Finally we use a PHP for loop which runs once for each record returned by the query, and reads a record at a time using the mysql_fetch_assoc() command and places it into an array called $recordset.

// Close Database Connection 
mysql_close($dbLink);

The final stage is to close the MySQL database connection as it's no longer required, which we do by using the PHP mysql_close() command.

Now that we have all the records from the database in an array, we next need to add a mixture of HTML and PHP to display these records on the web page.

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.