Home arrow MySQL arrow Page 2 - Building A Dynamic MySQL Paging Class With PHP
MYSQL

Building A Dynamic MySQL Paging Class With PHP


One handy class that Joe always keeps in his PHP toolbox is a custom MySQL recordset paging class, which he developed about 5 months ago. In this article Joe describes how he created the MySQL recordset paging class with PHP and also shows us how to use it with three cool examples!

Author Info:
By: Joe O'Donnell
Rating: 4 stars4 stars4 stars4 stars4 stars / 49
April 04, 2002
TABLE OF CONTENTS:
  1. · Building A Dynamic MySQL Paging Class With PHP
  2. · The class
  3. · The RecNav constructor
  4. · The ShowRecs function
  5. · Using the RecNav class
  6. · Conclusion

print this article
SEARCH DEVARTICLES

Building A Dynamic MySQL Paging Class With PHP - The class
(Page 2 of 6 )

Displaying the results of a database query in an easily readable format is one of those jobs that we as developers face each and every day, but what's actually the best way to display several hundred, or even several thousand records from a MySQL database? The answer is recordset paging.

Take a look at some popular sites such as Amazon, CDNow and eBay. They all use recordset paging to span their products out across multiple pages. Breaking your database queries up into groups of 10 or so records and providing links to view the rest of the records on a web page is not only easy to implement, but it also gives your site a professional touch: it's one thing to display 349 records on a page, but it's definitely another to show the first 10 records and then provide easy to use links to view the other 339 records in groups of 10 records at a time.

PHP supports classes, so wouldn't it be good if you could create just one class, wrap it up into a file and then instantiate that class whenever you needed to perform some recordset paging? Wouldn't it also be good if this class supported a template system with which you could define the HTML layout of the results? OK OK, so I sound like I'm trying to market my class to you... I got caught in the moment!

I'm actually talking about my recordset paging class, which is called RecNav. RecNav is a simple PHP class and it contains just one constructor and one function.

You should download the support material for this file before continuing. It includes the RecNav class and a sample script.

The constructor allows you to setup your RecNav class. It accepts a number of parameters. Its signature is shown below:

function RecNav(&$LinkIdentifier, $Query, $Template=DEFAULT_TEMPLATE, $TemplateHeader=DEFAULT_TEMPLATE_HEADER, $TemplateFooter=DEFAULT_TEMPLATE_FOOTER, $RecsPerPage=DEFAULT_NUM_RECS)

If you haven't worked with PHP and classes before, then you wouldn't have any idea what a constructor is... allow me to explain. A constructor is a special class function that has the same name as the class and is automatically executed by PHP whenever a new instance of that class is created. So, in this article the class we're working with is called RecNav, thus if we create a function called RecNav as part of our class, then PHP automatically executes this function whenever a new instance of the RecNav class is created.

The RecNav constructor accepts six parameters, with only the first two being compulsory. The details of these parameters are shown below:
  • &$LinkIdentifier: A reference to an identifier created by a call to mysql_connect. This identifier is used to perform queries against the MySQL database.
  • $Query: The query that will be used to build the recordset. $Query should be something like SELECT * FROM Products. The RecNav class handles the LIMIT keyword, etc.
  • $Template: Each record in the page is returned as part of a template. $Template should contain placeholders for the values of each record (more on this soon).
  • $TemplateHeader: The header template for the page of records that will be returned.
  • $TemplateFooter: The footer template for the page of records that will be returned.
  • $RecsPerPage: The number of records to return per page.
The first two parameters should be fairly self-explanatory, but I'm guessing that you're confused by the template parameters, right? Take a look at the descriptions for each template variable below:

$Template:

Let's pretend that we need to display 10 records from a MySQL database on the page. We want the records to look a bit fancy, so we make each record a <tr> tag containing multiple <td> tags, maybe like this:

<tr>

<td width="50%">Record #1</td>

<td width="20%">Available</td>

<td width="30%">$56,000</td>

</tr>

...

<tr>

<td width="50%">Record #10</td>

<td width="20%">Available</td>

<td width="30%">$62,950</td>

</tr>


Replace the values in the HTML above with placeholders, and that's what the $Template variable would look like.

$TemplateHeader

A table's no good without a row that describes what their fields are called, so we would place the following HTML code just before the <tr> tags above:

<tr>

<td width="50%">Record Number</td>

<td width="20%">Availability</td>

<td width="30%">Price</td>

</tr>


$TemplateFooter

A table also needs a closing tag, which might be a simple as a </table> tag.

All of the template variables mentioned above are used by the RecNav class to build a HTML table containing both the records for the requested page as well as navigational links to get to the other pages of records.

The most useful template variable is $Template. $Template is used to output each result to the page and accepts placeholders for each field in a record. If you don't specify a value for $Template, then it becomes the default value of DEFAULT_TEMPLATE, which is defined in the same file as the RecNav class. DEFAULT_TEMPLATE is a string variable, and looks like this:

<tr>

<td width='10%' height='21' align='center'>

<font face='verdana' size='1' color='darkblue'>

<b><| row0 |></b>

</font>

</td>

<td width='80%' height='21'>

<font face='verdana' size='1' color='black'>

<| row1 |>

</font>

</td>

<td width='10%' height='21'>

<font face='verdana' size='1' color='red'>

$<| row2 |>

</font>

</td>

</tr>


Notice the bolded tags? These are the placeholders. When the ShowRecs function of the RecNav class is called (we will look at this function later), these values are replaced with the field values from the MySQL record. The DEFAULT_TEMPLATE variable contains three placeholders, so if we had a MySQL table like this:

People

---------------------------------

PersonId INT

PersonName VARCHAR(50)

Salary DECIMAL(6,2)

With a couple of records in it, like this:

PersonId | PersonName | Salary

---------------------------------

1 John 24,000

2 Peter 28,000

3 Anthony 34,000

4 Robert 16,000

Then the record for John would be displayed like this:

<tr>

<td width='10%' height='21' align='center'>

<font face='verdana' size='1' color='darkblue'>

<b>1</b>

</font>

</td>

<td width='80%' height='21'>

<font face='verdana' size='1' color='black'>

John

</font>

</td>

<td width='10%' height='21'>

<font face='verdana' size='1' color='red'>

$24,000

</font>

</td>

</tr>


As you can see, the placeholders are replaced with the values from the record, making for a highly generic class: You decide which fields you'd like in the HTML output and where they should go.

Hopefully you now understand how the RecNav class works, so let's look at the code for the constructor function on the next page.
blog comments powered by Disqus
MYSQL ARTICLES

- MySQL and BLOBs
- Two Lessons in ASP and MySQL
- Lord Of The Strings Part 2
- Lord Of The Strings Part 1
- Importing Data into MySQL with Navicat
- Building a Sustainable Web Site
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- PhpED 3.2 More Features Than You Can Poke ...
- Creating An Online Photo Album with PHP and ...
- Creating An Online Photo Album with PHP and ...
- Security and Sessions in PHP
- Setup Your Personal Reminder System Using PHP
- Create a IP-Country Database Using PERL and ...
- Developing a Dynamic Document Search in PHP ...

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials