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.
Next: The RecNav constructor >>
More MySQL Articles
More By Joe O'Donnell
/ 32 
