Using concrete Zend_Db_Table_Abstract to efficiently iterate through a rowset

Question

I'm using a concrete implementation of Zend_Db_Table_Abstract:

class DB_TestClass extends Zend_Db_Table_Abstract {
    protected $_name = "test.TestData";
}

If I want select all rows in the table, I seem to have one option:

$t = new DB_TestClass;
$rowset = $t->fetchAll();

This returns an instance of Zend_Db_Table_Rowset which has an iterable interface you can loop though and access each row entry as a rowClass instance:

foreach($rowset as $row) {
    var_dump($row);
}  

HOWEVER, the rowset has loaded every row from the database into memory(!) On small tables this is OK, but on large tables - thousands of rows, for example - it quickly exhausts the memory available to PHP and the script dies.

How can I, using Zend_Db, iterate through the result set retrieving one row from a statement handle at a time, ala mysql_fetch_assoc()? This would allow efficient access to any number of rows (thousands, millions) without using excessive memory.

Thanks in advance for any suggestions.

Fogged

Answer 1

Then fetchAll () is not capable of what you want. You need to use Zend_Db_Select to get all the records and then do what you've intended through the loop

        $select = new Zend_Db_Select ($this->getFrontController()->getParam('bootstrap')->getResource ('Db'));
        $statement = $select->from ('verses')->query ();
        $statement->execute ();


        while ($row = $statement->fetch ())
        {
            // try something like that
            $db_row = new Zend_Db_Table_Row (array ('table' => $table, 'data' => $row));

            $db_row->text = $db_row->text . '_';
            $db_row->save ();

        }

Quadrivium

Doing this means you lose the row access gateway pattern functionality (so on each $row, you can no longer do: '$row->field = "new value"; $row->save();'), which is one of the benefits of using Zend_Db. Is there a way of bringing that back in using your suggestion above? – Fogged

Oh, and by the way, have a look at Zend_Db_Table_Select. Probably that will suit you better. – Quadrivium

Answer 2

Can you please specify the purpose for fetching all the rows together? Cause if you want to do some kind of processing on all the rows in the table then you any which ways have to get all the rows in to the memory. On the other hand if you want to do something like pagination, you can always use zend_pagination object which will just fetch the limited no. of rows at one time. Or better still you can set the offset and no. of rows to be fetched in the fetchAll function itself.

Fend

Say, for example, I want to run an update statement on each row, ala: '$row->column = "new value"; $row->save();'.Calling fetchAll with an offset and number of rows to retrieve them in sets is also inefficient (many DB hits, or if each row contained 1MB data (possible, if you're storing blobs), then it doesn't take much to exhaust the available RAM.) There shouldn't be a need for me to fetch, into RAM more than a single row at a time. – Fogged

If you are saving very heavy data in the database maybe its time to use something like memcached to cache that heavy data to avoid round trips to the server and distribute it over multiple servers. As far as I know Zend Framework's Abstract Table class uses the mysql_fetch_assoc function only so should not be much of a difference and yes if there are multiple rows to update then you must be using "update" command directly i suppose, doesn't make much sense updating each row. – Fend