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?

Could you give me an example? How should I catch a unique error message for each query? Do I have to check each query separately in the transaction, will this work? — TVA van Hesteren
– TVA van Hesteren, Commented Apr 2, 2017 at 18:04

Oleg B · Accepted Answer · 2022-02-13 09:31:46Z

3

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

edited Feb 13, 2022 at 9:31

answered Apr 2, 2017 at 18:39

Oleg B

1243 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

TVA van Hesteren Over a year ago

I tried this already, but this will commit since it won't catch an exception?

Michael - sqlbot Over a year ago

This doesn't make sense. $mysqli->commit(); should be immediately before catch so that if anything fails, whether query or commit, we make a desperate attempt to try to roll back.

Oleg B Over a year ago

Yes you're right, in common there may be errors while commit, but it's very specific errors in concurrence queries, not this case with invalid syntax

jagjeet · Accepted Answer · 2019-06-17 10:34:49Z

-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
}

answered Jun 17, 2019 at 10:34

jagjeet

3774 silver badges13 bronze badges

Collectives™ on Stack Overflow

PHP MySQL Transaction catch error

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related