Mysql update query, what if value didn't change? how do I check for that?

Question

When executing UPDATE statement, if value is same as new value than rowCount doesn't change. But for purposes of my application this is also a success. So how do I check for successful update no matter if value 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're not executing the query, merely preparing it. So rowCount() will report an invalid number of rows (the one referring to the last executed query), since no rows were affected yet, and the system doesn't know beforehand how many will be, once you execute the prepared statement with specific param values.

You should check for success upon executing the statement. The execute() method will return true if it succeeds and false otherwise. So if execution success is the only thing you need, then you should do it along the lines of:

$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 that contains the last update time. So that I can use that to get the entries that got updated after a specific time. In this way I able to reduce the number of entries send to client. The final decision is based on your requirement.

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