如何使用PHP PDO函数获取在SQL事务中获取特定查询的结果？

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?

Answer 1

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.