09-Aug-2013 in Software Development #PHP #SQL

Using Transactions with PHP Data Objects (PDO)

With PHP Data Objects, transactions are pretty simple. To be able to use a transaction, you simply wrap your queries with a call to beginTransaction() and either use commit() or rollBack() at the end. Rolling back is at your discretion but the easiest implementation would be to wrap the queries in a try {...} and issue rollBack() as part of the catch {...} as follows:

try
{
	$pdo->beginTransaction();
$query = $pdo->prepare('INSERT INTO table (column1, column2) VALUES (?, ?);');
$query->execute(array($value1, $value2));
$pdo->exec('UPDATE table2 SET column = false WHERE some_flag = false');
$pdo->commit();
}
catch (PDOException $exception)
{
$pdo->rollBack();
echo 'OH NOES: ' . $exception->getMessage();
}

As you can see, you can execute queries directly or as prepared statements within the transaction. Please keep in mind that some queries will force an auto-commit nullifying the transaction and removing the ability to rollback. Such queries include DROPing and TRUNCATEing tables. The command that force the auto-commit vary between database systems so be sure to test your code and/or write unit cases to handle the negative use cases!