0

Using PHP PDO, is there a way to distinguish the difference between an UPDATE failing, and an UPDATE running successfully but setting a field to its existing value?

$sql = 'UPDATE table SET column = :column WHERE id = :id';
$statement = $db->prepare($sql);
$statement->bindParam(':column', $column, PDO::PARAM_STR);
$statement->bindParam(':id', $id, PDO::PARAM_INT);
$statement->execute();
$RowsUpdated = $statement->rowCount();
echo '$RowsUpdated = '.$RowsUpdated.'<br />';

The above code sets $RowsUpdated to 1 if the value of $column is a new value, and sets it to 0 if it's the same as the existing value. I tried to force an error by altering the SQL statement, but it never returns a non-numeric value (such as NULL or False) and is never less than 0.

When SELECT'ing rows with COUNT(*), fetchColumn() returns the number of rows if there are any, or 0 if there aren't any, or something else if there's an error. So I'm able to use ($countRowsSelected > 0) to detect rows, ($countRowsSelected === "0") to detect no rows, and ELSE to detect an error.

Is it possible to do this with an UPDATE? In addition to the UPDATE, I want to trigger secondary actions but only if the saved value has CHANGED, so I want this:

if ($RowsUpdated > 0) {
    // We go a NEW value, let's do a little dance
} else if ($RowsUpdated === "0") {
    // It saved, but the value was unchanged
} else {
    // Error!
}

I tried adding PDO::MYSQL_ATTR_FOUND_ROWS => true to my PDO, but this caused rowCount() to return 1 regardless of whether or not the value changed, so it didn't help.

Ed Brissenden
  • 177
  • 2
  • 13
  • a failed statement would return a boolean false or throw an exception, depending on your pdo settings. a query which doesn't update (or match) anything is not a failure - it's just an empty result set. – Marc B Jan 28 '13 at 19:21
  • UPDATE failing maybe you must use ACID transactions - with try catch you can inspect when it fails. – Ragen Dazs Jan 28 '13 at 19:22
  • I was using ERRMODE_SILENT which I thought was best/safest for live environments, but I guess that may have been suppressing the error (?) although I was still able to get 1, 0 or NULL from a SELECT with that setting, just not with an UPDATE. It would be useful to know where the failure is, rather than just catching an error *somewhere* in the PDO. Meaning, it would be good to identify the error was around the UPDATE, by returning 1+, 0 or NULL for the rows UPDATED. – Ed Brissenden Jan 28 '13 at 19:55

1 Answers1

0

You could do one of the following:

  1. Use PDO::errCode(), respectively PDOStatement::errorCode(), to check if a SQL error occurred, or
  2. Initialize your database connection with the PDO::ATTR_ERRMODE set to PDO::ERRMODE_EXCEPTION (see manual). This way PDO throws an exception that you can catch.
helmbert
  • 35,797
  • 13
  • 82
  • 95
  • Do you favour either of these approaches over the other? I'm thinking ERRMODE_EXCEPTION may be the way to go. Is it safe to use in live environments if I only output a custom message (rather than $e->getMessage() of the PDOException)? It appears the try/catch will detect any error inside the PDO, whether it's in the initial SELECT, or the subsequent UPDATE, so I think it'll cater for ANY error up to the final '$db = null;' statement. If this is the case, I don't need a separate error for the UPDATE, just one overall error for any failure, which is great. Can you confirm this? – Ed Brissenden Jan 28 '13 at 19:50
  • I prefer `ERRMODE_EXCEPTION`. Checking for errors using `errorCode()` after each and every query is easy to forget (making errors that you forgot to check for even more surprising). In my opinion, using Exceptions makes it easier to track down errors. Using this approach in a live environment is usually not a problem, since you can catch the exception and treat it at your own discretion (e.g. print it along with the call stack in your development environment, and simply print a generic error message in your live environment). – helmbert Jan 28 '13 at 20:03