MySQL Cookbook, 2nd Edition

Using Transactions in PHP Programs

Problem

You want to perform a transaction in a PHP script.

Solution

Use the standard PEAR DB transaction support mechanism.

Discussion

The PEAR DB module supports a transaction abstraction that can be used to perform transactions. Use the autoCommit() method to disable auto-commit mode. Then, after issuing your statements, invoke either commit() or rollback() to commit or roll back the transaction.

The following code uses exceptions to signal transaction failure, which means that PHP 5 is required. (Earlier versions of PHP do not support exceptions.) The PEAR DB transaction-handling methods do not raise exceptions themselves when they fail, so the example program uses status checking within the try block to determine when to raise its own exception:

try { $result =& $conn->autoCommit (FALSE); if (PEAR::isError ($result)) throw new Exception ($result->getMessage ()); $result =& $conn->query ( "UPDATE money SET amt = amt - 6 WHERE name = 'Eve'"); if (PEAR::isError ($result)) throw new Exception ($result->getMessage ()); $result =& $conn->query ( "UPDATE money SET amt = amt + 6 WHERE name = 'Ida'"); if (PEAR::isError ($result)) throw new Exception ($result->getMessage ()); $result =& $conn->commit (); if (PEAR::isError ($result)) throw new Exception ($result->getMessage ()); $result =& $conn->autoCommit (TRUE); if (PEAR::isError ($result)) throw new Exception ($result->getMessage ()); } catch (Exception $e) { print ("Transaction failed: " . $e->getMessage ...