Mysql if the value has not changed? How can I check this?

Question

When executing an UPDATE statement, if the value matches a new value than rowCount, this does not change. But for the purposes of my application, this is also a success. So, how can I check for a successful update regardless of whether the value has changed or not?

$stmt = $conn->prepare('UPDATE users SET name = :name WHERE id = :id'); $result = $stmt->rowCount(); // 1 if ($result == 1) {echo "success!";} 

Answer 1

You do not fulfill the request, just prepare it. Thus, rowCount() will report an incorrect number of rows (the one that refers to the last executed query), since not a single row has been affected, and the system does not know in advance how many will be after executing the prepared statement with specific parameter values.

You must verify the success of the instructions. The execute() method will return true if it succeeds, and false otherwise. Therefore, if success in execution is the only thing you need, then you must do it in accordance with:

 $stmt = $conn->prepare('UPDATE users SET name = :name WHERE id = :id'); $result = $stmt->execute($params); // <-- execute first! if ($result) {echo "success!";} 

Answer 2

I agree with Legionar . But instead of count, I used to add a column containing the latest update time. So that I can use this to get records that are updated after a certain time. This way I can reduce the number of records sent to the client. The final decision is based on your requirements.

 $stmt = $conn->prepare('UPDATE users SET name = :name, updateTime = currentTime WHERE id = :id'); $result = $stmt->rowCount(); // 1 if ($result == 1) {echo "success!";} 

Answer 3

I think this cannot be done normally, but you can use a different column for reference.

Add the counter column to the users table. And then just increase this value with every update.

 $stmt = $conn->prepare('UPDATE users SET name = :name, counter = counter + 1 WHERE id = :id'); $result = $stmt->rowCount(); // 1 if ($result == 1) {echo "success!";} 

So now, it doesn’t matter if the name value changes or not, counter will change every time, so it will return 1 every time if it succeeds.

Or, as Damodaran replied, instead of counter you can use the current datetime when updating.