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.

Answer 1

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

Answer 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!';
}