CODEWITHSUNDEEP
phpmysqltry-catch
Guy
Guy

Reputation: 876

PHP try/catch with SQL. All or Nothing

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

Upvotes: 1

Views: 1021

Answers (2)

Chad
Chad

Reputation: 1189

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.

Upvotes: 1

Daniel Krom
Daniel Krom

Reputation: 10068

you can try to add 

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

and see if there's any error

Upvotes: 0

Related Questions