Transactions with PHP Data Objects (PDO)

PHP Data Objects (PDO) is arguably the best database abstraction layer available in PHP. It provides a consistent interface across multiple datasources (MySQL, PostgreSQL, et cetera) and helps boost your site’s security by way of prepared statements. It even supports transactions which we’ll be taking a look at here.

Database transactions allow the ability to execute multiple statements but also gives you a point in history in which you can roll back in case one of the statements happens to fails. Transactions generally come into play when data integrity is important like at financial institutions or in any situation where you simply want to run multiple queries and undo them all if there is a failure.

For this example, I’m assuming you already have a PDO connection established and an object named $pdo:

try
{
    // Tells PDO to throw exceptions
    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    // Starts our transaction
    $pdo->beginTransaction();

    // Batches a bunch of queries
    $pdo->exec('INSERT INTO `languages` (`language`) values ("PHP");');
    $pdo->exec('INSERT INTO `languages` (`language`) values ("Ruby");');
    $pdo->exec('INSERT INTO `languages` (`language`) values ("Python");');
    $pdo->exec('INSERT INTO `languages` (`language`) values ("Objective-C");');
    $pdo->exec('INSERT INTO `languages` (`language`) values ("Go");');

    // Commits out queries
    $pdo->commit();
}
catch (Exception $e)
{
    // Something borked, undo the queries!!
    $pdo->rollBack();
    echo 'ERROR: ' . $e->getMessage();
}

Not much to it! Forcing PDO to throw exceptions instead errors is the key to being able to use the try / catch which simplifies the logic needed to perform the rollback.