How to run several queries in a transaction

Short answer

Wrap the queries in beginTransaction() and commit(), and call rollBack() on an error:

<?php
 
$pdo = new PDO('sqlite::memory:');
$pdo->exec('CREATE TABLE accounts (id INTEGER PRIMARY KEY, balance INTEGER CHECK (balance >= 0))');
$pdo->exec('INSERT INTO accounts (balance) VALUES (500), (100)');
 
function transfer(PDO $pdo, int $from, int $to, int $amount): void
{
    $pdo->beginTransaction();
 
    try {
        $pdo->prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')->execute([$amount, $to]);
        $pdo->prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')->execute([$amount, $from]);
        $pdo->commit();
    } catch (Throwable $e) {
        $pdo->rollBack();
 
        throw $e;
    }
}
 
transfer($pdo, 1, 2, 200);
 
try {
    transfer($pdo, 1, 2, 9999);
} catch (PDOException) {
    echo "Transfer cancelled\n";
}
 
echo implode(', ', $pdo->query('SELECT balance FROM accounts ORDER BY id')->fetchAll(PDO::FETCH_COLUMN));

Output:

Transfer cancelled
300, 300

What happened

The second transfer credited the recipient, but the debit violated the table constraint. rollBack() undid both queries, and the balances stayed as they were: 300 and 300.

Thanks for reading!

Short answer

What happened

Functions used in this recipe

Thanks for reading!