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.