# PHP + MySQL transactions examples

## The Question :

297 people think this question is useful

I really haven’t found normal example of PHP file where MySQL transactions are being used. Can you show me simple example of that?

And one more question. I’ve already done a lot of programming and didn’t use transactions. Can I put a PHP function or something in header.php that if one mysql_query fails, then the others fail too?

I think I have figured it out, is it right?:

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

$a1 = mysql_query("INSERT INTO rarara (l_id) VALUES('1')");$a2 = mysql_query("INSERT INTO rarara (l_id) VALUES('2')");

if ($a1 and$a2) {
mysql_query("COMMIT");
} else {
mysql_query("ROLLBACK");
}


The Question Comments :
• You can use mysql_query("BEGIN"); instead of sequence mysql_query("SET AUTOCOMMIT=0"); mysql_query("START TRANSACTION");
• Please, don’t use mysql_* functions in new code. They are no longer maintained and are officially deprecated. See the red box? Learn about prepared statements instead, and use PDO or MySQLithis article will help you decide which. If you choose PDO, here is a good tutorial.
• Does “mysql_query(“SET AUTOCOMMIT=0″);” set all connections to wait for commit function or it’s just for its related connection?
• @Neal, Actually mysql wun die despite being deprecated, it will be available in PECL forever.
• @Pacerier Things that get deprecated don’t “die”. They are held officially for legacy software but cease to be maintained and stricken from any recommended practices for new software. The fact remains, don’t use mysql

## The Answer 1

333 people think this answer is useful

The idea I generally use when working with transactions looks like this (semi-pseudo-code):

try {
// First of all, let's begin a transaction
$db->beginTransaction(); // A set of queries; if one fails, an exception should be thrown$db->query('first query');
$db->query('second query');$db->query('third query');

// If we arrive here, it means that no exception was thrown
// i.e. no query has failed, and we can commit the transaction
$db->commit(); } catch (\Throwable$e) {
// An exception has been thrown
// We must rollback the transaction
$db->rollback(); throw$e; // but the error must be handled anyway
}



Note that, with this idea, if a query fails, an Exception must be thrown:
• PDO can do that, depending on how you configure it
• else, with some other API, you might have to test the result of the function used to execute a query, and throw an exception yourself.

Unfortunately, there is no magic involved. You cannot just put an instruction somewhere and have transactions done automatically: you still have to specific which group of queries must be executed in a transaction.

For example, quite often you’ll have a couple of queries before the transaction (before the begin) and another couple of queries after the transaction (after either commit or rollback) and you’ll want those queries executed no matter what happened (or not) in the transaction.

## The Answer 2

114 people think this answer is useful

I think I have figured it out, is it right?:

mysql_query("START TRANSACTION");

$a1 = mysql_query("INSERT INTO rarara (l_id) VALUES('1')");$a2 = mysql_query("INSERT INTO rarara (l_id) VALUES('2')");

if ($a1 and$a2) {
mysql_query("COMMIT");
} else {
mysql_query("ROLLBACK");
}



## The Answer 3

40 people think this answer is useful
<?php

// trans.php
function begin(){
mysql_query("BEGIN");
}

function commit(){
mysql_query("COMMIT");
}

function rollback(){
mysql_query("ROLLBACK");
}

mysql_connect("localhost","Dude1", "SuperSecret") or die(mysql_error());

mysql_select_db("bedrock") or die(mysql_error());

$query = "INSERT INTO employee (ssn,name,phone) values ('123-45-6789','Matt','1-800-555-1212')"; begin(); // transaction begins$result = mysql_query($query); if(!$result){
rollback(); // transaction rolls back
echo "transaction rolled back";
exit;
}else{
commit(); // transaction is committed
echo "Database transaction was successful";
}

?>



## The Answer 4

38 people think this answer is useful

As this is the first result on google for “php mysql transaction”, I thought I’d add an answer that explicitly demonstrates how to do this with mysqli (as the original author wanted examples). Here’s a simplified example of transactions with PHP/mysqli:

// let's pretend that a user wants to create a new "group". we will do so
// while at the same time creating a "membership" for the group which
// consists solely of the user themselves (at first). accordingly, the group
// and membership records should be created together, or not at all.
// this sounds like a job for: TRANSACTIONS! (*cue music*)

