Using Transactions in PHP Programs

15.6.1 Problem

You want to perform a transaction in a PHP script.

15.6.2 Solution

Issue the SQL statements that begin and end transactions.

15.6.3 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:
"
 . mysql_error ($conn_id) . "
");
 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, so do_queries( ) could be reimplemented as a single long expression:

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

Using the mysql Client Program

Writing MySQL-Based Programs

Record Selection Techniques

Working with Strings

Working with Dates and Times

Sorting Query Results

Generating Summaries

Modifying Tables with ALTER TABLE

Obtaining and Using Metadata

Importing and Exporting Data

Generating and Using Sequences

Using Multiple Tables

Statistical Techniques

Handling Duplicates

Performing Transactions

Introduction to MySQL on the Web

Incorporating Query Resultsinto Web Pages

Processing Web Input with MySQL

Using MySQL-Based Web Session Management

Appendix A. Obtaining MySQL Software

Appendix B. JSP and Tomcat Primer

Appendix C. References



MySQL Cookbook
MySQL Cookbook
ISBN: 059652708X
EAN: 2147483647
Year: 2005
Pages: 412
Authors: Paul DuBois

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net