English
English
When using PHP's PDO (PHP Data Objects) for database operations, the PDO::errorCode() method returning '00000' indicates that no error occurred. This might suggest that the database operation was completed successfully, but in reality, errorCode returning '00000' does not always accurately reflect whether the operation was truly successful.
This article will explain in detail why a PDO::errorCode() return value of '00000' does not necessarily mean the operation succeeded and introduce more reliable methods to determine the true success of a database operation.
PDO::errorCode() returns a '00000' SQLSTATE code, which means "no error". It mainly reflects whether errors occurred during database communication, such as syntax errors or connection failures.
However, in some cases, even if an SQL statement was executed and no error occurred, errorCode() will still return '00000', though the outcome may not be as expected. For example:
An UPDATE statement was executed, but no rows were actually updated (perhaps because the WHERE clause didn’t match any records).
A DELETE statement was executed, but no rows were deleted.
An INSERT was performed, but the data was altered by a trigger or constraints didn’t apply as expected.
These scenarios don’t trigger errors, but from a business logic perspective, the operation was not effective.
PDOStatement::rowCount() returns the number of rows affected by the last executed SQL statement. It’s an essential method for determining whether UPDATE or DELETE operations actually changed any data.
<?php
$pdo = new PDO('mysql:host=m66.net;dbname=testdb;charset=utf8', 'username', 'password');
<p>$sql = "UPDATE users SET status = 1 WHERE id = :id";<br>
$stmt = $pdo->prepare($sql);<br>
$stmt->execute([':id' => 123]);</p>
<p>if ($stmt->errorCode() === '00000') {<br>
$affectedRows = $stmt->rowCount();<br>
if ($affectedRows > 0) {<br>
echo "Update successful, {$affectedRows} row(s) affected.";<br>
} else {<br>
echo "Execution succeeded, but no rows were updated.";<br>
}<br>
} else {<br>
echo "Database error occurred. Error code: " . $stmt->errorCode();<br>
}<br>
?><br>
Here, although errorCode() returns '00000', only when rowCount() is greater than zero can we confirm that data was actually modified.
For INSERT operations involving auto-increment primary keys, you can use PDO::lastInsertId() to retrieve the inserted record’s ID. If the return value is non-empty and reasonable, the insert was successful.
<?php
$pdo = new PDO('mysql:host=m66.net;dbname=testdb;charset=utf8', 'username', 'password');
<p>$sql = "INSERT INTO users (name, email) VALUES (:name, :email)";<br>
$stmt = $pdo->prepare($sql);<br>
$stmt->execute([':name' => 'Zhang San', ':email' => '<a class="cursor-pointer" rel="noopener">zhangsan@example.com</a>']);</p>
<p>if ($stmt->errorCode() === '00000') {<br>
$lastId = $pdo->lastInsertId();<br>
if ($lastId) {<br>
echo "Insert successful. New user ID: {$lastId}";<br>
} else {<br>
echo "Execution succeeded, but no insert ID returned.";<br>
}<br>
} else {<br>
echo "Database error occurred. Error code: " . $stmt->errorCode();<br>
}<br>
?><br>
Note that lastInsertId() depends on the table having an auto-increment primary key; otherwise, the return value may be empty.
When executing multiple or complex operations, enabling transactions and using exception handling is the safest way to ensure atomicity of operations.
<?php
try {
$pdo = new PDO('mysql:host=m66.net;dbname=testdb;charset=utf8', 'username', 'password');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql1 = "UPDATE accounts SET balance = balance - 100 WHERE user_id = :user_id";
$stmt1 = $pdo->prepare($sql1);
$stmt1->execute([':user_id' => 1]);
if ($stmt1->rowCount() === 0) {
throw new Exception("Debit failed: no rows affected");
}
$sql2 = "UPDATE accounts SET balance = balance + 100 WHERE user_id = :user_id";
$stmt2 = $pdo->prepare($sql2);
$stmt2->execute([':user_id' => 2]);
if ($stmt2->rowCount() === 0) {
throw new Exception("Credit failed: no rows affected");
}
$pdo->commit();
echo "Transfer successful";
} catch (Exception $e) {
$pdo->rollBack();
echo "Operation failed. Reason: " . $e->getMessage();
}
?>
In this example, any ineffective operation throws an exception, triggering a rollback to prevent data inconsistency.
PDO::errorCode() returning '00000' means no error, but does not confirm any actual effect.
Use PDOStatement::rowCount() to check affected rows and verify the effectiveness of update or delete operations.
For insert operations, PDO::lastInsertId() helps confirm whether the insert was successful.
For complex logic, using transactions with exception handling ensures data integrity and accuracy.
By following these practices, you can more accurately determine whether a database operation was truly successful and avoid potential logical flaws that come from relying solely on errorCode().