$group_name = "The Thursday Thumpers";$member_name = "EleventyOne";
$conn = new mysqli($db_host,$db_user,$db_passwd,$db_name); // error-check this // note: this is meant for InnoDB tables. won't work with MyISAM tables. try {$conn->autocommit(FALSE); // i.e., start transaction

// assume that the TABLE groups has an auto_increment id field
$query = "INSERT INTO groups (name) ";$query .= "VALUES ('$group_name')";$result = $conn->query($query);
if ( !$result ) {$result->free();
throw new Exception($conn->error); }$group_id = $conn->insert_id; // last auto_inc id from *this* connection$query = "INSERT INTO group_membership (group_id,name) ";
$query .= "VALUES ('$group_id','$member_name')";$result = $conn->query($query);
if ( !$result ) {$result->free();
throw new Exception($conn->error); } // our SQL queries have been successful. commit them // and go back to non-transaction mode.$conn->commit();
$conn->autocommit(TRUE); // i.e., end transaction } catch ( Exception$e ) {

// before rolling back the transaction, you'd want
// to make sure that the exception was db-related
$conn->rollback();$conn->autocommit(TRUE); // i.e., end transaction
}



Also, keep in mind that PHP 5.5 has a new method mysqli::begin_transaction. However, this has not been documented yet by the PHP team, and I’m still stuck in PHP 5.3, so I can’t comment on it.

## The Answer 5

11 people think this answer is useful

Please check which storage engine you are using. If it is MyISAM, then Transaction('COMMIT','ROLLBACK') will not be supported because only the InnoDB storage engine, not MyISAM, supports transactions.

## The Answer 6

8 people think this answer is useful

When using PDO connection:

$pdo = new PDO('mysql:host=localhost;dbname=mydb;charset=utf8',$user, $pass, [ PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, // this is important ]);  I often use the following code for transaction management: function transaction(Closure$callback)
{
global $pdo; // let's assume our PDO connection is in a global var // start the transaction outside of the try block, because // you don't want to rollback a transaction that failed to start$pdo->beginTransaction();
try
{
$callback();$pdo->commit();
}
catch (Exception $e) // it's better to replace this with Throwable on PHP 7+ {$pdo->rollBack();
throw $e; // we still have to complain about the exception } }  Usage example: transaction(function() { global$pdo;

$pdo->query('first query');$pdo->query('second query');
$pdo->query('third query'); });  This way the transaction-management code is not duplicated across the project. Which is a good thing, because, judging from other PDO-ralated answers in this thread, it’s easy to make mistakes in it. The most common ones being forgetting to rethrow the exception and starting the transaction inside the try block. ## The Answer 7 5 people think this answer is useful I made a function to get a vector of queries and do a transaction, maybe someone will find out it useful: function transaction ($con, $Q){ mysqli_query($con, "START TRANSACTION");

for ($i = 0;$i < count ($Q);$i++){
if (!mysqli_query ($con,$Q[$i])){ echo 'Error! Info: <' . mysqli_error ($con) . '> Query: <' . $Q[$i] . '>';
break;
}
}

if ($i == count ($Q)){
mysqli_query($con, "COMMIT"); return 1; } else { mysqli_query($con, "ROLLBACK");
return 0;
}
}



## The Answer 8

4 people think this answer is useful

I had this, but not sure if this is correct. Could try this out also.

mysql_query("START TRANSACTION");
$flag = true;$query = "INSERT INTO testing (myid) VALUES ('test')";

$query2 = "INSERT INTO testing2 (myid2) VALUES ('test2')";$result = mysql_query($query) or trigger_error(mysql_error(), E_USER_ERROR); if (!$result) {
$flag = false; }$result = mysql_query($query2) or trigger_error(mysql_error(), E_USER_ERROR); if (!$result) {
$flag = false; } if ($flag) {
mysql_query("COMMIT");
} else {
mysql_query("ROLLBACK");
}



Idea from here: http://www.phpknowhow.com/mysql/transactions/

## The Answer 9

1 people think this answer is useful

One more procedural style example with mysqli_multi_query, assumes $query is filled with semicolon-separated statements. mysqli_begin_transaction ($link);

for (mysqli_multi_query ($link,$query);
mysqli_more_results ($link); mysqli_next_result ($link) );

! mysqli_errno ($link) ? mysqli_commit ($link) : mysqli_rollback (\$link);