MySQL Cookbook

Using Transactions in PHP Programs

Problem

You want to perform a transaction in a PHP script.

Solution

Issue the SQL statements that begin and end transactions.

Discussion

PHP provides no special transaction mechanism, so it’s necessary to issue the relevant SQL statements directly. This means you can either use BEGIN to start a transaction, or disable and enable the auto-commit mode yourself using SET AUTOCOMMIT. The following example uses BEGIN. The statements of the transaction are placed within a function to avoid a lot of messy error checking. To determine whether or not to roll back, it’s necessary only to test the function result:

function do_queries ($conn_id)
{
    # move some money from one person to the other
    if (!mysql_query ("BEGIN", $conn_id))
        return (0);
    if (!mysql_query ("UPDATE money SET amt = amt - 6 WHERE name = 'Eve'", $conn_id))
        return (0);
    if (!mysql_query ("UPDATE money SET amt = amt + 6 WHERE name = 'Ida'", $conn_id))
        return (0);
    if (!mysql_query ("COMMIT", $conn_id))
        return (0);
    return (1);
}

if (!do_queries ($conn_id))
{
    print ("Transaction failed, rolling back. Error was:\n"
            . mysql_error ($conn_id) . "\n");
    mysql_query ("ROLLBACK", $conn_id);
}

The do_queries( ) function tests each method and returns failure if any of them fail. That style of testing lends itself to situations in which you may need to perform additional processing between statements or after executing the statements and before returning success. For the example shown, no other processing is necessary, ...