Search code examples
phpmysqlsql-updaterows-affected

MySQL no affected rows upon UPDATE when value not changed

MySQL with PHP, trying to update a row: 

$dbQuery = 'UPDATE UserTable SET Age=25 WHERE Id=3';
$result = mysqli_query($dbLink, $dbQuery);
if ($result === FALSE) {
  // Take care of error
}
else {
  $numAffectedRows = mysqli_affected_rows($dbLink);
}

I get zero $numAffectedRows in two different cases:
1. When there is no user row with Id=3
2. When there is a user row with Id=3 but Age was already 25 before

Is there a way I can distinguish between the two cases? (apart from reading the row before and manually check the value before updating)

Solution

  • According to mysql documentation, you can change the behaviour of affected_rows by passing the MYSQLI_CLIENT_FOUND_ROWS flags while connecting using mysql_real_connect.

    In this case, mysql_affected_rows returns the number of rows matched by the WHERE condition, not the number of updated rows.