CODEWITHSUNDEEP
phpmysql
Owen
Owen

Reputation: 7577

PHP/mySQL ON DUPLICATE KEY UPDATE - did INSERT or UPDATE occur?

I have a mySQL query using ON DUPLICATE KEY UPDATE that I am runnining from a PHP script. I would like to test whether an UPDATE or an INSERT occurred. How can I do that?

Upvotes: 2

Views: 574

Answers (2)

nickb
nickb

Reputation: 59699

I had to figure this out one time, so I'm referring to my notes on the matter.

The following query:

INSERT INTO table ... ON DUPLICATE KEY UPDATE ...,  id = LAST_INSERT_ID(  id)

Where 'id' is the primary key on the table, allows you to call mysqli_affected_rows(), which will return:

  1. 0 - Row existed, nothing updated
  2. 1 - No row existed, inserted
  3. 2 - Row existed, something updated

Upvotes: 3

John Watson
John Watson

Reputation: 2573

From MySQL documentation:

"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 can use mysql_affected_rows() to determine the number of rows affected.

Upvotes: 2

Related Questions