Article
Object Oriented PHP: Paging Result Sets
A Paged Result Set
One of the more aggravating pieces of code to write from scratch every time you need it is that which lets you present a large database result set in pages that the user can navigate. With our newly-found OOP skills, we can design a reusable class that will do the work for us!
Unlike our rectangle-based example above, the best way to go about designing a class is usually by deciding how you want objects of that class to behave, and then code it to those specifications. In programmer lingo, we begin by defining the interface for our class.
Let's say we have a database of jokes (readers of my book will be familiar with this example), and we have the following Jokes table, where the AID column refers to entries in an Authors table:
Now, let's say we wanted to write a PHP script that would list all of the jokes by a given author, but would only display 5 at a time and would allow the user to navigate through these pages of 5 jokes.
With conventional scripting, such an application would involve some relatively lengthy PHP code in the page, but here's how it looks if we use an object to do all the work:
<?php
require('pagedresults.php');
$cnx = @mysql_connect('localhost','kyank','********');
mysql_select_db('jokes',$cnx);
$rs = new MySQLPagedResultSet("select * from jokes where aid=$aid",
5,$cnx);
?>
<html>
<head>
<title>Paged Results Demo</title>
</head>
<body>
<table border="1">
<?php while ($row = $rs->fetchArray()): ?>
<tr><td><?=$row['JokeText']?></td><td><?=$row['JokeDate']?></td></tr>
<?php endwhile; ?>
</table>
<p><?=$rs->getPageNav("aid=$aid")?></p>
</body>
</html>
The sections in bold point out where the object (of class MySQLPagedResultSet) comes into play. Everything else should look pretty standard if you've worked with PHP for any length of time. Let's break it down:
require('pagedresults.php');
As with our rectangle example, we've placed our class definition in a separate file. Not only does this make for less confusing code mixed in with our HTML, but it also makes it easy for us to reuse the class on other pages and sites, simply by including the file in any page that needs it.
After we connect to the MySQL server and select our database, we create the object:
$rs = new MySQLPagedResultSet("select * from jokes where aid=$aid",
5,$cnx);
As you can see, the constructor function for this class takes three parameters:
- the SQL query itself
- the number of records we want to display per page (in this case, 5), and
- a reference to the database connection (
$cnx)
We could have designed the object so that we just pass it a normal MySQL result set, but I decided to incorporate the query processing into the object, so that instantiating the object sort of replaces the usual call to mysql_query.
Once you've obtained a result set from the database, the usual procedure is to then use a while loop to step through the result set and print out its contents. Our paged result set object (which we've just instantiated as $rs) allows us to do the same thing, using its fetchArray method as we would normally use the mysql_fetch_array function:
<?php while ($row = $rs->fetchArray()): ?>
So each call to $rs->fetchArray() will return an array that represents one row of the result set (which is placed in the $row variable), until the end of the current page of results is reached. At the end of the page, $rs->fetchArray() will return false, thus ending the while loop.
All that's left is to provide for navigation between pages:
<p><?=$rs->getPageNav("aid=$aid")?></p>
The getPageNav method is responsible for producing the navigation links, which, for page 4 of an 8 page result set, would look like this:
The method creates links back to the current script that contain a special variable called resultpage in the query string. The constructor of MySQLPagedResultSet watches for that variable, and uses it to determine which page of the result set to display.
Since in most cases the SQL query will involve one or more variables (in this case, the $aid variable to select which author's jokes to display), getPageNav may be passed any additional query string elements. In this case, we pass it "aid=$aid", which will ensure that the $aid variable is passed on through any of the generated links.
Now that we've seen how the object should work, we can delve into implementing that interface in the MySQLPagedResultSet class.