Transactions with PHP Data Objects (PDO)

Josh Sherman
1 min read
Software Development PHP SQL

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.

Join the Conversation

Good stuff? Want more?

Weekly emails about technology, development, and sometimes sauerkraut.

100% Fresh, Grade A Content, Never Spam.

About Josh

Husband. Father. Pug dad. Musician. Founder of Holiday API, Head of Engineering and Emoji Specialist at Mailshake, and author of the best damn Lorem Ipsum Library for PHP.

Currently Reading

Parasie Eve

Previous Reads

Buy Me a Coffee Become a Sponsor

Related Articles