On Aug 20, 2010, at 7:32 AM, Terre Porter wrote:
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?)
Right, you can't execute another SQL statement until you finish
fetching results from the current unbuffered query.
Anyone know how I can update a record set when using the $stmt-
>fetch()
loop?
I can think of three alternatives:
1. Use a buffered query, which performs a fetchall internally even
though you fetch() one row at a time in your app. But if your query
returns a result set too large to buffer within your PHP memory limit,
this might not work.
2. Collect the id's to update in an array, and then issue a single
update after your loop is done. This still might create a long array
of id's, but that should take a lot less memory than buffering all
columns of the result set.
$ids = array();
while ($row = $stmt->fetch()) {
...
$ids[] = $row['id'];
...
}
$stmt->closeCursor();
$db->update($thisDBTable, array('filtered' => 0), $db->quoteInto("id
IN (?)", $ids));
3. Open a second DB connection for the concurrent updates:
$db2 = Zend_Db::factory( ... );
while ($row = $stmt->fetch()) {
...
$db2->update($thisDBTable, array('filtered' => 0), $db2-
>quoteInto("id = ?", $row['id']));
...
}
Regards,
Bill Karwin
