English
English
Data loss is one of the most troublesome problems for developers when performing database operations. Whether it is user data update, inventory deduction, or order status modification, once the database operation is not successfully executed but not detected and processed, it may lead to serious consequences.
This article will explain how to use PDOStatement::rowCount() and database transaction rollback mechanism to effectively prevent this situation in PHP.
PDOStatement::rowCount() is a function in PDO that returns the number of rows affected by the previous DELETE , INSERT , or UPDATE statement. It can help us determine whether operations have really had an impact on the database.
It should be noted that not all database drivers support calling rowCount() for SELECT operations, which we usually use to detect whether the data has been successfully updated or deleted.
When performing multiple related database operations, if one of the steps is not successful, the previous changes should be rolled back to avoid data inconsistencies. For example, if you want to update a user's email address, but the record has not been actually modified, and the program continues to perform subsequent operations, this may lead to logical confusion or even data loss.
At this time, it is particularly critical to use transactions and rowCount() in combination.
Here is a practical example showing how to use rowCount() and transactions to control data when updating user information to ensure data security:
<?php
$dsn = 'mysql:host=localhost;dbname=test_db;charset=utf8mb4';
$user = 'db_user';
$password = 'db_pass';
try {
$pdo = new PDO($dsn, $user, $password, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);
// Suppose we want to update the user's mailbox
$userId = 123;
$newEmail = '[email protected]';
$pdo->beginTransaction();
$stmt = $pdo->prepare("UPDATE users SET email = :email WHERE id = :id");
$stmt->execute([
':email' => $newEmail,
':id' => $userId
]);
// use rowCount Determine whether the data has been updated
if ($stmt->rowCount() === 0) {
// No records have been updated，Roll back transactions
$pdo->rollBack();
echo "Update failed，No matching user found，Transaction has been rolled back。";
} else {
// Update successfully，Submit transactions
$pdo->commit();
echo "邮箱Update successfully，Transaction has been submitted。";
}
} catch (PDOException $e) {
if ($pdo->inTransaction()) {
$pdo->rollBack();
}
echo "Database operation failed：" . $e->getMessage();
}
?>
Use beginTransaction() to start a transaction;
execute() executes the update;
Use rowCount() to determine whether any records are actually updated;
If no records are updated, the transaction will be rolled back immediately;
Otherwise, submit safely.
This method is not only suitable for update operations, but is very practical in inventory systems, points deductions, payment processing and other scenarios. For example:
Roll back the order status when deduction fails;
Roll back payment record insertion if update user balance fails;
Rollback log writing operations when deleting a user fails.
Never assume that the UPDATE or DELETE operation will succeed;
Be sure to handle exceptions when using transactions to ensure rollback;
For multiple highly dependent operations, be sure to wrap in one transaction;
rowCount() returns 0 does not necessarily mean failure, sometimes the value remains unchanged. But in a rigorous data scenario, it is exactly the "invalid write" we want to prevent.
By combining PDOStatement::rowCount() and transaction control mechanism, developers can more accurately determine whether the database operation is actually in effect, thereby performing transaction rollback when necessary, preventing data problems that "looks successful, but actually fails". This is an indispensable skill in the development of high-reliability systems.
Have you encountered similar problems in your project? Welcome to leave a message to share your experience!
