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, ...