PHP PDO MySQL Correct way to check if update query succeeded if no rows touched
What is the surest way to find out if the update query succeeded when using php pdo and mysql?
In my application, I am updating the totals of the items that are being sent, but the user and the table looks like this:
the elements
userId | itemsAdded | itemsChecked | itemsUnChecked | itemsTotal
1 | 5 | 2 | 3 | 5
So when I do update items set itemTotals = itemsChecked+itemUnChecked
, the column itemsTotal
stays the same, unless the itemsAdded variables and the items are incremented increment (2 + 3 is 5, 1 + 4 is also 5).
I used to use rowCount () to check if the query succeeded, but in this case, since the column itemsTotal
stays the same, there is no way to know if 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 this return true or false based on whether the query was successful or based on the number of rows updated?
I only need to check if the request succeeded or not. You do not need to specify the number of updated rows.
source to share
The method execute()
either throws an exception or returns FALSE, depending on the error mode that you set to connect to the database.
If you set the error mode to an exception, i.e.
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
You can use a try / catch block to handle the exception:
try {
$query->execute(...);
echo 'Updated succeeded';
} catch(PDOException $e) {
echo 'Updated failed!';
echo 'Error: ' . $e->getMessage();
}
(Information about the error mode settings and settings is available in the documentation: http://php.net/manual/en/pdo.error-handling.php
If PDO is not configured to throw an exception, you can use a simple test if
. (The method execute()
will return FALSE if the statement is not executed.)
if ($query->execute(...)) {
echo 'Updated succeeded';
} else {
echo 'Updated failed!';
}
For finer control with different types of failures, you can use a method errorCode()
to retrieve the SQLSTATE associated with the last operation in the statement handle and run conditional tests on the return value. http://php.net/manual/en/pdostatement.errorcode.php
source to share
Even if no line is touched it doesn't mean that the update didn't work, just like you said nothing has changed. It would be unfortunate if an exception was thrown. To handle this, you 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!';
}
source to share