Object Oriented PHP: Paging Result Sets
About the Author
Kevin Yank
Kevin began developing for the Web in 1995 and is a highly respected technical author. He wrote Build your own Database Driven Website using PHP and MySQL, a practical step-by-step guide published by SitePoint, and he's co-author of the SitePoint Tech Times, a bi-weekly newsletter for technically-minded web developers. Kev believes that any good webmaster should have seen at least one episode of MacGyver.
By: Kevin Yank
January 30th, 2002
If as a PHP developer you find yourself writing the same code again and again, or dealing with an increasingly stifling array of custom functions and include files in your script, it's probably time you looked into Object Oriented Programming (OOP). OOP has truly come into its own over the past 5 years or so. The world over, application and Web developers alike have discovered the merits of this type of software design, and now it's your turn!
In this article, I'll teach you the basic principles and features of Object Oriented Programming that exist in the PHP language, and take you through a few simple examples so you can see how it all works. We'll then take this newfound knowledge and apply it to a practical problem. Together, we'll create a reusable component that will handle the splitting of MySQL result sets into pages.
Buckle up; I see objects on the road ahead -- it's going to be a bumpy ride!
What's the Big Idea?
Although Object Oriented Programming itself is not always simple, the main idea behind it is fairly easy to grasp.
The primary goal of Object Oriented Programming is to bind together data (in the form of variables) with the code that is responsible for working with them.
So instead of having one big mess of variables with one big mess of PHP code, OOP would have you break down your problem into smaller (and, ideally, more organized) bundles of variables and code. These bundles can then work together to produce the desired effect. If you do your job really well, you can even take some of those bundles and drop them right into another project down the line and save yourself the trouble of re-developing that functionality the next time you need it.
So just so we're clear, OOP doesn't actually allow you to do anything you couldn't already do with big messes of code and variables; it's really just a more organized way of working. And if you're anything like me, your PHP scripts could use all the help they can get in the organization department!
So let's say you're sick of writing database connection code every time you develop a new database-driven Web site in PHP. You could combine all of the variables (username, password, hostname, database name, etc.) and all the code (connecting to the database server, selecting a database, checking for errors, etc.) involved in your database connection into a single package, called an object. You can then use that object to handle your database connection from then on.
Don't be a Square
Okay, let's face it: rectangles haven't been interesting since 2nd grade geometry. But they make a conveniently simple example for us to get started with, and if you actually did get into Web development hoping for manual labour, you can pretend they're bricks.
So let's say some 2nd grader who's failing geometry has paid you to write a PHP script that performs calculations on rectangles. With the market downturn you're in no position to pick and choose, so you take the job.
You start out with a simple HTML form to allow the student to enter the width and height of a rectangle:
<form action="domyhomework.php" method="get">
Width: <input type="text" name="w" /><br />
Height: <input type="text" name="h" /><br />
<input type="submit" />
</form>
Now, domyhomework.php -- the script that will process this form -- could simply take the width and height and calculate the characteristics of the rectangle:
<?php
$area = $w * $h;
$perimeter = ($w + $h) * 2;
?>
<html>
<body>
<p>Width: <?=$w?><br />
Height: <?=$h?></p>
<p>Area: <?=$area?><br />
Perimeter: <?=$perimeter?></p>
</body>
</head>
Nice and simple, but if you gain a reputation for writing rectangle scripts (hey, it could happen!), you might get tired of writing the code to calculate the area and perimeter over and over again.
If you've learned about PHP functions, you might decide to write two functions to perform these calculations in an include file. Let's say you write a file called rect.php, which contains just the following code:
<?php
function rect_area($width,$height)
{
return $width * $height;
}
function rect_perim($width,$height)
{
return ($width + $height) * 2;
}
?>
You can then use these functions in any file that needs them. Here's our revised domyhomework.php:
<?php
require('rect.php'); // Load rectangle functions
$area = rect_area($w,$h);
$perimeter = rect_perim($w,$h);
?>
<html>
<body>
<p>Width: <?=$w?><br />
Height: <?=$h?></p>
<p>Area: <?=$area?><br />
Perimeter: <?=$perimeter?></p>
</body>
</head>
Now, this is all well and good, but with Object Oriented Programming, we can do better!
The Object Oriented Version
As I explained above, the objective of OOP is to bring the data (in this case, the width and height variables) together with the code for working with them (our two functions for calculating area and perimeter).
Let's modify rect.php so that instead of just defining a couple of functions, it defines a class from which we can create (instantiate) rectangle objects:
<?php
class Rectangle
{
var $width;
var $height;
function Rectangle($width, $height)
{
$this->width = $width;
$this->height = $height;
}
function area()
{
return $this->width * $this->height;
}
function perimeter()
{
return ($this->width + $this->height) * 2;
}
}
?>
Let's step through this code a few lines at a time, so I can explain it from top to bottom:
We start by declaring that we're writing a class named Rectangle.
class Rectangle
{
Class names, by convention, should always begin with a capital letter. This is not required by PHP, but by sticking to the established standard like this, your code will be easier for other developers to read and maintain.
Next we declare two variables: $width and $height.
var $width;
var $height;
These aren't just regular PHP variables, however! Since they appear inside a class definition, we're actually declaring variables that will exist inside each object of this class. Remember: we're drawing up the blueprints from which rectangle objects will be constructed. So basically we're telling PHP that every rectangle should have a width and a height. In OOP parlance, variables that belong to objects are called properties. So we've just declared two properties of Rectangle objects.
Up next, a function:
function Rectangle($width, $height)
{
Like with the variables above, functions declared inside a class definition will belong to every object of this class. This function is special, because it has the same name as the class (Rectangle). Such a function is called a constructor, and is called when the object is instantiated, as we'll see shortly.
Constructors are responsible for setting up the newly-instantiated object so that it's ready to use. In the case of our Rectangle class, we need to give the width and height properties (variables) that we declared above their initial values. As you can see above, this constructor function takes two parameters: $width and $height. Let's see how the constructor assigns those parameter values to the corresponding properties in the object:
$this->width = $width;
$this->height = $height;
}
Within any function that is part of a class definition, the special variable $this refers to the object to which the function belongs. Thus, in the above code, both occurrences of $this mean "this Rectangle object".
The arrow operator (->) may look scary at first, but it's really quite simple. It's used to get at the variables and functions that belong to an object. So in the above, $this->width refers to the $width variable we declared above -- the width property of this object. Likewise, $this->height refers to the $height variable that belongs to this object.
With this in mind, our constructor function is taking $width (the first parameter passed to the function) and assigning it to $this->width (the width property of this object), and is taking $height (the second parameter) and assigning it to $this->height.
We'll see later how we can use this constructor to create a new Rectangle object with a given width and height. For the time being, let's move onto our (by now familiar) area and perimeter calculation functions:
function area()
{
return $this->width * $this->height;
}
function perimeter()
{
return ($this->width + $this->height) * 2;
}
}
These are very similar to the rect_area and rect_perim functions we declared in the previous section, but you'll notice that these functions don't take any width/height parameters. Like the variables ($width and $height) we declared above, these functions will belong to each object that is instantiated from this class. If we create three different Rectangle objects, each one will have an area and perimeter function of its very own. Just like variables that belong to objects are called properties, functions that belong to objects are called methods.
Now, with this in mind, it should become clear why the area and perimeter methods don't require parameters. The job of these functions is not to calculate the area and perimeter of just any rectangle. Their job is to calculate those values for their rectangle!
So if you look closely at the functions, you'll notice that they use $this->width and $this->height as their width and height values. Once again, we're using $this to mean "the object to which I belong".
To see how all the elements of this class declaration come together to create a working object, let's revise domyhomework.php to use our new Object Oriented approach to rectangles.
Rectangle Calculator 2.0
Before we dive into our new rectangle calculation script, we need to learn how to create an object out of a class. That is, we've laid down the blueprints for a Rectangle, but we have yet to actually create one. Here's how:
new Rectangle(width, height)
The keyword new tells PHP that you want it to create a new object, and that's what is responsible for the magic here. By saying new Rectangle, we're telling PHP to instantiate our Rectangle class. Now, remember that the constructor function we declared took two parameters -- the width and the height of the rectangle to be created -- so we must specify those values here (width and height).
So, for example, to create a 10 by 20 rectangle and put it in a variable called $myRectangle, we'd use the following line of PHP code:
$myRectangle = new Rectangle(10,20);
We could change the rectangle's width:
$myRectangle->width = 50;
And then print out its area:
echo $myRectangle->area(); // Prints out '1000'
More importantly, we can create two rectangles, and play with them together:
$rect1 = new Rectangle(10,20);
$rect2 = new Rectangle(30,40);
echo $rect1->area(); // Prints out '200'
echo $rect2->perimeter(); // Prints out '140'
Now that we know how to create and use a Rectangle object, we can create a revised domyhomework.php script:
<?php
require('rect.php'); // Load rectangle functions
$r = new Rectangle($w,$h);
?>
<html>
<body>
<p>Width: <?=$r->width?><br />
Height: <?=$r->height?></p>
<p>Area: <?=$r->area()?><br />
Perimeter: <?=$r->perimeter()?></p>
</body>
</head>
See how elegant the code is? We create a rectangle by specifying its width and height, and from then on the object handles all the details for us. All we need to do to determine the rectangle's area and perimeter is to ask for them by calling the relevant methods. This quality of OOP, where functionality is hidden away inside objects so the developer who uses it doesn't need to know how it works, is called encapsulation.
If you're interested in reading about the object oriented features of PHP in greater detail, I'll refer you to Chapter 13 of the PHP Manual [1]. The basics we've seen to this point are enough for most uses of Object Oriented code in PHP.
But enough of rectangles! Let's now design something useful, shall we?
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 [2] 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.
Implementing the Class
My description of the interface presented by objects of the MySQLPagedResultSet class no doubt gave you plenty of clues as to how the class itself will work. In this final section, we'll plough through the code for the class to see what makes it tick. If your only interest is to use the class in a project of yours, however, you can download pagedresults.php here [3].
class MySQLPagedResultSet
{
var $results;
var $pageSize;
var $page;
var $row;
We start with the list of member variables (a.k.a. properties):
$resultswill be used to store the actual MySQL result set.$pageSizewill store the number of records to be displayed per page.$pagewill keep track of which page of the result set is to be displayed.$rowis used by thefetchArraymethod to track the current row in the result set.
Next up, the constructor:
function MySQLPagedResultSet($query,$pageSize,$cnx)
{
global $resultpage;
As we had decided, the constructor takes three parameters -- the SQL query, the page size (records per page), and the database connection.
The first thing we do in this function is obtain access to the global variable $resultpage. This is the variable that the getPageNav method will insert into the navigation links to indicate which page is to be displayed, so we need to get access to it here to determine whether a page has been specified.
PHP 4.1 NOTE: As of PHP 4.1, the powers that be recommend that theregister_globalsoption inphp.inibe disabled, and that variables be instead accessed through one of the new global arrays:$_GET,$_POST,$_COOKIE,$_SERVER,$_ENV, or$_REQUEST. If you are using PHP 4.1 or later and choose to comply with this recommendation, you can replace this first line with$resultpage = $_GET['resultpage'];.
Next, the constructor executes the query it was given:
$this->results = @mysql_query($query,$cnx);
The results are placed in $this->results -- the member variable we declared above. While we're at it, we initialize $this->pageSize, which will simply be given the value of the $pageSize parameter that was passed to the constructor:
$this->pageSize = $pageSize;
Finally, we need to set the current page. We must first consult the $resultpage variable to see if a particular page was set. If the variable is found to be empty (or erroneously set to a negative number), we set it to 1 so that the first page will be selected by default:
if ((int)$resultpage <= 0) $resultpage = 1;
The (int) forces PHP to cast (i.e. convert) the value in $resultpage to an integer, just in case someone modified the query string and allowed a non-numerical value to slip in.
We also need to be sure the page specified isn't past the end of the result set. For this we use the getNumPages method, which we'll see in a moment:
if ($resultpage > $this->getNumPages())
$resultpage = $this->getNumPages();
Finally, with a valid page number in hand we pass the value to the setPageNum method, which we'll see shortly:
$this->setPageNum($resultpage);
}
The getNumPages method determines the number of pages in the current result set. This method is used internally by other methods of the class, in particular the constructor we have just seen.
function getNumPages()
{
if (!$this->results) return FALSE;
return ceil(mysql_num_rows($this->results) /
(float)$this->pageSize);
}
As you can see, finding the number of pages is a relatively simple calculation. We just divide the number of rows in the result set (mysql_num_rows($this->result)) by the page size ($this->pageSize), and then round up using PHP's ceil function. To ensure that the division produces a fractional value that can be rounded up, we have cast the pageSize property to a floating point number.
The setPageNum method can be used to set the page of the result set that is being viewed. Instances where this method would be useful are fairly rare (try to imagine a case where you'd want to jump to a particular page of the result set but you can't pass, or wish to override, the $resultpage variable), but the constructor also uses it to set the page based on the $resultpage variable. So practically you can think of it as an extension of the constructor.
function setPageNum($pageNum)
{
if ($pageNum > $this->getNumPages() or
$pageNum <= 0) return FALSE;
$this->page = $pageNum;
$this->row = 0;
mysql_data_seek($this->results,($pageNum-1) * $this->pageSize);
}
First the method checks the $pageNum parameter to make sure it's a number within the valid range of pages. If not, it returns false. Next it stores the new page number in $this->page, resets $this->row to zero, and finally uses PHP's mysql_data_seek function to move to the first row of the selected page in the MySQL result set, as calculated from the $pageNum and $this->pageSize.
Next up, getPageNum, which returns the current page number:
function getPageNum()
{
return $this->page;
}
Why a getPageNum method, you ask? After all, if $rs is a MySQLResultSet, you could obtain the page number from $rs->page, right? Well, have a look at the setPage method. As you can see, there's a lot involved in setting a new page number for the result set. If a user of the class decided to set the page by directly modifying $rs->page, none of that stuff would happen, and the object would not behave as expected.
Current thinking on object-oriented design suggests that no property should be directly accessed from outside the class. Rather, methods should be provided to 'get' and 'set' the value of every property that users of the class might need access to. In most object-oriented languages, this can be enforced by making the properties 'private', and thus completely inaccessible to the outside world.
PHP has no such facility for ensuring data privacy, but the principle still applies. Rather than asking a user of the class to remember that, while he or she may read the page number directly, it must always be set using setPageNum, we provide a matching getPageNum method to retrieve the value, and instead do everything we can to discourage accessing the properties directly.
A practical instance where getPageNum might be of use is if you wanted to display "Page X of Y" at the top of your result list. The code for doing so would just be:
<p>Page <?=$rs->getPageNum()?> of <?=$rs->getNumPages()?>.</p>
Wow -- a lot of talk for such a simple method! Fortunately, these next two need little explanation:
function isLastPage()
{
return ($this->page >= $this->getNumPages());
}
function isFirstPage()
{
return ($this->page <= 1);
}
These two functions allow a user of the class to determine if the current page is the first page and/or the last page of the result set. These functions both return either true or false. The code should be pretty self-explanatory.
All that's left are the two real 'workhorse' methods of the class. First, we have fetchArray, which takes the place of mysql_fetch_array for our paged result sets:
function fetchArray()
{
if (!$this->results) return FALSE;
if ($this->row >= $this->pageSize) return FALSE;
$this->row++;
return mysql_fetch_array($this->results);
}
The method returns false both if the result set stored in $this->results is false (which would indicate a failed query), or if the current row number is greater or equal to the page size (which would indicate that the end of the page has been reached). Otherwise, the current row number is incremented and mysql_fetch_array is called to return the next row in the result set.
And finally the 'Big Kahunah', getPageNav:
function getPageNav($queryvars = '')
{
As you can see, the $queryvars parameter is given a default value of '' (the empty string) to make it optional, so you can call getPageNav without parameters if you don't need to pass any variables in the query strings of the generated links.
$nav = '';
if (!$this->isFirstPage())
{
$nav .= "<a href=\"?resultpage=".
($this->getPageNum()-1).'&'.$queryvars.'">Prev</a> ';
}
First off, if the current page is not the first page (which we check using the isFirstPage method) we want to display the 'Prev' link. The URL for the link is a pure query string, since we're just linking straight back to the same page. The query string contains the magic resultpage variable, which will tell the MySQLPagedResultSet object which page to display. We also add any additional query variables (as indicated by the $queryvars parameter) onto the end of the query string.
The output of this function is built up in a variable called $nav, which we'll return at the end, rather than echoing it straight to the output. This makes the method a little more flexible by allowing the user of the class to decide how to handle the output.
The next step is to build the list of pages:
if ($this->getNumPages() > 1)
for ($i=1; $i<=$this->getNumPages(); $i++)
{
if ($i==$this->page)
$nav .= "$i ";
else
$nav .= "<a href=\"?resultpage={$i}&".
$queryvars."\">{$i}</a> ";
}
The if statement ensures that we only bother creating a list if there are more than one. It wouldn't make sense just to have the number '1' displayed at the bottom of the list of results, would it?
We then use a pretty typical for loop to go through the page numbers outputting a linked number for each page, with the exception of the current page (where $i=$this->page), which we print out without a link.
Finally, we print out the 'Next' link if the current page is not the last page (according to isLastPage):
if (!$this->isLastPage())
{
$nav .= "<a href=\"?resultpage=".
($this->getPageNum()+1).'&'.$queryvars.'">Next</a> ';
}
return $nav;
}
}
Writing a class is definitely more time-consuming than writing the bare code to do the same job, but if it's a job you expect to do more than once in your career as a PHP developer, a well designed class like this one can be a real time-saver on future projects!
A Note on Performance
PHP scripts that display large database result sets a page at a time typically make use of the LIMIT clause of the SELECT query in MySQL.
Using LIMIT, you can specify the number of results you want to obtain, as well as the number of the first result you would like to retrieve. For example, you could tell it you want to select up to 5 rows beginning with the 26th. This would be the equivalent of retrieving only the rows on the 6th page of a paged result set that displays 5 records per page.
If the full result set you're working with is likely to be very large in comparison to your page size (e.g. if it contains hundreds of records and you're displaying them five at a time), having PHP retrieve the complete result set from the database server (as is done by the MySQLPagedResultSet class presented in this article) can be a bit of a waste, performance-wise.
If you're in the mood for a challenge, you could adapt the class from this article so that it tacks an appropriate LIMIT clause onto the end of the SELECT query before submitting the query, but you'll also need to have it dissect the query and generate a SELECT COUNT(*) query to determine the number of records in the complete result set.
It is my opinion that, except in extreme cases, the convenience of having a simple, reusable component for creating paged result sets -- in addition to the saved overhead of not having to do a separate query to count the results -- outweighs the lost performance of retrieving the full result set.
Summary
In this article I not only took you through the basics of Object Oriented Programming and demonstrated how they could be applied in PHP, but I walked you through the development of an extremely useful class for splitting MySQL result sets into pages.
Perhaps this class is lacking some functionality of regular result sets, or you'd like to display graphical next/previous links as opposed to the text links we've created here? All you need to do is add more methods to the class to make it do what you need! And the best part is, you only need to write it once and you can then re-use the functionality in any future project!
[1] http://www.php.net/oop
[2] http://sitepoint.com/books/?bookid=php-oop
[3] http://www.webmasterbase.com/examples/php-oop/pagedresultset.zip