[fw-general] Large DB recordset, while/loop, update rows

[Terre Porter]

Hey all,

I'm trying to loop through a large growing record set, using code like:

$options = array(
    Zend_Db::AUTO_QUOTE_IDENTIFIERS => false
);

$db = Zend_Db::factory (
    $config['database.adapter'],
    array (
        'host' => $config['database.params.host'],
        'dbname' => $config['database.params.dbname'],
        'username' => $config['database.params.username'],
        'password' => $config['database.params.password'],
        'port' => $config['database.params.port'],
        'options' => $options
    )
);

$db->query('SET NAMES utf8');

$select = $db->select()->from($thisDBTable)->where('filtered = ?', 0);
$stmt = $db->query($select);

while ($row = $stmt->fetch()) {

        // do some processing stuff and test the record for exclusion
        // ...

        // if the processing is successful, tag the record as filtered for
archiving later
        $db->update($thisDBTable, array('filtered' => 0), "id =
".$row['id']);

        // count the rows
        $cnt++;
        if ($cnt>3) { break;} // debug stoper
}
echo 'Records processed  '. $cnt.'<BR>';

It always stops with $cnt of 0 records unless I replace the update statement
with an echo $row['id'], it returns a $cnt of 4.

I'm guessing this is because I'm reusing the $db and since it is not a
buffered result it overwrites the original query with the update query. (am
I guessing right?)

Anyone know how I can update a record set when using the $stmt->fetch()
loop?

Thanks
Terre