Page 1 of 1

Zend_Db_Table streaming database result

Posted: Thu Jun 12, 2008 9:29 am
by []InTeR[]
I have some problems with the way Zend framework handles my query's.

If you have a Zend_Db_Table extention like:

Code: Select all

 
class databaseMedia extends Zend_Db_Table
{
    protected $_name            = 'media';
    protected $_primary         = array('id');
    protected $_dependentTables = array('databasePublisher');
}
 
You can get all nice things from you database without using real sql.
Now i want to get about 2.000.000 records with this oop method of the zend framework.

But..
The Zend_Db_Table will get the recods at once when you requesting your data with databaseMedia->fetchAll(), and databaseMedia->fetchRow() will allwase return the same row (first).

I'm looking for something like:

Code: Select all

 
    $oTable = new databaseMedia($oAdapter);
    while($oRow = $oTable->fetchNext())
    {
      // do something with the individiual data row.
    }
 
While the only solution i can find with this framework will be

Code: Select all

 
    $oTable = new databaseMedia($oAdapter);
    $oRows = $oTable->fetchAll(); // huge array now.
    foreach($oRows as $oRow)
    {
      // do something with the individiual data row.
    }
 
Does anybody knows how to use this correctly?

Re: Zend_Db_Table streaming database result

Posted: Thu Jun 12, 2008 9:37 am
by Eran
Not sure what you are asking for, you want to retrieve all the rows in a table but not put them in an array? You want to fetch each row independently? that's the worst scenario performance wise. What is wrong with having them in an array?

Why are you retrieving the entire table if you mind me asking? maybe filtering the results using a WHERE clause would help.

Re: Zend_Db_Table streaming database result

Posted: Thu Jun 12, 2008 12:05 pm
by []InTeR[]
I have a big database. So i dont want to have them in a array. I want them to stay in the mysqlbuffer.
This to save memory that the scripts uses, and increase speed.

I can explain with a little non oop code.

What the 'fetchall' does (what i dont want).

Code: Select all

 
$q = "SELECT * FROM `media`";
$rp = mysql_query($q);
$a = array();
while($t = mysql_fetch_row($rp))
{
  $a[] = $t;
}
foreach($a as $t)
{
   // do something with the row.
}
 
What fetchRow does frmo the Zend_Db_Table (wrong):

Code: Select all

 
$q = "SELECT * FROM `media` LIMIT 1";
$rp = mysql_query($q);
$a = array();
while($t = mysql_fetch_row($rp))
{
  $a[] = $t;
}
foreach($a as $t)
{
   // do something with the row.
}
 
What i want:

Code: Select all

 
$q = "SELECT * FROM `media`";
$rp = mysql_query($q);
while($t = mysql_fetch_row($rp))
{
  // do something with the code
}
 
Something like the fetch from other Zend_Db functions, but the Zend_Db_Table doesnt suport that.

Re: Zend_Db_Table streaming database result

Posted: Thu Jun 12, 2008 1:13 pm
by Eran
OK now I understand, you don`t want them to be returned as row objects (which is what the fetchAll method of the ZF does). Personally I fully agree with you, and haven't use the fetchAll method in a long time. Instead, I built a wrapper around Zend_Db_Select that integrates with the Zend_Db_Table and does what you are asking for and more. I even submitted a proposal for it back then, but the proposal process sort of halted in the meantime (Zend are doing some restructuring of the process or something).
You can read it here if you are interested - http://framework.zend.com/wiki/display/ ... Db_Gateway. It has been a while since I proposed it and has refactored it significantly since.

Re: Zend_Db_Table streaming database result

Posted: Thu Jun 12, 2008 4:18 pm
by Weirdan
well, you could both have them as Zend_Db_Table_Row's and fetch one by one:

Code: Select all

 
class Something extends Zend_Db_Table_Abstract
{
    protected $_name = 'q';
    protected $_primary = array('id');
}
 
$e = new Something;
$query = $e->select()->query();
$query->execute();
while ($row = $query->fetch(Zend_Db::FETCH_ASSOC)) {
    $object = $e->createRow($row); // here we have Zend_Db_Table_Row
    var_dump($object->toArray()); // and use its method
}