[SOLVED] Transactions in PHP !

[d.shankar]

Hi all..

 

I have a php file that consists of more than 25 queries to different tables..

If any one query fails.. i am in trouble.

 

So i need to introduce the transaction concept in my php code...

Though i have tried many variations i am still unsuccessful.. can somebody help me out ?

 

Here is the sample skeleton code how i did..

 

 

<?php

mysql_query("SET AUTOCOMMIT=0"); 
mysql_query("START TRANSACTION");

mysql_query("BEGIN");
{

/*

My 25 sql queries goes down here till the end of comment

*/



if(mysql_error())
{
mysql_query("ROLLBACK");
echo "<h1>Transaction Failed. Please Try Again.</h1>";

}
else
{
mysql_query("COMMIT");
echo "<h1>Transaction Successful.</h1>";	
}

}

?>

 

Any suggestions ?

[MasterACE14]

you've lost me.

[Daniel0]

<?php
try {
$db = new PDO('mysql:host=localhost;dbname=test', 'username', 'password');
}
catch (PDOException $e) {
die('Connection failed: ' . $e->getMessage());
}

try {
$db->beginTransaction();

$db->exec('query1');
$db->exec('query2');
// etc...

$db->commit();
}
catch (PDOException $e) {
$db->rollBack();
die('Transaction failed: ' . $e->getMessage());
}
?>

 

If one of the queries inside the second try block fail then it will throw a PDOException and go to the corresponding catch block. The actions will then be rolled back. The rest of the try block will not be executed. If all the queries execute successfully then you will make it to the call to PDO::commit() it will be committed.

 

Edit: Oh, and don't use the type of error handling (die()) I've used here. It was just for demonstration. Implement proper error handling.

 

you've lost me.

Then why bother replying?

[d.shankar]

Wow thank you admin.. will check that out..

 

Btw.. whats that PDO do i have install any extension ?

[Daniel0]

http://php.net/manual/en/book.pdo.php

 

If you're running PHP 5.1+ (which you should) then it should be enabled by default.

 

Edit: By the way, you will have to use InnoDB and not MyISAM as only the former supports transactions. That shouldn't be too much of a problem though.

[d.shankar]

Thanks a lot admin.