Working with Transactions (TRANSACTIONS)

Transactions allow us to execute a group of SQL commands as a single, atomic unit. If any command fails, we can undo all changes and return the database to its state before the start. In PDO, we manage transactions with three key methods: beginTransaction() (start), commit() (confirm all changes), and rollBack() (cancel everything in case of an error).

EXAMPLE

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "pdo_database";

try {
    $connection = new PDO("mysql:host=$server;dbname=$database;charset=utf8mb4", $username, $password);
    $connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // STEP 1: Start of transaction
    $connection->beginTransaction();

    // STEP 2: Executing SQL statements
    $connection->exec("INSERT INTO users (first_name, last_name, email) VALUES ('Alice', 'White', 'alice@example.com')");
    $connection->exec("INSERT INTO users (first_name, last_name, email) VALUES ('Matthew', 'Brooks', 'matthew@example.com')");

    // STEP 3: Committing the changes
    $connection->commit();
    
    echo "<div style='font-family: monospace; color: #2e7d32; margin-bottom: 12px;'>Transaction successful! Both users were added.</div>";

    // STEP 4: Displaying the refreshed table
    $query = $connection->query("SELECT id, first_name, last_name, email FROM users");
    $results = $query->fetchAll(PDO::FETCH_ASSOC);

    echo "<table border='1' style='border-collapse: collapse; width: 100%; font-family: monospace;'>";
    echo "<tr><th>ID</th><th>First Name</th><th>Last Name</th><th>Email</th></tr>";

    foreach ($results as $row) {
        echo "<tr>";
        echo "<td>" . $row['id'] . "</td>";
        echo "<td>" . $row['first_name'] . "</td>";
        echo "<td>" . $row['last_name'] . "</td>";
        echo "<td>" . $row['email'] . "</td>";
        echo "</tr>";
    }
    echo "</table>";

} catch (PDOException $e) {
    $connection->rollBack();
    echo "Transaction failed and was rolled back: " . $e->getMessage();
}

$connection = null;
?>

RESULT

The result will be displayed here...

In the example above, we attempted to insert two new records into the users table simultaneously. If an error occurred during the second insertion, PDO would use the catch block to discard the first entry as well, keeping the database clean and consistent.

beginTransaction() disables autocommit mode.
commit() permanently saves all changes made within the transaction.
rollBack() is triggered in the catch block and reverts all commands since the start of the transaction.