0

This seems like such a simple problem but Google cannot seem to find much on the topic.

For caching reasons I want to run a query to let a user like something:

INSERT INTO tbl_review_vote (user_id,review_id,vote) VALUES(:uid,:rid,:vote) ON DUPLICATE KEY UPDATE vote=:vote

But then understand from the server response whether or not the like was inserted or updated so that I can pre-aggregate a likes to the user review record.

Is there a good way of doing this?

Sammaye
  • 43,242
  • 7
  • 104
  • 146
  • Hi it is asked before: http://stackoverflow.com/questions/778534/mysql-on-duplicate-key-last-insert-id – Serhat Akay Oct 04 '13 at 14:58
  • You can look at the number of rows inserted. Which MySQL API are you using? – Hanky Panky Oct 04 '13 at 14:58
  • I don't know if you're using any DB class, but usually (even the simple `mysql_query()` does the same, for ex) the return values are different (true/resource), so you can check that. Look on the doc of the methods you're using – Damien Pirsy Oct 04 '13 at 14:58
  • @Hanky웃Panky The number of rows does not work, that can represent inserted or updated – Sammaye Oct 04 '13 at 14:59
  • @DamienPirsy I am using PDO and any return is not disconcernable between insert and update that I can see – Sammaye Oct 04 '13 at 15:00
  • @SerhatAkay that linked answer didn't help but Shamils did – Sammaye Oct 04 '13 at 15:05

2 Answers2

2

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. You could then look at the difference between mysql's LAST_INSERT_ID() and your API's last_insert_id to ascertain whether it was an insert or an update.

An alternative way to see what it was, is by adding an update_count column which increments with your query: update_count = update_count + ;, or alternatively, a timestamp without ON UPDATE CURRENT_TIMESTAMP

bear
  • 11,364
  • 26
  • 77
  • 129
0

Try these php functions:

mysql_affected_rows() or mysqli_affected_rows()

Thomas Laier Pedersen
  • 449
  • 2
  • 8
  • 1
    This is not an answer, also the question clearly states PDO is being used, not mysqli. – N.B. Oct 04 '13 at 15:08