PHP try/catch with SQL. All or Nothing

Question

I need to make changes in two databases at the same time, but I don't want either of them to go through if either fails.

This block of code attempts to update the password for a user in two separate databases. The first query goes, then the database is switched, then the second query goes. The try/catch block will fire if there is an error, however if the first query is successful and the second query is not, the first query still goes through despite the exception being thrown and caught. Is try/catch not the right way to approach this?

Thanks for any input!

                try {

                    $new_hash = password_encrypt($new_password);

                    $query = "UPDATE user SET ";
                    $query .= "user.hashedPassword = '{$new_hash}' ";
                    $query .= "WHERE user.userID = '{$user_id}' ";

                    if(!mysqli_query($connection, $query)) {
                        throw new Exception('Change Unsuccessful.');
                    }

                    if(!mysqli_select_db($connection, 'sub_' . $user['username'] . '_db')) {
                        throw new Exception('Change Unsuccessful. Switch Error.');
                    }

                    $query = "UPDATE user SET ";
                    $query .= "user.hashedPassword = '{$new_hash}' ";
                    $query .= "WHERE user.userID = '{$user_id}' ";

                    if(!mysqli_query($connection, $query)) {
                        throw new Exception('Change Unsuccessful.');

                    }

                    $_SESSION['message'] = 'Password Changed.';
                    $_SESSION['messageType'] = 'success';

                    redirect_to("/main.php");

                } catch(Exception $e) {
                    $_SESSION['message'] = $e->getMessage();
                    $_SESSION['messageType'] = 'danger';
                    redirect_to("/main.php");
                }

score 1 · Answer 1 · answered Sep 25 '18 at 15:25

I know this is old, but I came across it via a DuckDuckGo search first. Here is what I think to be the right answer;

Use SQL transactions.

In your case, you must start the transactions for both databases, and if either database throws an exception, roll them both back.

At the end of the day, transaction allows you to actually try and complete or undo the command.

score 0 · Answer 2 · answered Nov 05 '14 at 14:26

0

you can try to add

if (!$mysqli->query("query")) 
{
        printf("Error: %s\n", $mysqli->error);
}

and see if there's any error

answered Nov 05 '14 at 14:26

Daniel Krom

9,751
3
43
44

PHP try/catch with SQL. All or Nothing

2 Answers2