There were some issues with in the mysql driver; they were fixed just
yesterday. If you upgrade to the latest snapshot from
http://snaps.php.net, you should have better luck.
You'll probably benefit from a deeper explanation of what's going on
here, so here we go:
Generally speaking, a driver will allow you to prepare a statement for
later execution. You can keep a prepared statement around for as long
as you like, and run other queries quite happily.
Once you start to execute() the statement, the driver will
(usually--some drivers do not) occupy the network connection to the
database until you have finished fetching all of the data, which means
that you can't kick off new queries or continue fetching data from other
queries while it is thus occupied.
This means that your code sample is technically incorrect, because you
don't finish your conversation with statement 1 before executing
statement 2.
A more correct version of your script would be:
$stmt_1 = $pdo->prepare('SELECT x FROM table');
$stmt_2 = $pdo->prepare('SELECT y FROM table');
$stmt_1->execute();
print("Statement1: \n");
while ($row = $stmt_1->fetch(PDO_FETCH_ASSOC)) print_r($row);
$stmt_2->execute();
print("Statement2: \n");
while ($row = $stmt_2->fetch(PDO_FETCH_ASSOC)) print_r($row);
Another safe technique is to use fetchAll() to fetch the data up-front,
and then use foreach() to iterate over it.
If you're writing code like this:
$stmt_1->execute();
var_dump($stmt_1->fetch());
$stmt_2->execute();
you'll run into a similar issue, because you're not giving $stmt_1 the
opportunity to finish its conversation with the database server--even if
there was only one row to fetch. In such a situation, you can use the
newly added closeCursor() method to indicate that you're done with the
results from this execution; you can still re-execute the statement
later--it doesn't delete or otherwise maim the statement handle:
$stmt_1->execute();
var_dump($stmt_1->fetch());
$stmt_1->closeCursor(); // done for now
$stmt_2->execute();
var_dump($stmt_2->fetchAll());
$stmt_1->execute(); // run it again--closeCursor didn't delete $stmt_1
Hope that helps,
--Wez.
Jochen Vajda wrote:
Hi all,
Yesterday I played with PDO and I ran into an unexpected behaviour.
In the following code, statement 2 is never executed because of a out
of sync warning.
But if you have exceptions enabled there is no exception thrown and you
wonder about the result.
Should a new statement not override an old statement?
Why are there statement objects if I could only execute one statement?
Is this a db driver specific issue or because it's not implemented yet?
For testing I used PHP 5.1.0b2.
<?php
$pdo = new PDO('mysql:dbname=db;host=localhost', 'user', 'password');
$pdo->setAttribute(PDO_ATTR_ERRMODE, PDO_ERRMODE_WARNING); # Try in
exception mode, too
$stmt_1 = $pdo->prepare('SELECT x FROM table');
$stmt_1->execute();
$stmt_2 = $pdo->prepare('SELECT y FROM table');
$stmt_2->execute();
// Returns nothing !!!
print("Statement2: \n");
while ($row = $stmt_2->fetch(PDO_FETCH_ASSOC)) print_r($row);
print("Statement1: \n");
while ($row = $stmt_1->fetch(PDO_FETCH_ASSOC)) print_r($row);
?>
Greets,
Jochen Vajda
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
