Tag: Zend_Db_Select

Zend_Paginator and Your Result Set.

The day was cloudy, it rained last night (i love rain), so it placed me into one of those, “damn about time”, moods.  Then I popped open Chrome and came to one of my usual stomping grounds on the net.  I clicked on the thread. Read how a few people were having an issue I grappled with earlier when dealing with Zend_Paginator and decided to write. The issue, How to use the Zend_Paginator on a database result set without having to fetch ALL of the records.

What is the Pagination?  
A bit of an intro. What’s Pagination.  Pagination is simply a way in which developers allow users visiting a site to scroll through a long list of records be it 10 at at time, 100 at a time, or <your arbitrary number here> at a time.  Im sure you’ve seen it around the web but might not have called it by the fancy-smancy “Pagination” name.  Hell I call it, “that next and previous button thinggie”, or “that 1 2 3 4 5 page selector stuff”.  Lets continue. 

Pagination used to be an tedious but necessary task for developers.  I wont go into al the steps to create your own custom pagination code base but let me just tell you it sucked. Thank you Zend_Paginator for less stress in my life.

Zend_Paginator a public service announcement.
In its newest installment, Zend Framework 1.6, the developers at Zend added in a pagination component, Zend_Paginator.  This allowed developers like you and me to create Pagination features on the fly (it took me 1 minute to implement when i timed it)  Lets take a quick example.

<?php
require_once “Zend/Loader.php”;
Zend_Loader::loadClass(“Zend_Paginator”);

//Intialize records. This is what th user will paginate through.
$myRecords = array(“Armando Padilla”,
“Monica R.”,
“Elisa P.”,
“Francisca P.”,
“Guillermo P.”,
“Some awesome person in my family”);

//Create Paginator Object
$Paginator = Zend_Paginator::factory($myRecords);

//Set the current page were on.
$Paginator->setCurrentPageNumber($currentPage);

//Set the number of records to show per page
$Paginator->setItemCountPerPage(3);

//Save Paginator as View var.
$this->view->paginator = $Paginator;
?>

Going through the code above, we initialize our array of records which will be paginated by the user in the variable $myRecords.  I then create a Zend_Paginator object and pass in $myRecords into the factory method.  The Zend Framework then hooks everything else up for us behind the scenes and all we have to do is make sure we pass in the $currentPage the user is currently on and display the pagination links on our page.  

The Problem
So wheres the problem?  Well the problem lies in how users of the Zend_Paginator object are using it.  Many developers are using the Zend_Paginator object with database records errounously.  They hit the database, return ALL the records, store them into memory, and then allow Zend_Paginator to handle these potential HUGE record sets in the backend. Bleh  

Wrong way of doing it Example:

<?php
require_once “Zend/Loader.php”;
Zend_Loader::loadClass(“Zend_Paginator”);

//Initialize of DB object
$db = new Zend_Db::factory() //Paraphrasing here.
$statement = “SELECT * FROM Player”;
$myRecords = $db->fetchAll($statement);

//Create Paginator Object
$Paginator = Zend_Paginator::factory($myRecords);

//Set the current page were on.
$Paginator->setCurrentPageNumber($currentPage);

//Set the number of records to show per page
$Paginator->setItemCountPerPage(3);

//Save Paginator as View var.
$this->view->paginator = $Paginator; 
?>

Very inefficient.  

The Solution
We don’t have to select ALL the records in the Db but allow Zend_Paginator to select and use small manageable pieces at a time for us.  How?  If we had a table with 1000000 records we dont have to select all records but construct a Zend_Db_Select object with the appropriate criteria, and then pass that into the factory instead of the array.  Lets take a look how this is done using the same Players table.

The right way of doing it

<?php
require_once “Zend/Loader.php”;
Zend_Loader::loadClass(“Zend_Paginator”);

//Initialize of DB object
Zend_Loader::loadClass(“Zend_Db”);
$db = new Zend_Db::factory() //Paraphrasing here. 

//Create Select Object
Zend_Loader::loadClass(“Zend_Db_Select”);
$selectStatement = $db->select()->from(“Players”);

//Create Paginator Object
$Paginator = Zend_Paginator::factory($selectStatement);

//Set the current page were on.
$Paginator->setCurrentPageNumber($currentPage);

//Set the number of records to show per page
$Paginator->setItemCountPerPage(3);

//Save Paginator as View var.
$this->view->paginator = $Paginator;
?>

Thats all there is too it.  Less memory consumption because we dont have fetch ALL our records at a time and allows the Zend Framework to do the heavy lifting for us. Hope this helps a few people looking for the answer to this.