getting mysql_insert_id() while using ON DUPLICATE KEY UPDATE with PHP

Question

I've found a few answers for this using mySQL alone, but I was hoping someone could show me a way to get the ID of the last inserted or updated row of a mysql DB when using PHP to handle the inserts/updates.

Currently I have something like this, where column3 is a unique key, and there's also an id column that's an autoincremented primary key:

$query ="INSERT INTO TABLE (column1, column2, column3) VALUES (value1, value2, value3) ON DUPLICATE KEY UPDATE SET column1=value1, column2=value2, column3=value3"; mysql_query($query);  $my_id = mysql_insert_id(); 

$my_id is correct on INSERT, but incorrect when it's updating a row (ON DUPLICATE KEY UPDATE).

I have seen several posts with people advising that you use something like

INSERT INTO table (a) VALUES (0) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id)  

to get a valid ID value when the ON DUPLICATE KEY is invoked-- but will this return that valid ID to the PHP mysql_insert_id() function?

Answer 1

Here's the answer, as suggested by Alexandre:

when you use the id=LAST_INSERT_ID(id) it sets the value of mysql_insert_id = the updated ID-- so your final code should look like:

<?     $query = mysql_query("         INSERT INTO table (column1, column2, column3)          VALUES (value1, value2, value3)          ON DUPLICATE KEY UPDATE             column1 = value1,              column2 = value2,              column3 = value3,              id=LAST_INSERT_ID(id)     ");     $my_id = mysql_insert_id(); 

This will return the right value for $my_id regardless of update or insert.

Answer 2

You could check if the Query was an insert or an update ( mysql_affected_rows(); returns 1 on insert and 2 on update).

If it was an insert use mysql_insert_id, if it was an update you'd need another Query.

<?php $query ="INSERT INTO TABLE (column1, column2, column3) VALUES (value1, value2, value3) ON DUPLICATE KEY UPDATE SET column1=value1, column2=value2, column3=value3"; mysql_query($query); if(mysql_affected_rows() == 1) { $id = mysql_insert_id(); } else { // select ...  } ?> 

I know it's not excatly what your looking for but it's the best i could come up with