Object Oriented PHP: Paging Result Sets

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!