Friday, August 14, 2009

MySQL transaction (rollback+commit) - better with PHP

Lately I've been working on a project that has MySQL tables with relationship to each other. I have several functions that accesses the Database, and one function to call all these functions. However, I need to know that if one of the function that accesses the database fails, I need to do a rollback.

Here's how we can do it:

<?php
function addShop($shop){

$ok = true;
$ok = $ok && mysql_query('START TRANSACTION');

$ok = $ok && shops_AddShop($shop);
$ok = $ok && ($shop['id'] = mysql_insert_id());
$ok = $ok && shops_UpdateCategories($shop);

if($ok){
mysql_query('COMMIT');
}else{
mysql_query('ROLLBACK');
}

return $ok;
}

?>


If one of the MySQL queries failed, I can do a rollback and reverse the appropriate changes. It's much better taking advantage of the short circuit evaluation as talked about earlier on.

In that case you will have a more stable application.

No comments: