CODEWITHSUNDEEP
phpmysql
Red Balloon
Red Balloon

Reputation: 175

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

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!";}

Upvotes: 6

Views: 2725

Answers (3)

geomagas
geomagas

Reputation: 3280

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!";}

Upvotes: 3

Legionar
Legionar

Reputation: 7587

I think this cant be done normally, but you can use another column for help.

Add column counter to your users table. And then just increase this value on each 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, doesnt matter, if value name will change or not, counter will change each time, so it will return each time 1 if successed.

Or also as Damodaran answered, instead of counter you can use current datetime when doing update.

Upvotes: 0

Damodaran
Damodaran

Reputation: 11047

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!";}

Upvotes: 2

Related Questions