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.2 Creating the HTML and PHP to display the Results
Switch back into Design view in Dreamweaver MX, and add
a table to the page, as shown in figure 1 below.
Figure 1 - Example
Table Layout
This is an ordinary HTML table with 2 rows and 3 columns
to display the Title of the Tutorial, the Author's name and the Date the tutorial
was published.
Note that x's have been
used to mark where the dynamic data will go, and this makes it easier to find
the correct place in Code View.
First, were going to make the table row where the records
will be displayed a repeat region, so that a table row is created for every
record in the recordset.
Select the table in Design View by clicking on an outer
edge of the table so it becomes selected as shown in Figure 1 above. Next,
switch into Code View, and you'll see the HTML code for the table has been
highlighted and will look similar to that shown below.
<table
width="600" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="300">Tutorial Title </td>
<td width="200">Author</td>
<td width="100">Date</td>
</tr>
<tr>
<td width="300">xxxxxxx</td>
<td width="200>xxxxxxx</td>
<td width="100">xxxxxxx</td>
</tr>
</table>
Code Block 3 -
HTML Code for the results table
If you look at the code above you can see that the code
for the second row above is highlighted in blue. To make this row repeat once
for each record in the recordset stored in $recordset
we just need to wrap a PHP foreach() loop round the HTML code, as shown below (highlighted in green).
<table
width="600" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="300">Tutorial Title </td>
<td width="200">Author</td>
<td width="100">Date</td>
</tr>
<?php foreach($recordset as $record){ ?>
<tr>
<td width="300">xxxxxxx</td>
<td width="200>xxxxxxx</td>
<td width="100">xxxxxxx</td>
</tr>
<?php } ?>
</table>
Code Block 4 -
HTML Code for the results table with Repeat Region
With the foreach() loop in place the HTML for a table row will be sent to the browser
once for each record in the array $recordset. Each time round the loop, we can refer to the current record using
the variable $record.
Our next job is to replace the x's that act as placeholders with the dynamic data from record
in $record.
Change Code Block 4 as shown below.
<table
width="600" border="0" cellspacing="2" cellpadding="2">
<tr>
<td width="300" class="tableHeading">Article Title
</td>
<td width="200" class="tableHeading">Author</td>
<td width="100" class="tableHeading">Date</td>
</tr>
<?php foreach($recordset as $record){ ?>
<tr>
<td width="300" class="tableData"><?php
echo $record['title']; ?></td>
<td width="200" class="tableData"><?php
echo $record['author']; ?></td>
<td width="100" class="tableData"><?php
echo date("d/m/Y",$record['dateTime']); ?></td>
</tr>
<?php } ?>
</table>
Code Block 5 -
HTML Code for the results table with Repeat Region & Dynamic Data
In Code Block 5 above, you can see that we use the PHP echo() command to output the data in each field to the screen. Because the
database record is in the form of an array, we use the fieldname so that PHP
knows which field we're referring to e.g. $record['title'] to access the title
field in the current record.
For the datetime field, because
the date is output as a UNIX timestamp, we can use the PHP date() command to format it for us, so that
it outputs in the form of dd/mm/yyyy.
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.
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
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.