phpmysqlpdoon-duplicate-keyrows-affected

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

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...

Solution

  • 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?