PHP MySQL Transaction catch error

Question

I have been looking for two days now and still haven't found the answer. Suppose I have underneath code in PHP:

$mysqli->begin_transaction();
mysqli_query($mysqli, "DELETE FROM Test WHERE ID=1");
mysqli_query($mysqli, "DELETE FROM TEST WHEREE ID=2"); <-- THIS ONE WILL FAIL BECAUSE OF TYPO
mysqli_query($mysqli, "DELETE FROM Test WHERE ID=3");
if ($mysqli->commit()) {
    //SUCCESS
    }
else {
    //Failed        
    $mysqli->rollback();
    }

I am not able to check whether the queries within the transaction all have succeeded, because when I execute these queries, the commit function always returns true.

How can I check whether all queries within the transaction have succeeded?

Answer 1

You can use mysqli_error for catching the error.

$startTrans = 'START TRANSACTION;';  // Starting a mysql transaction
mysqli_query($conn, $startTrans); 

$query = 'Insert into students (roll_no,name,class) VALUES (101,"mathew","fourth")';
$result = mysqli_query($conn, $query);
if (mysqli_error($conn)) {
   // Rollback can be done here 
   mysqli_query($conn, "ROLLBACK;"); // All above queries will get rolled back
   die(mysqli_error($conn)); 
} else {
   mysqli_query($conn, "COMMIT;"); // changes will get saved to database
}