MySQL no affected rows upon UPDATE when value not changed

Question

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)

Your not affecting a row in either case, this seems like a strange question, surely your updating via a form which means the user exists to begin with and you know their ID? — CodeX, Aug 25 '14 at 09:54
A standard update query sent from a form if coded right will return either a success message or an error message, in your success message you will get the affected rows ie. 1. In your error message you could also return the affected rows ie.0 — CodeX, Aug 26 '14 at 10:36

score 5 · Accepted Answer · answered Aug 25 '14 at 08:57

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.

score 0 · Answer 2 · answered Aug 25 '14 at 09:23

I don't think this is possible with just one query. I would go for INSERT INTO..ON DUPLICATE approach.

$dbQuery = "INSERT INTO UserTable (Id,Age) VALUES('3','25') ON DUPLICATE KEY 
            UPDATE Id='3',Age='25'";
$result = mysql_query($dbQuery);
if ($result === FALSE) {
   // Take care of error
}
else {
   $numAffectedRows = mysql_affected_rows();
   // $numAffectedRows = 0  => row exist
   // $numAffectedRows = >0 => row added with Id,Age specified
}

MySQL no affected rows upon UPDATE when value not changed

2 Answers2