Wednesday 12 September 2007

MySQL Transaction in PHP (Commit & Rollback)




We all know that an SQL query will either execute completely or not at all (i.e. they are atoms). But, sometimes, we want several queries to be bundled together as response to business logic. Transaction processing will help you to ensure this and maintain database integrity.

If you are new to MySQL transaction with PHP, then this article will definitely help. :-)

Step One - Make sure the type of your database table is innoDB not MyISAM.
(Here is a screenshot of from phpMyAdmin)

Step Two - Start transaction $dbh->query('BEGIN');
$dbh is an pearDB object ($dbh = new pearDB(), if you are not familiar with pearDB, please click here.)

Step Three - Specify your queries which reflect the business logic. ($sqlA and $sqlB)
Step Four - 'ROLLBACK' to cancel $sqlA and $sqlB, and 'COMMIT' to confirm the changes.

Code Example:

(Click the image to Enlarge)


If you have any question or suggestion, feel free contact me on twitter: http://twitter.com/alexzang

No comments: