PHP: PDO transaction example.

In this guide, we will show you how to use transactions with PHP’s PDO object.

For those who don’t know, a database transaction represents a “block” or a “unit” of work.

In most cases, this “unit” of work will consist of multiple queries that are somehow related to one another.

In certain scenarios, you might want to make sure that all of your queries execute successfully before you commit the changes to your database.

For this example, we’re going to create a mock PHP application that adds money to a user’s account.

In this case, we have two queries. The first one will insert a payment record into a table called “payments”. The second one will update the balance of the user’s account.

/**
 * Connect to MySQL and instantiate the PDO object.
 * Set the error mode to throw exceptions and disable emulated prepared statements.
 */
$pdo = new PDO('mysql:host=localhost;dbname=test', 'root', '', array(
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_EMULATE_PREPARES => false
));


//We are going to assume that the user with ID #1 has paid 10.50.
$userId = 1;
$paymentAmount = 10.50;


//We will need to wrap our queries inside a TRY / CATCH block.
//That way, we can rollback the transaction if a query fails and a PDO exception occurs.
try{

    //We start our transaction.
    $pdo->beginTransaction();


    //Query 1: Attempt to insert the payment record into our database.
    $sql = "INSERT INTO payments (user_id, amount) VALUES (?, ?)";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $userId, 
            $paymentAmount,
        )
    );
    
    //Query 2: Attempt to update the user's profile.
    $sql = "UPDATE users SET credit = credit + ? WHERE id = ?";
    $stmt = $pdo->prepare($sql);
    $stmt->execute(array(
            $paymentAmount, 
            $userId
        )
    );
    
    //We've got this far without an exception, so commit the changes.
    $pdo->commit();
    
} 
//Our catch block will handle any exceptions that are thrown.
catch(Exception $e){
    //An exception has occured, which means that one of our database queries
    //failed.
    //Print out the error message.
    echo $e->getMessage();
    //Rollback the transaction.
    $pdo->rollBack();
}

In this example, we make sure that all of our queries are successful before we commit the changes.

In other words, we do not want to insert a record into the payments table if the second query is unable to update the user’s credit balance.

An explanation of our PDO transaction code.

A step-by-step explanation of the PHP code above:

  1. Firstly, we connect to MySQL using the PDO object. We set the error mode to PDO::ERRMODE_EXCEPTION so that PHP will throw a PDO Exception if an error occurs. We also disabled emulated prepared statements by setting PDO::ATTR_EMULATE_PREPARES to false.
  2. For the sake of this example, we set the User ID to 1 and the payment amount to 10.50.
  3. We start off by opening our Try / Catch block. All of our queries will occur inside of this “Try block” so that we can catch any exceptions that may occur.
  4. We begin our transaction inside the Try block by calling the beginTransaction function. When we call this function, it will disable MySQL’s autocommit mode. This means that MySQL will not commit any changes until we tell it to.
  5. After that, we attempt to insert the payment into our payments table.
  6. We then update the user’s account with their new credit count.
  7. Finally, we commit the transaction by calling the commit function.

If one of our queries fails, a PDO exception will be thrown.

If that happens, the code inside of our catch block will execute.

At that stage, MySQL will abort the transaction and rollback any changes.

Transaction terms.

A quick explanation of some of the key terms.

  • Begin transaction: The transaction begins by disabling the default autocommit nature of MySQL. For example, if you run an INSERT query, MySQL will not commit the change. Instead, it will “hold” the query until you manually commit it.
  • Commit: When you commit a transaction, you are basically telling MySQL that it should go ahead and save any changes. It is another way of saying “Everything went fine. You can save those changes now.”
  • Rollback: When you perform a rollback, you are telling MySQL to abort the transaction and discard any changes. Typicall, a rollback will only occur if a query fails.