Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using concrete Zend_Db_Table_Abstract to efficiently iterate through a rowset

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.

like image 603
Jonathan Tullett Avatar asked Jun 24 '11 10:06

Jonathan Tullett


2 Answers

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 ();

        }
like image 181
akond Avatar answered Oct 19 '22 08:10

akond


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.

like image 21
Sid Avatar answered Oct 19 '22 07:10

Sid