CODEWITHSUNDEEP
phpmysqltransactionscommitrollback
TVA van Hesteren
TVA van Hesteren

Reputation: 1249

PHP MySQL Transaction catch error

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?

Upvotes: 1

Views: 3689

Answers (2)

Oleg B
Oleg B

Reputation: 124

Common way to do this:

$mysqli->begin_transaction();
try {
    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");
    $mysqli->commit();
} catch (\Exception $e) {
    // this will show statement with error
    echo $e->getMessage();
    $mysqli->rollback();
    throw $e;
}

Upvotes: 3

jagjeet
jagjeet

Reputation: 377

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
}

Upvotes: -1

Related Questions