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?

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

Solution 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

Solution 3

Although not using mysql_insert_id() and ON DUPLICATE KEY UPDATE, alternative great way to get the value of any field when updating another found here:

UPDATE table SET id=(@tempid:=id) , .... LIMIT 1;
SELECT @tempid;

I used it having table with (id,status) 'id' primary index auto-increment, and 'status' was the field upon which update was made, but i needed to get 'id' of the updated row. This solution also proof to race conditions as mysql_insert_id().

Comments

[Decent Dabbler]

I don't have the answer. But the solution looks clever. Why don't you try it? I would think it shouldn't be to hard to create a test case that would give a definitive answer. PS.: Don't get me wrong though; I can understand you would want reassurance. I would probably too. But I would try it first I guess. :)

[Alex Jasmin]

I was curious how that could work but I found this in the MySQL manual: If expr is given as an argument to LAST_INSERT_ID(), the value of the argument is returned by the function and is remembered as the next value to be returned by LAST_INSERT_ID().

[Decent Dabbler]

@Alexandre: yes, and presuming mysql_insert_id() is just a proxy to LAST\_INSERT\_ID() I would think this should work as expected.

[julio]

thanks guys, this is the approach that worked for me. You're right-- when you use the id=LAST_INSERT_ID(id) it sets the value of mysql_insert_id = the updated ID.

[ianace]

what if the id being updated is being referenced in another table? would this cause trouble?

[Déjà vu]

+1 for the affected_rows() returns 1 on insert and 2 for update.

[Russell G]

This helped me, but just to be clear, for anyone else having the problem... If the record already exists and gets updated, the call to LAST_INSERT_ID() ensures that when you later call mysql_insert_id(), it'll return the id of the record updated, and not zero as it usually would. Also note that "id" should be replaced by the auto_increment column name in the table. More info: dev.mysql.com/doc/refman/5.1/en/insert-on-duplicate.html

[Jakob Eriksson]

if nothing is updated (values same as in db), nor inserted; affected rows returns 0

[Martin]

Sadly as of MySQL 5.7 this no longer appears to work, it does not give back the correct value. It appears to give the insert id + the result of the update (0,1,2)