PHP PDO MySQL Correct way to check if an update query succeeded when no rows are affected

Question

What is a sure way to tell if an update query succeeded when using php pdo and mysql?

In my app, I update totals of items that are submitted but a user, and the table looks like this:

items

userId | itemsAdded | itemsChecked | itemsUnChecked | itemsTotal
     1 |          5 |            2 |              3 |          5

So when I do update items set itemTotals = itemsChecked+itemUnChecked the itemsTotal column remains the same unless the itemsAdded changes and the itemsUnChecked increments (2 + 3 equals 5, 1 + 4 is also 5).

I used to use rowCount() to check if a query succeeded, but in this case, since the itemsTotal column stays the same, there's no way of telling if the sql succeeded or not.

$query = $conn->prepare($sql);

$query->execute(array(

    ":itemCount" => $itemCount
    ":itemId" => $itemId
));

$queryCount = $query->rowCount();

if($queryCount == 1) {
    echo 'Updated succeeded';
} else {
    echo 'Updated failed!';
}

I could also use:

$query = $conn->prepare($sql);

$result = $query->execute(array(

    ":itemCount" => $itemCount
    ":itemId" => $itemId
));

if($result) {
    echo 'Updated succeeded';
} else {
    echo 'Updated failed!';
}

But does that return true or false based on if the query succeed or based on the number of rows it updated?

I only need to check if the query succeeded or not. No need to tell the number of rows that were updated.

possible duplicate of [How to tell when query executed successfully in PHP PDO?](http://stackoverflow.com/questions/9521228/how-to-tell-when-query-executed-successfully-in-php-pdo) — backtrack, Apr 21 '15 at 14:00

spencer7593 · Accepted Answer · 2018-05-08T21:59:23.980

7

The execute() method will either throw an exception or return FALSE (depending on the error mode you have set for the database connection) when the execution of a SQL statement fails.

If we set the error mode to throw an exception, prior to executing a statement, (usually immediately after establishing a database connection), like this

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

Then we can use a try/catch block to handle an exception thrown by a SQL statement. Something like this:

try {
  $query->execute(...);
  echo 'Update succeeded';

} catch(PDOException $e) {
  echo 'Update failed!';
  echo 'Error: ' . $e->getMessage();
}

Information about error mode settings and handling is available in the documentation here: http://php.net/manual/en/pdo.error-handling.php

Or, if PDO isn't set to throw an exception, we can use a simple if test. (The execute() method will return FALSE if the the statement fails.)

if ($query->execute(...)) {
   echo 'Update succeeded';

} else {
   echo 'Update failed!';

}

For more precise control with the different types of failure, we can use the errorCode() method to retrieve the SQLSTATE associated with the last operation on the statement handle, and we can perform conditional tests on the returned value. http://php.net/manual/en/pdostatement.errorcode.php

edited May 08 '18 at 21:59

answered Aug 06 '14 at 19:28

spencer7593

106,611
15
112
140

OK. So execute returns false if the statement fails, and not if no rows were update, right? – jmenezes Aug 06 '14 at 19:32
jmenezes: Yes, exactly. It's considered "success" if an UPDATE statement succeeds but updates zero rows.. The database finished executing the statement and did not encounter an error. (It's perfectly valid for an UPDATE statement to affect zero rows, that's not considered a "failure". Either because no rows matched the predicates in the WHERE clause, or because no rows needed to be modified because the existing value in the column already matched the value in the SET clause.) You can get what you need from the statement handle that ran the UPDATE, there's no need for another query. – spencer7593 Aug 06 '14 at 19:37
Right. Thanks for explaining and answering. – jmenezes Aug 06 '14 at 19:41
Only after you've determined the statement is completed successfully would you check `rowCount()`, to find out how many rows were affected. NOTE: If `PDO::MYSQL_ATTR_FOUND_ROWS => true` on the connection, then `rowCount()` returns number of rows found (matched), rather than number of rows affected. – spencer7593 Aug 06 '14 at 19:43

score 2 · Answer 2 · answered Aug 06 '14 at 19:13

2

Even if no rows are affected that does not mean the update failed just, like you said, nothing was changed. It would only fail if there was an exception thrown. To handle this you would need to implement a try/catch block.

http://php.net/manual/en/language.exceptions.php

try{
    $query = $conn->prepare($sql);      
    $result = $query->execute(array(    
        ":itemCount" => $itemCount
        ":itemId" => $itemId
    ));
    echo 'Updated succeeded';
} catch(Exception $e) {
    echo 'Updated failed!';
}

answered Aug 06 '14 at 19:13

AJ Allen

61
4

OK.When checking the second way I've done in my question, does execute return true or false based on whether or not the query succeeded or based on if one or more rows were affected? How does it do that, and is that safe to use instead of try catch? – jmenezes Aug 06 '14 at 19:27
The above answer by spencer7593 i think answers your question about the return value of execute. A good way to see this happen would be to execute your update statement in the mysql command prompt window to see the result. If you actually make a change it should say: Query Ok, (5) rows affected (0.001 sec) or if none were affected: Query Ok, (0) rows affected (0.001 sec). If it was unsuccessful you would see an ERROR message. – AJ Allen Aug 06 '14 at 19:58

PHP PDO MySQL Correct way to check if an update query succeeded when no rows are affected

2 Answers2