Rolling back MySQL queries

Question

If I have multiple queries on chain, on a structure based on IFs, like this:

$query1 = mysqli_query("query here");

if(!query1){
    //display error
} else {
    $query2 = mysqli_query("another query here");
    if(!query2){
        //display error
        //rollback the query1
    } else {
        query3 = mysqli_query("yet again another query");
        if(!query3) {
            //display error
            //rollback the query2
            //rollback the query1
        } else {
            query4 = mysqli_query("eh.. another one");
            if(!query4){
                //display error
                //rollback the query3
                //rollback the query2
                //rollback the query1
            } else {
                return success;
            }
        }
    }
}

Is there a best way to rollback the previous query, if the next one fails? Otherwise I'm gonna have the first 2 query successfull, which edited the database, but the 3° failed, so 3° and 4° didn't edit the dabatase, with the result of having it corrupted.

I thought about something like:

    ...
    $query2 = mysqli_query("another query here");
    if(!query2){
        //display error
        $rollback = mysqli_query("query to rollback query1");
    } else {
        query3 = mysqli_query("yet again another query");
        if(!query3) {
            //display error
            $rollback = mysqli_query("query to rollback query2");
            $rollback = mysqli_query("query to rollback query1");
        } else {
        ...

But the above method grants even more chances to fail more queries. Is there any other more effective methods?

To 'rollback' any query, use begin tran first and the use of commit or rollback, I advice you to search for transactions — Neobugu, Mar 10 '17 at 17:50
You can start here: https://dev.mysql.com/doc/refman/5.7/en/commit.html — Neobugu, Mar 10 '17 at 17:51
You never passed the db connection to any of your queries btw, should that be next to actual syntax/code. — Funk Forty Niner, Mar 10 '17 at 18:00

score 2 · Answer 1 · edited May 23 '17 at 12:09

This is how i would do it with mysqli:

Configure mysqli (somewehere at the begining of your application) to throw exceptions when a query fails.

mysqli_report(MYSQLI_REPORT_STRICT);

This way you will not need all the if .. elseif .. else.

$connection->begin_transaction();
try {
    $result1 = $connection->query("query 1");
    // do something with $result1

    $result2 = $connection->query("query 2");
    // do something with $result2

    $result3 = $connection->query("query 3");
    // do something with $result3

    // you will not get here if any of the queries fails
    $connection->commit();
} catch (Exception $e) {
    // if any of the queries fails, the following code will be executed
    $connection->rollback(); // roll back everything to the point of begin_transaction()
    // do other stuff to handle the error
}

Update

Usually the user don't care about, why his action failed. If a query fails, it's never the users fault. It's either the fault of the developer or of the environment. So there shouldn't be a reason to render an error message depending on which query failed.

Note that if the users intput is the source of the failed query, then

you didn't validate the input properly
your queries are not injection safe (If the input can cause an SQL error it can also be used to compromise your DB.)

However - I don't say there can't be reasons - I just don't know any. So if you want your error message depend on which query failed, you can do the following:

$error = null;
$connection->begin_transaction();
try {
    try {
        $result1 = $connection->query("query 1");
    } catch (Exception $e) {
        $error = 'query 1 failed';
        throw $e;
    }
    // do something with $result1

    try {
        $result2 = $connection->query("query 2");
    } catch (Exception $e) {
        $error = 'query 2 failed';
        throw $e;
    }
    // do something with $result2

    // execute more queries the same way

    $connection->commit();
} catch (Exception $e) {
    $connection->rollback();
    // use $error to find out which query failed
    // do other stuff to handle the error
}

Mmm this is interesting, but, how do I know on exception if it failed the result2 instead of 1 for example? — Net Jacker, Mar 10 '17 at 18:16
@NetJacker You don't know it at the runtime. This follows the rule "everything or nothing". If anything fails - roll back all changes. You can analyse the error message in the error log later and fix the source of the error. — Paul Spiegel, Mar 10 '17 at 18:22
This is the correct answer. You don't need to know which query failed, but you will since exception will contain the message about the error. This is the proper way and hence +1 from me. — N.B., Mar 10 '17 at 18:42

Rolling back MySQL queries

1 Answers1