mysqli_transaction不起作用或我误解了

Question

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.

Answer 1

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);