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
4.2 Adapting the existing code
Open show_all_records.php and save it as recordset_paging.php. We will then adapt the code on this page to add in recordset paging.
Switch into Code View in Dreamweaver MX, and change the
code in the main PHP code before the <html> tag so that it matches that shown below in Code Block 6.
<?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 to find Total Number of Records
$sql = "SELECT id ";
$sql .= "FROM example_data ";
// Read in Total Number of Records
$dbResult = mysql_query($sql, $dbLink) or die ("MySQL
Error: " . mysql_error() );
$totalRecords = mysql_num_rows($dbResult);
// Calculate Number of Pages Required
$recordsPerPage = 5;
$totalPageNumber = ceil($totalRecords / $recordsPerPage);
// Check for Page Number
if(strlen($_GET['page']) < 1){
$currentPage = 1;
} else {
$currentPage = $_GET['page'];
}
// Create Offset Number
$offset = ($currentPage * $recordsPerPage) - $recordsPerPage;
// Create New SQL Query
$sql = "SELECT UNIX_TIMESTAMP(date) as dateTime,id,title,author
";
$sql .= "FROM example_data ";
$sql .= "ORDER BY UNIX_TIMESTAMP(date) DESC ";
$sql .= "LIMIT " . $offset . "," . $recordsPerPage;
//
Execute SQL Query and Read in Records
$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 6 -
Modified Code to implement Paging functionality
The new / modified code is highlighted in blue, and we'll
go through the code step by step.
// Build SQL query to find Total Number of Records
$sql = "SELECT id ";
$sql .= "FROM example_data ";
// Read in Total Number of Records
$dbResult = mysql_query($sql, $dbLink) or die ("MySQL
Error: " . mysql_error() );
$totalRecords = mysql_num_rows($dbResult);
Although were going to be reading only the records for the
current "page", in order to know how many pages will be required
we still need to know the total number of records in the table. To do this
we use a simple SQL query to select each record we're interested in (in this
case, every record). We then run the query, and use the PHP mysql_num_rows() command to find out how many records
were returned, and we store this value in the variable $totalRecords.
// Calculate Number of Pages Required
$recordsPerPage = 5;
$totalPageNumber = ceil($totalRecords / $recordsPerPage);
Next, we set the number of records per page we require,
in this case 5. We can then divide the total number of records by the number
of records per page to find the total number of pages required. Because this
value could be a decimal e.g. 1.5, we round it up to the next whole number
using the PHP ceil() command. As an example, we'll look at our case of 5 records per page,
and 17 records in the table:
$totalRecords / $recordsPerPage
= 17 / 5 = 3.4
ceil(3.4) = 4
So we need 4 pages to display all the data (although there
will only be 2 entries on the last page).
// Check for Page Number
if(strlen($_GET['page']) < 1){
$currentPage = 1;
} else {
$currentPage = $_GET['page'];
}
The next step is to find out which page of data we want
to view. In this case we check for a URL parameter called page, which will be sent when we create the navigation bar. If this parameter
isn't present in the URL, then we set $currentPage to 1, so that we will
see the first page of data.
// Create Offset Number
$offset = ($currentPage * $recordsPerPage) - $recordsPerPage;
Next, we need to create the offset number to use with the
MySQL LIMIT command. Because the offset must start
from 0, we get this number by multiplying the current page number by the number
of records per page, and then subtracting the number of records per page from
the result. For our data this will give the following offsets
| Page
Number |
Formula |
Offset |
Records
Returned |
| 1 |
(1 * 5) 5 |
0 |
0,1,2,3,4 |
| 2 |
(2 * 5) 5 |
5 |
5,6,7,8,9 |
| 3 |
(3 * 5) 5 |
10 |
10,11,12,13,14 |
| 4 |
(4 * 5) 5 |
15 |
15,16 |
// Create New SQL Query
$sql = "SELECT UNIX_TIMESTAMP(date) as dateTime,id,title,author
";
$sql .= "FROM example_data ";
$sql .= "ORDER BY UNIX_TIMESTAMP(date) DESC ";
$sql .= "LIMIT " . $offset . "," . $recordsPerPage;
Finally, we modify the original SQL query to include the
LIMIT command, passing it the record
offset and the number of records per page. The rest of the code then runs
this query and reads the relevant records into the $recordset array as before.
At this stage, you can test the page in your browser, and
this time you should see the first 5 records in the table only displayed,
as shown in Figure 3 below.
Figure 3 - New
page showing first page of 5 records
At the moment the system is working, but there's no way
for a user to move backwards and forwards through the pages of records. If
you manually change the URL and add the parameter ?page=2, you should see the second page of records, as shown in Figure 4 below.
Figure 4 - New
page showing second page of 5 records
Next, we're going to create a dynamic navigation bar that
will allow users to easily flip backwards and forwards through the pages.
Gareth 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.