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, ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.