Skip to Main Content
MySQL Cookbook
book

MySQL Cookbook

by Paul DuBois
October 2002
Intermediate to advanced content levelIntermediate to advanced
1024 pages
27h 26m
English
O'Reilly Media, Inc.
Content preview from 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, ...

Become an O’Reilly member and get unlimited access to this title plus top books and audiobooks from O’Reilly and nearly 200 top publishers, thousands of courses curated by job role, 150+ live events each month,
and much more.
Start your free trial

You might also like

MySQL Reference Manual

MySQL Reference Manual

Michael Widenius, David Axmark, Kaj Arno
High Performance MySQL

High Performance MySQL

Jeremy D. Zawodny, Derek J. Balling
MySQL Stored Procedure Programming

MySQL Stored Procedure Programming

Guy Harrison, Steven Feuerstein

Publisher Resources

ISBN: 0596001452Catalog PageErrata