How do you obtain the result of a particular query in a SQL transaction using PHP PDO function fetch?

Question

I have a SQL transaction similar to the one below. I am unable to obtain the result of the 3rd query in the transaction using PHP function PDO::fetch()

$database = DatabaseFactory::getFactory()->getConnection();
$sql = "BEGIN;
        DELETE FROM users WHERE ID = 4;
        UPDATE audit SET nousers= nousers - 1 WHERE ID = 4;
        SELECT nousers FROM audit WHERE ID = 4;
        COMMIT;";
$query = $database->prepare($sql);
$query->execute();

How do I use $query->fetch() to obtain the result of the 3rd query in the transaction?

Please check the link:-http://stackoverflow.com/questions/2708237/php-mysql-transactions-examples — Alive to die - Anant, Apr 04 '15 at 14:47
@anantkumarsingh the link does not give an answer regarding obtaining the result of a transaction query using PDO::fetch(). I am not currently using mysql_* functions cause they are deprecated. Thanks for the help. — wolf3D, Apr 04 '15 at 15:10

score 0 · Answer 1 · answered Apr 04 '15 at 15:44

You have five queries in your transaction, counting BEGIN and COMMIT.

Call prepare() and execute() five times, one for each of the queries. For your SELECT query, retrieve and examine the result set.

There's no need to put all queries of a transaction into a single prepare / execute cycle. The whole point of the transaction is to allow you to run multiple queries so they look like one unitary operation to other clients of the database server.

How do you obtain the result of a particular query in a SQL transaction using PHP PDO function fetch?

1 Answers1