English
English
When using PHP's MySQLi extension for database operations, the UPDATE statement is one of the most commonly used SQL operations. However, many developers often overlook a crucial detail when performing UPDATE operations: failing to verify whether there are any matching records before executing the update. This oversight can directly affect the value of the mysqli::$affected_rows property, potentially leading to logic errors or unexpected behavior in the program.
In MySQLi, after executing a data modification operation (such as INSERT, UPDATE, or DELETE), $mysqli->affected_rows returns the number of affected rows. This value is often used in various scenarios to determine whether the operation was successful or to log the operation, among other things.
For example:
$mysqli = new mysqli("localhost", "user", "pass", "database");
$mysqli->query("UPDATE users SET status = 'active' WHERE last_login < NOW() - INTERVAL 30 DAY");
echo $mysqli->affected_rows;
This code will output the number of affected rows, which corresponds to the number of users whose status was successfully updated.
When the UPDATE statement doesn't match any records, $mysqli->affected_rows will return 0. This occurs because no records were modified, regardless of whether the syntax is correct or if the SQL query executed successfully, the affected rows will still be 0.
Note: Even if the SQL syntax is correct, if the WHERE condition does not match any data, affected_rows will still be 0. This does not mean that the SQL execution failed.
For example:
$mysqli = new mysqli("localhost", "user", "pass", "database");
$mysqli->query("UPDATE users SET status = 'active' WHERE id = 999999");
echo $mysqli->affected_rows;
Assuming no user with id = 999999 exists, the output will be 0, indicating that no records were updated.
There is also a situation that is often misunderstood: Even if records are matched, if the updated value is the same as the original value, it will not be counted as an affected row.
For example:
$mysqli = new mysqli("localhost", "user", "pass", "database");
$mysqli->query("UPDATE users SET status = 'active' WHERE status = 'active'");
echo $mysqli->affected_rows;
In this case, although many rows might have been matched, since the status field was already set to 'active', no actual data modification took place. MySQL will consider that no rows were "affected" and will return 0.
If you rely on affected_rows to determine whether an operation was successful, be sure to consider the following two points:
Check whether the SQL query was successful (you can verify this using $mysqli->errno or $mysqli->error).
Ensure that affected_rows is greater than 0, indicating that data was indeed modified.
Complete Example:
$mysqli = new mysqli("localhost", "user", "pass", "database");
$result = $mysqli->query("UPDATE users SET status = 'inactive' WHERE last_login < NOW() - INTERVAL 1 YEAR");
if ($result === false) {
die("SQL Error: " . $mysqli->error);
} elseif ($mysqli->affected_rows > 0) {
echo "Successfully updated {$mysqli->affected_rows} records.";
} else {
echo "No records were updated.";
}
When building management systems or APIs, developers may wish to use the affected_rows value to verify if the conditions provided by the client matched any records. If this mechanism is not clearly communicated, clients might mistakenly believe the update failed or that the server encountered an error.
For example, consider the following API:
POST /api/update-status.php?user_id=123 HTTP/1.1
Host: m66.net
If the user ID does not exist, and the API code only checks affected_rows to determine if the operation was successful, the client may incorrectly assume there was an issue with the system. It is better to send a more explicit response, such as "No matching records found," rather than "Operation failed."
In PHP's MySQLi operations, $mysqli->affected_rows is an important tool for determining whether data has actually changed. However, it is essential to remember:
If no matching records are found, the value is 0.
If records are matched but the data before and after the update is the same, it will also return 0.
SQL execution errors will not be reflected in affected_rows, so extra checks are necessary.
Developers must fully understand the behavior of this property to avoid logical errors or misinterpretation of update results.
