Iterating over a table (with Zend_Db_Table and Zend_Paginator)

So frequently, I need to run small data migrations or transformations. Especially on the way to Doctrine, there's a lot to clean-up in a database which has been used and evolved over five years or so.

The other day, I wanted to run some transformations on the data located a history column in a pretty simple table — here's what it looks like:

mysql> SHOW FIELDS FROM data;
+-------------+------------------+------+-----+---------------------+----------------+
| Field       | Type             | Null | Key | Default             | Extra          |
+-------------+------------------+------+-----+---------------------+----------------+
| id          | int(11)          | NO   | PRI | NULL                | auto_increment | 
| first       | varchar(255)     | YES  | MUL | NULL                |                | 
| last        | varchar(255)     | YES  | MUL | NULL                |                | 
| email       | varchar(255)     | YES  | MUL | NULL                |                | 
| history     | varchar(255)     | YES  | MUL | NULL                |                | 
| rec_datemod | datetime         | YES  |     | NULL                |                | 
| rec_dateadd | datetime         | NO   | MUL | 0000-00-00 00:00:00 |                | 
+-------------+------------------+------+-----+---------------------+----------------+
12 rows in set (0.02 sec)

Building a class to interface the table is simple:

<?php
class Data extends Zend_Db_Table_Abstract
{
    protected $_primary = 'id';
    protected $_name = 'data';
}

Now it should be easy to iterate across it, find data and save — wrong!

So part of the problem is Zend_Db_Table: just doesn't provide an interface for that kind of thing.

It would be nice if it implemented an iterator (in Zf 2.0 or whatever). I'm guessing though that it should be dropped in favour of Doctrine2 so the framework can focus on other areas and leverage excellent the code and the brainpower put into Doctrine(2).

Anyhow — back to iterating a table. Since Zend_Db_Table is of no help, let's use a paginator!

Zend_Paginator to the rescue

When used in web-context, Zend_Paginator's setup can be pretty demading. I'm not sure if other people share my sentiments, but the pagination control (helper and related partial) sure blow my mind.

Luckily, we are gonna use Zend_Paginator in a command line script. Which means: no pagination control necessary. ;-)

In a nutshell Zend_Paginator needs to know two things: one is a database query of how to retrieve the records for the current page. The other one is the total number of records to. This is used to figure out the total number of pages, current interval and so on.

Here's how!

Return data

Here's the first part — I'm gonna use my simple Data model here:

<?php
$data = new Data();
$select = $data->select();
$adapter = new Zend_Paginator_Adapter_DbTableSelect($select);

Hint, hint: Zend_Paginator_Adapter_DbTableSelect vs. Zend_Paginator_Adapter_DbSelect — here is an interesting side-effect. While the second returns a dumb array, the first returns an object of Zend_Db_Table_Row, which allows us to easily manipulate data and save it to the database.

This took me a while to figure out — thanks for the pointer!

Counting

The second query is for counting:

<?php
$select = $data->select()->from('data', array('COUNT(*) AS row_count'));
$rowCount = $this->model->fetchRow($select2)->row_count;
$adapter->setRowCount($rowCount);

The above translates to:

SELECT COUNT(*) AS row_count FROM data;

Continue to use $adapter and create the object of your desires:

<?php
$paginator = new Zend_Paginator($adapter);
$paginator->setItemCountPerPage(200);

Tada! A (really) simple example for Zend_Paginator with 200 items per page!

Using it all in a script

I'd recommend you stick the above code in a function or a small class (I OO everywhere).

My script looks like the following:

<?php
$currentPage = 1;

$cli = new Cli;
$paginator = $cli->getPaginator($currentPage);
$totalPages = $paginator->count();

while ($currentPage <= $totalPages) {
    $paginator->setCurrentPageNumber($currentPage);
    foreach ($paginator as $row) {
        echo $row->id . PHP_EOL;
    }
    ++$currentPage;
}

That's all. Spice it up with some Zend_ProgressBar and it looks pretty professional. ;-)

Working with the data

In case you made it this far, you should have a loop which echo's the id.

Here's a little extra (remember Zend_Paginator_Adapter_DbTableSelect?) to demo working with the actual record easier — using Zend_Db_Table_Row:

<?php
// assuming $paginator is setup
foreach ($paginator as $row) {
    $row->history = 'some new string';
    $row->save(); // done
}

…pretty simple, eh?

Fin

This is by far not the most efficient code (but I guess you already knew that). I'm guessing or hoping that toolkits like Doctrine(2) offer something very similar.

This blog post is more or less an example of how to get the most out of a (Zend) framework when you are using it anyway.

Other small advantages would be that extending the script with resume capabilities should be trivial. Add a commandline argument (see getopt()) and easily resume or skip pages next time you re-run the script. It's all there.

Till's blog