Differentiate between 'no rows were affected' and rows succesfully UPDATEd--to same value (MySQL and PHP)

Question

I am executing SQL (MySQL) commands from PHP. There are several possible outcomes to each execution:

1. Record updated to new value
2. Record updated, but values happen to be the same
3. 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...

Bourdon

Using PDO? Check out rowCount(). MySQLi? affected_rows() – Howardhowarth

in addition to @JayBlanchard, as the writer said he is using mysql, so php.net/manual/en/function.mysql-affected-rows.php would do the work – Nasia

mysql_* functions are deprecated @Nasia and will never receive a recommendation from me. The code the OP has posted indicates they may be using something other than the old MySQL API. Read carefully, the OP states using MySQL (the database) not mysql_* the API. ¯\_(ツ)_/¯ – Howardhowarth

well, I half agree with you but as the OP mentioned it on the title + tagged mysql, so I just gave him the last piece of the cake that you don't like (: edit: well I think its understand able by "I am executing SQL (MySQL) commands" ¯_(ツ)_/¯ – Nasia

I can execute MySQL commands from PHP in any number of ways @Danny, stating that does not tell you which API I am using. You'll note I couched my comment as questions and the OP disappeared. – Howardhowarth

@JayBlanchard - thanks for the help. Unfortunately I realized I made a mistake in my post--I meant to ask how to differentiate between scenarios TWO and three, not ONE and three! So rowCount() --I <i>am</i> using PDO :P --doesn't really accomplish what I need…because it only returns AFFECTED rows…any other ideas? Thanks again, tho'! (I'll correct my error now...) – Bourdon

@Nasia - thanks for the help. Unfortunately I realized I made a mistake in my post--I meant to ask how to differentiate between scenarios TWO and three, not ONE and three! So rowCount() --I <i>am</i> using PDO :P --doesn't really accomplish what I need…because it only returns AFFECTED rows…any other ideas? Thanks again, tho'! (I'll correct my error now...) – Bourdon

Answer 1

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?

Foulmouthed

@mickmackusa-I guess. I was sort of hoping there was a way to make it work via checking return values so I wouldn't have to make 2 calls… (also, I'm not using mysqli, but that's not such a big deal) – Bourdon

@Bourdon Will it be especially burdensome to make 2 calls? ( <-not sarcasm.) I totally understand seeking out a sexy single call process. If you settle for 2 calls, your secret is safe with me. – Foulmouthed

@mickmackusa-Lol! I'll see if I can work with that other idea…which is certainly very interesting! If not, I'll probably do 2 calls….shhhhh…. – Bourdon