Transactional support has long been on the wish list of most MySQL developers and, with the release of MySQL 4.0, this wish was finally granted. Not long after MySQL 4.0, PHP 5.x was released with a new MySQL extension, MySQL Improved, which allowed PHP developers to access these new transactional capabilities using native PHP functions. This brief tutorial will show you how to use these MySQLi functions to implement MySQL-based transactions with PHP.

Overview

In case you don't already know, a transaction is simply a block of SQL statements to be executed in all-or-nothing mode, usually because they are inter-dependent on each other. A transaction is said to be successful only if all its constituent statements are executed successfully; a failure in any one should cause the system to be "rolled back" to its pre-existing state to avoid data linkage/corruption problems.

A good example of this is a money transfer between two bank accounts. At the database level, such a transfer involves two steps: first, subtracting the transfer sum from balance of the source account and then adding it to the balance of the target account. If an error occurs in the second step, then the first step must be reversed to avoid a mismatch (and mobs of angry customers). A transaction-safe system would automatically perform this reversal to an earlier system "snapshot".

Most databases (including MySQL) accomplish this through a mixture of commands:• The START TRANSACTION command marks the beginning of a new transaction block. It's usually followed by a series of SQL commands.• The COMMIT command marks the end of a transaction block and signals that all changes made during the transaction should be "committed" or made permanent.• The ROLLBACK command marks the end of a transaction block and signals that all changes made during the transaction should be reversed.

Transactional functions in PHP

PHP's MySQLi extension introduces new functions to help developers leverage MySQL's transactional capabilities. Essentially, these functions are equivalent to calling the SQL START TRANSACTION, COMMIT and ROLLBACK commands. Listing A shows you an example.

Implement MySQL-based transactions a new set of PHP extensionsListing A

<?php

// connect to database $dbh = mysqli_connect($host, $user, $pass, $db); // turn off auto-commit mysqli_autocommit($dbh, FALSE); // run query 1 $result = mysqli_query($dbh, $query1); if ($result !== TRUE) { mysqli_rollback($dbh); // if error, roll back transaction } // run query 2 $result = mysqli_query($dbh, $query2); if ($result !== TRUE) { mysqli_rollback($dbh); // if error, roll back transaction } // and so on... // assuming no errors, commit transaction mysqli_commit($dbh); // close connection mysqli_close($dbh);

?>

There are three basic steps to implementing a transaction in PHP:

1. The first step is always to turn off database "auto-commit", which essentially means that the system saves your changes as you make them. This is important because in a transactional environment, you should only save your changes after you're sure that all the transactional "units" have been successfully completed. You can turn off database auto-commit through the mysqli_autocommit() function.

2. Next, proceed to issue INSERT, UPDATE and/or DELETE queries in the usual way, via the mysqli_query() function. It's important to test the return value of each query to see whether or not it succeeded. In the event that any of the queries fails, the mysqli_rollback() function is used to return the system to its pre-transaction state.

3. Assuming all the commands that make up the transaction block are successfully executed, use the mysqli_commit() function to save the changes to the database system. Note that the transaction cannot be reversed once this function is called.

http://whitepapers.zdnet.com/abstract.aspx?docid=235832&promo=200111