PDO try/catch for the WHERE clause doesn't catch an error

Question

I'm using a typical PDO try/catch clause, and it works as it should except for one aspect: The WHERE clause.

Here is the code I have:

$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

try {
    $cancel_query = "UPDATE table SET active = 0 WHERE id = $request_id";
    $cancel_stmt = $pdo->prepare($cancel_query);
    $cancel_stmt->execute();

    $message = "That request was successfully canceled.";

} catch (PDOException $e) {
    $message = 'This request was not canceled. Something went wrong. ' . $e->getMessage();
}

$pdo = null;

echo $message;

This will catch the error most of the time, such as if I purposely put in the wrong table name, or column name. But if I put in a $request_id that doesn't exist, the message will indicate success. Since nothing was updated, because the WHERE clause doesn't come up with a row to update, it would be ideal in the business sense for the exception to be caught.

Is there a way for this to throw an error if no update is made?

Check https://stackoverflow.com/questions/10522520/pdo-were-rows-affected-during-execute-statement — Nigel Ren, Feb 05 '19 at 17:04
I think you need to check http://php.net/manual/en/pdostatement.rowcount.php and if there are no rows updated act accordingly — Andreas, Feb 05 '19 at 17:04

Cid · Accepted Answer · 2019-02-05T17:14:13.933

Throw yourself an exception if the update isn't successfully done (or set $message differently if you don't want to raise an exception)

$pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);

try {
    $cancel_query = "UPDATE table SET active = 0 WHERE id = $request_id";
    $cancel_stmt = $pdo->prepare($cancel_query);
    $cancel_stmt->execute();

    if ($cancel_stmt->rowCount() < 1) //Count the number of affected rows, if less than 1 then none
    {
        throw new Exception('Oh no :('); // of course, write a proper error message
        // $message = "something is wrong"; // if you don't want to throw, set $message and use an else statement for the success message
    }
    else
    {
        $message = "That request was successfully canceled.";
    }

} catch (PDOException $e) {
    $message = 'This request was not canceled. Something went wrong. ' . $e->getMessage();
}
catch (Exception $ex)
{
   $message = 'This request was canceled. Something went wrong. ' . $ex->getMessage();
}

$pdo = null;

echo $message;

Is it safe to say that if I ask for a WHERE x = $x, and that $x value doesn't exist, that this doesn't constituate an error? — Mark, Feb 05 '19 at 17:48
Yup, I did. I didn't get any error. I'm just trying to figure out the logic. I guess if you want to update, and it looks for something to update, and doesn't find it, then it did its job successfully, just with no changes. Right? — Mark, Feb 05 '19 at 21:25
Yep, right :). If the `WHERE` clause is false, then nothing happens, but that's not an error, just normal behaviour — Cid, Feb 05 '19 at 21:26

PDO try/catch for the WHERE clause doesn't catch an error

1 Answers1