MySQL does not affect rows in UPDATE when value does not change

Question

MySQL with PHP trying to update a string:

$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 $ numAffectedRows null in two different cases:
1. If there is no user line with Id = 3
2. When there is a user line with Id = 3, but Age was already 25 before

Is there a way to distinguish between the two cases? (besides reading the line before and manually checking the value before updating)

Answer 1

According to the mysql documentation , you can change the behavior of affected_rows by passing the MYSQLI_CLIENT_FOUND_ROWS flags when connecting using mysql_real_connect .

In this case, mysql_affected_rows returns the number of rows matching the WHERE clause, not the number of rows updated.

Answer 2

I do not think this is possible with just one request. I would go for the 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 }