PHP: PDO Transaction Example.

This is a short tutorial on how to use transactions with PDO object in PHP. For those who don’t know; database transactions represent a “block” or “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 ensure that all queries have executed successfully BEFORE you commit the changes to the database. Example: The second query should not be executed if the first query fails.

For this example, I’m going to create a mock application that adds credit or money to a user’s account:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55

<?php   /** * 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 start our transaction. $pdo->beginTransaction();   //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{       //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(); }

A run-down of the PHP above:

1. We connected to MySQL using the PDO object. We set the error mode to PDO::ERRMODE_EXCEPTION so that failed queries will result in a PDO Exception being thrown. We also disabled emulated prepared statements by setting PDO::ATTR_EMULATE_PREPARES to false.
2. For example purposes, I’ve set the User ID to 1 and the payment amount to 10.50.
3. We begin our transaction by calling the beginTransaction function. As soon as this function is called, the autocommit mode for MySQL is set to 0 (by default, this is set to 1).
4. We open our Try / Catch block. We will run all of our queries inside the Try block so that we can catch any exceptions that occur.
5. We insert the payment into our payments table.
6. We then update the user’s account with his / her new credit count.
7. Finally, we commit the transaction by calling the commit function.

If one of our queries fail, a PDO exception will be thrown and the code inside our catch block will be executed. This means that the entire transaction will be rolled back if an error occurs.

Explanation of transaction terms.

A quick explanation of some of the key terms:

• Begin transaction: The transaction is started and the default autocommit nature of MySQL is disabled. Example: If you run an INSERT query, the data won’t be inserted straight away.
• Commit: When you commit a transaction, you are basically telling MySQL that everything went OK and that the results of your queries are to be made final.
• Rollback: When you rollback a transaction, you are basically telling MySQL that you do not want the changes to be committed. This is often used whenever a query fails.