2

I have written a code to turn off AutoCommit :

<?php 
$con = mysqli_connect("localhost","root","","databases");
if (mysqli_connect_errno($con))
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
if(mysqli_query($con,"SET autocommit=0")){//tried using mysqli_autocommit($con,FALSE);

// Insert some values 
$a=mysqli_query($con,"INSERT INTO member VALUES (2,'Peter','Peter')");
$b=mysqli_query($con,"INSERT INTO member VALUES (3,honey,'honey')");
echo "a:";
echo $a;
echo "b:";
echo $b;
if($a&&$b){
mysqli_query($con,"COMMIT");//tried using mysqli_commit($con);
echo "in if";
}
else{
mysqli_query($con,"ROLLBACK");//tried using mysqli_rollback($con);
echo "in else";
}
}
mysqli_close($con);
?>

output of this code is : a:1 b: in else

But in the table, The row First row is being inserted. I used wamp for this. Please let me know where I am going wrong

user2022887
  • 131
  • 2
  • 3

3 Answers3

0

I'm using this functions for transaction starts, commits and rollbacks:

function transactionStart($dbConnection){
    return $dbConnection->autocommit(false);
}
function transactionCommit($dbConnection) {
    $dbConnection->commit();
    return  $dbConnection->autocommit(true);
}
function transactionRollback($dbConnection) {
     $dbConnection->rollback();
     return $dbConnection->autocommit(true);
}

A little bit of pseude code for your specific case:

//TODO: Build up connection, results in $con variable
transactionStart($con);
//TODO: Insert data
if(*all data inserted correctly*){
  transactionCommit($con);
} else{
  transactionRollback($con);
}
Michael Kunst
  • 2,978
  • 25
  • 40
0

You need to start a transaction before issuing a series of sql commands that constitute a transaction.

 START TRANSACTION

will do it in SQL. Or you can use 

$con->autocommit ( false );
$con->begin_transaction(); 
... transactional SQL statements ...
$con->commit();

to do this cleanly.

PHP + MySQL transactions examples

@Sylvain Leroux is right! If you're using MyISAM, you can issue transaction commands all you want and they still won't work. The transaction support is in InnoDB.

Community
  • 1
  • 1
O. Jones
  • 103,626
  • 17
  • 118
  • 172
  • 1
    As a complement, you should of course use transactional tables (i.e.: InnoDB) – Sylvain Leroux Aug 14 '13 at 16:28
  • I came back to this answer: I now have a doubt. Once auto_commit is set to false, you don't have to start transaction explicitly. To quote [the doc](http://dev.mysql.com/doc/refman/5.0/en/commit.html): "To disable autocommit mode implicitly for a single series of statements, use the START TRANSACTION statement". Either you have to disable auto_commit globally *or* temporary by using `START TRANSACTION`. No? – Sylvain Leroux Aug 14 '13 at 16:32
0

I think your second column is a varchar. Your second insert statement are missing the quotes for the second column. Please replace the insert query like

$a = mysqli_query($con,"INSERT INTO member VALUES (2,'Peter','Peter')");
$b = mysqli_query($con,"INSERT INTO member VALUES (3,'honey','honey')");
Ryan Vincent
  • 4,483
  • 7
  • 22
  • 31
Naisam Uthakkan
  • 1