Paging and Sorting Data with Zend Framework, Doctrine and PEAR (part 1)
Page Down
When building database-backed applications, one of the important problems for a developer or user interface engineer involves making large data sets more manageable by, and therefore more useful to, application users. To illustrate how important this problem actually is, consider what would happen if Google threw up all the results for a particular search on a single page. Not only would the page take an unimaginably long time to load, but it would be very hard for you, the user, to avoid being overwhelmed by the immense volume of data being presented.
One of the most common solutions to this problem involves breaking these large data sets into smaller chunks ("pages") and providing controls for users to move between them. Not only does this allow the user to exert some control over the amount of data being displayed at any given time, but it also reduces the load on the database server, which now only has to present a subset of the relevant data at any given time.
Back in the good old days, adding pagination to a PHP application was mostly a manual task, involving offset calculations and custom query generation. In recent years, the task has become significantly simpler, mostly due to the presence of ready-made pagination components in most common frameworks. These components are simple to use and integrate, and they are also flexible enough to work different types of data sources, including PHP arrays, database result sets and even XML documents. Using open-source components also reduces the amount of custom programming needed, and improves overall product quality.
This article will introduce you to one such component, Zend_Paginator, which is a part of the Zend Framework. However, open source is all about choice and so, this article will also discuss two other popular components, PEAR Pager and Doctrine Pager. Come on in, and let's get started.
Revving Up
Before diving into the code, a few notes and assumptions. I'll assume throughout this article that you're familiar with HTML, SQL and XML, and that you have a working Apache/PHP/MySQL development environment. I'll also assume that you know the basics of working with classes and objects in PHP, as all the components used in this article are written in compliance with OOP principles.
This article also assumes that you have downloaded and successfully installed the following packages:
- The Zend Framework (this article uses Zend Framework v1.9.3)
- The Doctrine ORM (this article uses Doctrine v1.1.0)
- The PEAR Pager package and all necessary dependencies (this article uses PEAR Pager v3.2.9)
- The PEAR MDB2 package and all necessary dependencies (this article uses PEAR MDB2 v2.4.1)
- The PEAR Structures_DataGrid package and all necessary dependencies (this article uses PEAR Structures_DataGrid v0.9)
For the Zend Framework and the Doctrine ORM, installation is typically as simple as uncompressing the distribution archive and adding the location of the resulting Zend/ or Doctrine/lib/ directory to the PHP include path. For the various PEAR packages, installation can be accomplished either with the PEAR command-line installer or by manually uncompressing the package archive into the main PEAR directory. Detailed installation instructions can be obtained from each project's online manual.
Finally, you should also download and install the MySQL example 'world' database, which is an example database provided by the MySQL development team. It's used in most of the examples in this article, and can be downloaded from the MySQL Web site.
Hero Worship
Let's begin with a simple example. Consider the following script, which uses Zend_Paginator to page through an array:
<?php
// include auto-loader class
require_once 'Zend/Loader/Autoloader.php';
// register auto-loader
$loader = Zend_Loader_Autoloader::getInstance();
// set data
$data = array(
'Superman', 'Spider-man', 'Batman',
'Robin', 'Green Lantern', 'Iron Man',
'The Flash', 'The Human Torch', 'The Hulk',
'Wolverine', 'Captain America', 'Spawn',
'Hellboy'
);
// initialize pager with data set
$pager = new Zend_Paginator(new Zend_Paginator_Adapter_Array($data));
// set page number from request
$currentPage = isset($_GET['p']) ? (int) htmlentities($_GET['p']) : 1;
$pager->setCurrentPageNumber($currentPage);
// set number of items per page
$pager->setItemCountPerPage(5);
?>
<html>
<head></head>
<body>
<div id="data">
<table border="1">
<?php foreach ($pager->getCurrentItems() as $item): ?>
<tr>
<td><?php echo $item; ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
</body>
</html>
Zend_Paginator operates on data sets through so-called "adapters", which are specific to the data source being paginated. By default, Zend_Paginator comes with adapters for PHP arrays, database result sets (through Zend_Db) and SPL Iterators; it's also possible to support new data sources by implementing the Zend_Paginator_Adapter_Interface in a custom class.
This script begins by setting up the Zend auto-loader, which takes care of automatically loading Zend Framework components as needed. It then initializes an instance of Zend_Paginator and passes the object constructor an instance of the Array adapter, which is itself initialized with the array containing the data set. Finally, the Zend_Paginator object's getCurrentItems() method returns the actual data items for the current page.
The current page and the number of items to be displayed per page can be set using the Zend_Paginator object's setCurrentPageNumber() and setItemCountPerPage() methods. In the example above, the current page number is obtained from the request itself, through the $_GET['p'] variable.
To see this script in action, browse to the script URL in a browser, and you should see something like this:
To switch to a different page, add the page number to the URL as a GET variable:
Turning The Pages
In the real world, you can't expect users to manually append GET parameters to a URL to switch between pages; you need to offer them a set of page links so that they can click their way through the data set. This is reasonably easy to do, because Zend_Paginator comes with a getPages() method that provides all the information you need to set up these links. Consider the next example, which illustrates:
<?php
// include auto-loader class
require_once 'Zend/Loader/Autoloader.php';
// register auto-loader
$loader = Zend_Loader_Autoloader::getInstance();
// set data
$data = array(
'Superman', 'Spider-man', 'Batman',
'Robin', 'Green Lantern', 'Iron Man',
'The Flash', 'The Human Torch', 'The Hulk',
'Wolverine', 'Captain America', 'Spawn',
'Hellboy'
);
// initialize pager with data set
$pager = new Zend_Paginator(new Zend_Paginator_Adapter_Array($data));
// set page number from request
$currentPage = isset($_GET['p']) ? (int) htmlentities($_GET['p']) : 1;
$pager->setCurrentPageNumber($currentPage);
// set number of items per page from request
$itemsPerPage = isset($_GET['c']) ? (int) htmlentities($_GET['c']) : 5;
$pager->setItemCountPerPage($itemsPerPage);
// get page data
$pages = $pager->getPages();
// create page links
$pageLinks = array();
$separator = ' | ';
for ($x=1; $x<=$pages->pageCount; $x++) {
if ($x == $pages->current) {
$pageLinks[] = $x;
} else {
$q = http_build_query(array('p' => $x, 'c' => $itemsPerPage));
$pageLinks[] = "<a href=\"?$q\">$x</a>";
}
}
?>
<html>
<head></head>
<body>
<div id="data">
<table border="1">
<?php foreach ($pager->getCurrentItems() as $item): ?>
<tr>
<td><?php echo $item; ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<br/>
<div id="links">
Pages: <?php echo implode($pageLinks, $separator); ?>
</div>
</body>
</html>
This example is similar to the previous one, but it includes one additional method call: the getPages() method, which returns an object containing various bits of useful information: the total number of pages in the data set; the first, last, current, next and previous page numbers; the page numbers in the current page "window"; the total number of items in the data set; and the number of items per page. Here's an example of the object returned by getPages():
stdClass Object
(
[pageCount] => 3
[itemCountPerPage] => 5
[first] => 1
[current] => 1
[last] => 3
[next] => 2
[pagesInRange] => Array
(
[1] => 1
[2] => 2
[3] => 3
)
[firstPageInRange] => 1
[lastPageInRange] => 3
[currentItemCount] => 5
[totalItemCount] => 13
[firstItemNumber] => 1
[lastItemNumber] => 5
)
This information is very useful, because with just a few lines of code, it can be converted into a set of page links that allow the user to navigate through the data set. In the above example, a loop is used to create these links based on the information provided by the getPages() method.
Here's an example of what the output looks like:
Notice also that in this example, the number of items per page is retrieved from the request as a GET parameter, and added to Zend_Paginator via the setItemCountPerPage() method. This allows the user to customize not just which page of the data set is being displayed, but also how many items are displayed on the page. If you play with this a little, you'll also see that the number of available pages increases or decreases in inverse proportion to the number of items per page.
City Breaks
Most of the time, you're not going to be paging through static arrays. So let's look at Zend_Paginator in the context of a more real-world example: paginating database results. Consider the following example, which illustrates one approach based on the previous examples:
<?php
// include auto-loader class
require_once 'Zend/Loader/Autoloader.php';
// register auto-loader
$loader = Zend_Loader_Autoloader::getInstance();
try {
// connect to database and get data set
$dbh = new PDO('mysql:host=localhost;dbname=world', 'root', '');
$sql = 'SELECT ID, Name, CountryCode, District, Population FROM city WHERE ID < 500';
$sth = $dbh->prepare($sql);
$sth->execute();
$data = $sth->fetchAll(PDO::FETCH_NUM);
unset($dbh);
// initialize pager with data set
$pager = new Zend_Paginator(new Zend_Paginator_Adapter_Array($data));
// set page number from request
$currentPage = isset($_GET['p']) ? (int) htmlentities($_GET['p']) : 1;
$pager->setCurrentPageNumber($currentPage);
// set number of items per page from request
$itemsPerPage = isset($_GET['c']) ? (int) htmlentities($_GET['c']) : 20;
$pager->setItemCountPerPage($itemsPerPage);
// get page data
$pages = $pager->getPages();
// create page links
$pageLinks = array();
$separator = ' | ';
for ($x=1; $x<=$pages->pageCount; $x++) {
if ($x == $pages->current) {
$pageLinks[] = $x;
} else {
$q = http_build_query(array('p' => $x, 'c' => $itemsPerPage));
$pageLinks[] = "<a href=\"?$q\">$x</a>";
}
}
} catch(Exception $e) {
die ('ERROR: ' . $e->getMessage());
}
?>
<html>
<head></head>
<body>
<div id="data">
<table border="1">
<tr>
<th>ID</th>
<th>Name</th>
<th>Country Code</th>
<th>District</th>
<th>Population</th>
</tr>
<?php foreach ($pager->getCurrentItems() as $item): ?>
<tr>
<td><?php echo htmlentities($item[0]); ?></td>
<td><?php echo htmlentities($item[1]); ?></td>
<td><?php echo htmlentities($item[2]); ?></td>
<td><?php echo htmlentities($item[3]); ?></td>
<td><?php echo htmlentities($item[4]); ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<br/>
<div id="links">
Pages: <?php echo implode($pageLinks, $separator); ?>
</div>
</body>
</html>
No great magic here: the script opens a connection to the MySQL database server, retrieves a list of city records through a PDO query, iterates over the result set and adds each record to the $data array. Once this is complete, Zend_Paginator is used to split the data into chunks for display, as in the previous examples.
Here's an example of what the output looks like:
Now, while this example works pretty well, it's actually pretty terrible from an efficiency perspective. The reason? Every time the user selects a new page for display, all the matching records in the table are retrieved, but only a small subset is actually presented to the user. Or, to put it more precisely, every time the user selects a new page for display, the PDO query retrieves 500 records, but only 20 of those are actually displayed to the user. This is a waste of bandwidth and memory and, as the database grows in size, it will result in ever-decreasing performance.
Fortunately, there is a solution, and it's a pretty cool one. In addition to the PHP array adapter, Zend_Paginator also comes with an adapter for database result sets. This adapter, which is closely integrated with the Zend_Db and Zend_Db_Select components, works by retrieving only the records that are actually needed for the page being requested by the user. It does this by internally rewriting the query to retrieve the total number of matching records and then, based on the page number requested, calculating the start and end offset of the record chunk needed to populate that page and retrieving only that chunk. The end result is, needless to say, much more efficient.
Here's the code:
<?php
// include auto-loader class
require_once 'Zend/Loader/Autoloader.php';
// register auto-loader
$loader = Zend_Loader_Autoloader::getInstance();
try {
// connect to database and get data set
$db = Zend_Db::factory('Pdo_Mysql', array(
'host' => 'localhost',
'username' => 'root',
'password' => '',
'dbname' => 'world'
)
);
$db->setFetchMode(Zend_Db::FETCH_NUM);
// initialize pager with data set
$pager = new Zend_Paginator(
new Zend_Paginator_Adapter_DbSelect(
$db->select()
->from('city',
array('ID', 'Name', 'CountryCode', 'District', 'Population'))
->where('ID < 500'))
);
// set page number from request
$currentPage = isset($_GET['p']) ? (int) htmlentities($_GET['p']) : 1;
$pager->setCurrentPageNumber($currentPage);
// set number of items per page from request
$itemsPerPage = isset($_GET['c']) ? (int) htmlentities($_GET['c']) : 20;
$pager->setItemCountPerPage($itemsPerPage);
// get page data
$pages = $pager->getPages();
// create page links
$pageLinks = array();
$separator = ' | ';
for ($x=1; $x<=$pages->pageCount; $x++) {
if ($x == $pages->current) {
$pageLinks[] = $x;
} else {
$q = http_build_query(array('p' => $x, 'c' => $itemsPerPage));
$pageLinks[] = "<a href=\"?$q\">$x</a>";
}
}
} catch(Exception $e) {
die ('ERROR: ' . $e->getMessage());
}
?>
<html>
<head></head>
<body>
<div id="data">
<table border="1">
<tr>
<th>ID</th>
<th>Name</th>
<th>Country Code</th>
<th>District</th>
<th>Population</th>
</tr>
<?php foreach ($pager->getCurrentItems() as $item): ?>
<tr>
<td><?php echo htmlentities($item[0]); ?></td>
<td><?php echo htmlentities($item[1]); ?></td>
<td><?php echo htmlentities($item[2]); ?></td>
<td><?php echo htmlentities($item[3]); ?></td>
<td><?php echo htmlentities($item[4]); ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<br/>
<div id="links">
Pages: <?php echo implode($pageLinks, $separator); ?>
</div>
</body>
</html>
This script begins by using the Zend_Db::factory() method to initialize a new Zend_Db object. A Zend_Db_Select object is then used to programmatically construct the required SELECT query; this object is passed to Zend_Paginator's DbSelect database adapter. Using Zend_Db_Select instead of an SQL query string enables Zend_Paginator to perform the necessary query rewriting and "chunking" described earlier. The remainder of the script is the same as before, with the getPages() method taking care of returning the correct subset of records for display.
Incidentally, you can also cache infrequently-modified data and help improve performance further, by passing Zend_Paginator a configured Zend_Cache instance. You'll find more details on this in the online manual
Sliding Around
If you're sharp-eyed, you'll have noticed something about the previous examples: in all of them, the complete set of page links is displayed. This is fine for small result sets, but as the number of results increases, the number of visible page links will also increase beyond what one might reasonably expect. From a user interface point of view, then, there should be a way of restricting the number of page links displayed at any given time.
Zend_Paginator makes this easy, via its setPageRange() method. This method controls the number of pages included in the current page range, or page "window". The actual page numbers depend on the current page and the paging mode (more below), and can be obtained from the 'pagesInRange' property of the object returned by the getPages() method. This might sound a little confusing at first glance, so here are a couple of numerical examples:
Current page = 5 Number of pages in page range = 3 Paging mode = Sliding Page numbers in page range = 4,5,6 Current page = 12 Number of pages in page range = 5 Paging mode = Sliding Page numbers in page range = 10,11,12,13,14
The paging mode tells Zend_Paginator whether the page range should be specified in "jumping", "sliding" or "elastic" mode. In sliding mode, the page range is always centered around the current page; in jumping mode, the page range is displayed in fixed blocks; and in elastic mode, the page range expands and contracts depending on the current page. Personally, I use sliding mode for most applications, but feel free to be different.
Here's an example of setting up paging controls in "sliding" mode, with five page numbers in the available range at any time:
<?php
// include auto-loader class
require_once 'Zend/Loader/Autoloader.php';
// register auto-loader
$loader = Zend_Loader_Autoloader::getInstance();
try {
// connect to database and get data set
$db = Zend_Db::factory('Pdo_Mysql', array(
'host' => 'localhost',
'username' => 'root',
'password' => '',
'dbname' => 'world'
)
);
$db->setFetchMode(Zend_Db::FETCH_NUM);
// initialize pager with data set
$pager = new Zend_Paginator(
new Zend_Paginator_Adapter_DbSelect(
$db->select()
->from('city',
array('ID', 'Name', 'CountryCode', 'District', 'Population'))
)
);
// set page number from request
$currentPage = isset($_GET['p']) ? (int) htmlentities($_GET['p']) : 1;
$pager->setCurrentPageNumber($currentPage);
// set number of items per page from request
$itemsPerPage = isset($_GET['c']) ? (int) htmlentities($_GET['c']) : 20;
$pager->setItemCountPerPage($itemsPerPage);
// set number of pages in page range
$pager->setPageRange(5);
// get page data
$pages = $pager->getPages('Sliding');
// create page links
$pageLinks = array();
$separator = ' | ';
// build first page link
$pageLinks[] = getLink($pages->first, $itemsPerPage, '«');
// build previous page link
if (!empty($pages->previous)) {
$pageLinks[] = getLink($pages->previous, $itemsPerPage, '‹');
}
// build page number links
foreach ($pages->pagesInRange as $x) {
if ($x == $pages->current) {
$pageLinks[] = $x;
} else {
$pageLinks[] = getLink($x, $itemsPerPage, $x);
}
}
// build next page link
if (!empty($pages->next)) {
$pageLinks[] = getLink($pages->next, $itemsPerPage, '›');
}
// build last page link
$pageLinks[] = getLink($pages->last, $itemsPerPage, '»');
} catch(Exception $e) {
die ('ERROR: ' . $e->getMessage());
}
function getLink($page, $itemsPerPage, $label) {
$q = http_build_query(array(
'p' => $page,
'c' => $itemsPerPage
)
);
return "<a href=\"?$q\">$label</a>";
}
?>
<html>
<head></head>
<body>
<div id="data">
<table border="1">
<tr>
<th>ID</th>
<th>Name</th>
<th>Country Code</th>
<th>District</th>
<th>Population</th>
</tr>
<?php foreach ($pager->getCurrentItems() as $item): ?>
<tr>
<td><?php echo htmlentities($item[0]); ?></td>
<td><?php echo htmlentities($item[1]); ?></td>
<td><?php echo htmlentities($item[2]); ?></td>
<td><?php echo htmlentities($item[3]); ?></td>
<td><?php echo htmlentities($item[4]); ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<br/>
<div id="links">
Pages: <?php echo implode($pageLinks, $separator); ?>
</div>
</body>
</html>
Notice that the program logic to generate the page links is different in this case: instead of using the 'pageCount' property, this version iterates over the 'pagesInRange' property so that only the page numbers included in the page range appear. This logic is based on the ItemPagination pattern that's explained in the Zend Framework manual, so take a look there for more information (and a few more pagination patterns as well).
Here's what the output looks like:
The best way to understand the different paging modes is by trying them out. To do this, switch the getPages() argument in the previous example to 'Jumping' and 'Elastic' modes, and the differences should become clear.
Sorting It All Out
Quite often, paging is accompanied by sorting: many applications include sorting filters that allow users to sort result sets by different criteria. This is also reasonably easy to implement, and it makes a nice addition to the user interface once you've got the paging sorted out. Here's a revision of the previous example that illustrates:
<?php
// include auto-loader class
require_once 'Zend/Loader/Autoloader.php';
// register auto-loader
$loader = Zend_Loader_Autoloader::getInstance();
try {
// set sort parameters from request
$sortField = isset($_GET['s']) ? htmlentities($_GET['s']) : 'ID';
$sortDir = isset($_GET['d']) ? htmlentities($_GET['d']) : 'asc';
// connect to database and get data set
$db = Zend_Db::factory('Pdo_Mysql', array(
'host' => 'localhost',
'username' => 'root',
'password' => '',
'dbname' => 'world'
)
);
$db->setFetchMode(Zend_Db::FETCH_NUM);
// initialize pager with data set
$pager = new Zend_Paginator(
new Zend_Paginator_Adapter_DbSelect(
$db->select()
->from('city',
array('ID', 'Name', 'CountryCode', 'District', 'Population'))
->order("$sortField $sortDir"))
);
// set page number from request
$currentPage = isset($_GET['p']) ? (int) htmlentities($_GET['p']) : 1;
$pager->setCurrentPageNumber($currentPage);
// set number of items per page from request
$itemsPerPage = isset($_GET['c']) ? (int) htmlentities($_GET['c']) : 20;
$pager->setItemCountPerPage($itemsPerPage);
// set number of pages in page range
$pager->setPageRange(5);
// get page data
$pages = $pager->getPages();
// build first page link
$pageLinks = array();
$separator = ' | ';
$pageLinks[] = getLink($pages->first, $itemsPerPage, $sortField, $sortDir, '«');
// build previous page link
if (!empty($pages->previous)) {
$pageLinks[] = getLink($pages->previous, $itemsPerPage, $sortField, $sortDir, '‹');
}
// build page number links
foreach ($pages->pagesInRange as $x) {
if ($x == $pages->current) {
$pageLinks[] = $x;
} else {
$pageLinks[] = getLink($x, $itemsPerPage, $sortField, $sortDir, $x);
}
}
// build next page link
if (!empty($pages->next)) {
$pageLinks[] = getLink($pages->next, $itemsPerPage, $sortField, $sortDir, '›');
}
// build last page link
$pageLinks[] = getLink($pages->last, $itemsPerPage, $sortField, $sortDir, '»');
} catch(Exception $e) {
die ('ERROR: ' . $e->getMessage());
}
function getLink($page, $itemsPerPage, $sortField, $sortDir, $label) {
$q = http_build_query(array(
'p' => $page,
'c' => $itemsPerPage,
's' => $sortField,
'd' => $sortDir,
)
);
return "<a href=\"?$q\">$label</a>";
}
?>
<html>
<head></head>
<body>
<div id="data">
<table border="1">
<tr>
<th>ID
<?php echo getLink($pages->current, $itemsPerPage, 'ID', 'asc', '⇑'); ?>
<?php echo getLink($pages->current, $itemsPerPage, 'ID', 'desc', '⇓'); ?>
</th>
<th>Name
<?php echo getLink($pages->current, $itemsPerPage, 'Name', 'asc', '⇑'); ?>
<?php echo getLink($pages->current, $itemsPerPage, 'Name', 'desc', '⇓'); ?>
</th>
<th>Country Code
<?php echo getLink($pages->current, $itemsPerPage, 'CountryCode', 'asc', '⇑'); ?>
<?php echo getLink($pages->current, $itemsPerPage, 'CountryCode', 'desc', '⇓'); ?>
</th>
<th>District
<?php echo getLink($pages->current, $itemsPerPage, 'District', 'asc', '⇑'); ?>
<?php echo getLink($pages->current, $itemsPerPage, 'District', 'desc', '⇓'); ?>
</th>
<th>Population
<?php echo getLink($pages->current, $itemsPerPage, 'Population', 'asc', '⇑'); ?>
<?php echo getLink($pages->current, $itemsPerPage, 'Population', 'desc', '⇓'); ?>
</th>
</tr>
<?php foreach ($pager->getCurrentItems() as $item): ?>
<tr>
<td><?php echo htmlentities($item[0]); ?></td>
<td><?php echo htmlentities($item[1]); ?></td>
<td><?php echo htmlentities($item[2]); ?></td>
<td><?php echo htmlentities($item[3]); ?></td>
<td><?php echo htmlentities($item[4]); ?></td>
</tr>
<?php endforeach; ?>
</table>
</div>
<br/>
<div id="links">
Pages: <?php echo implode($pageLinks, $separator); ?>
</div>
</body>
</html>
The basic implementation idea here is simple: the sorting field and direction are passed along from request to request as GET parameters, together with the page number and item count. These parameters are incorporated into the Zend_Db_Select query, and used to order the result set accordingly. The user-defined getLink() function is also updated to include these parameters in every link that it generates.
Here's what it looks like:
That's about it for the first part of this article. In the second part, I'll be looking at two other commonly-used pagination classes, PEAR Pager and Doctrine_Pager, and illustrating how you can use them as alternatives to Zend_Paginator. Come back for that and, until then, happy coding!
Copyright Melonfire, 2009. All rights reserved.
Comments (Login to leave comments)
$currentPage = isset($_GET['p']) ? (int) htmlentities($_GET['p']) : 1;
instead of this:
$currentPage = isset($_GET['p']) ? (int) $_GET['p'] : 1;
htmlentities in this case is redundant. Sanitization is alerady done by converting it to (int) ;)
its redundant because of the (int) conversion.
But also, when using Doctrine, the ORM does the trick for you ;)