CODEWITHSUNDEEP
phpmysqlitransactions
user3847937
user3847937

Reputation: 109

mysqli_transaction is not working or I misundersatnd

MY php code for mysqli transaction is 

<?php

/*db connection*/

$conn = mysqli_connect("localhost", "root", "", "test");

mysqli_autocommit($conn, FALSE);

$insert1 = mysqli_query($conn, "INSERT INTO user(name,age,gender) VALUES('DEEPU', 24, 'male')");

$insert2 = mysqli_query($conn, "INSERT INTO user(name,age,gender2) VALUES('ANIL', 25, 'male')");

mysqli_commit($conn);

mysqli_close($conn);

?>

I know mysqli transaction is used for, If any operation within the transaction fails, the entire transaction will fail.

Problem:

In my code second query is wrong, it uses gender2 instead of gender. Since one query is failed, the entire transaction should fail. But here the first query execute and data is inserted in table. I expect no data will insert in table because the second query is fail.

Is any mistake in my code?? OR I totally misunderstand the point.

If I use

 mysqli_rollback($conn);

it brings to the pre-transactional state.

NOTE:I am using innoDB engine.

What I need

If both query is correct: data should insert.

If one is correct and other is wrong: no data will insert.

Pease help...Thank you.

Upvotes: 0

Views: 79

Answers (1)

Rimble
Rimble

Reputation: 893

mysqli_query returns true on succesful query. You can use this to commit if both queries succeed and else rollback if either fails.

$conn = mysqli_connect("localhost", "root", "", "test");

mysqli_autocommit($conn, FALSE);

$query1 = "INSERT INTO user(name,age,gender) VALUES('DEEPU', 24, 'male')";    
$query2 = "INSERT INTO user(name,age,gender2) VALUES('ANIL', 25, 'male')";

if(mysqli_query($conn, $query1) && mysqli_query($conn, $query2))
{
     mysqli_commit($conn);
}else
{
     mysqli_rollback($conn);
}

mysqli_close($conn);

Upvotes: 2

Related Questions