I am executing SQL (MySQL) commands from PHP. There are several possible outcomes to each execution:
- Record updated to new value
- Record updated, but values happen to be the same
- Record finds no rows to update (ie, no rows match the
WHERE clause)
I am wondering how to differentiate between #'s 1 and 3: both cases return zero as the number of rows being affected, so:
$result = $db->exec($statement)
will have $result == 0 in both cases. How can I tell the difference?
EDIT: I meant to ask how to differentiate between scenarios TWO and 3, not 1 and 3! Sorry for the inconvenience...
A simple solution would be two queries.
First, run a SELECT query to check if the row exists using mysqli_num_rows().
Then, if the row exists, you can run the UPDATE query and use mysqli_affected_rows().
[EDIT]
...I'll suggest a potential alternative for anyone seeking out a single call. I don't know if you are interested in doing any INSERTs, or purely UPDATEs. Below is some food for thought:
From the top comment @ http://php.net/manual/en/mysqli.affected-rows.php :
On "INSERT INTO ON DUPLICATE KEY UPDATE" queries, though one may expect affected_rows to return only 0 or 1 per row on successful queries, it may in fact return 2.
From Mysql manual: "With ON DUPLICATE KEY UPDATE, the affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated."
See: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html
Here's the sum breakdown per row:
+0: a row wasn't updated or inserted (likely because the row already existed, but no field values were actually changed during the UPDATE)
+1: a row was inserted
+2: a row was updated
Can you make that suit your needs?
