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?

Heterologous

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. :) – Ewell

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(). – Pattern

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

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

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.

Heterologous

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

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 – Liatris

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) – Brittni

What exactly is "id" ? Is this the auto increment colum name ? What if the thread starter has "foo" as auto increment unique identifier? – Tabular

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

Multiplicity

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

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

Answer 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().

Assets

Answer 4

This is my solution where you put the data into a single array and it's automatically duplicated/populated into the "INSERT INTO .. ON DUPLICATE UPDATE .. " query.

It's great for maintainability, and if you want you can make it a function / method too.

// save to db:

$qData = [
    "id" =>                mysql_real_escape_string($email_id),     
    "synd_id" =>           mysql_real_escape_string($synd_id),
    "campaign_id" =>       mysql_real_escape_string($campaign_id),
    "event_id" =>          mysql_real_escape_string($event_id),
    "user_id" =>           mysql_real_escape_string($user_id),
    "campaign_name" =>     mysql_real_escape_string($campaign_name), 
    "subject" =>           mysql_real_escape_string($subject),
    "from_name"=>          mysql_real_escape_string($from_name),
    "from_email"=>         mysql_real_escape_string($from),
    "content"=>            mysql_real_escape_string($html),
    "link_to_template" =>  mysql_real_escape_string($hash),
    "ext_campaign_id" =>   mysql_real_escape_string($ext_campaign_id),
    "ext_list_id"=>        mysql_real_escape_string($ext_list_id),
];


$q = "INSERT INTO email_campaigns (".
  // i.e create a string like `id`, `synd_id`, `campaign_id`..  with linebreaks for readability
  implode(", \n", array_map(function($k){ return "`$k`"; }, array_keys($qData)))
.")
VALUES (".
  // i.e '20', '532', '600' .. 
  implode(", \n", array_map(function($v){ return "'$v'"; }, array_values($qData)))
." )  ON DUPLICATE KEY UPDATE ".
  // i.e `synd_id`='532', `campaign_id`='600' ... 
  // id & link_to_template table keys are excluded based on the array below
  implode(", \n", array_filter(array_map(function($k, $v){ if(!in_array($k, ['id', 'link_to_template']) ) return "`$k`='$v'" ; }, array_keys($qData), array_values($qData))))  ;

Prentiss