1

In my User table I have phone as unique. So when I insert duplicate phone number I want to update deviceId, registrationId and I want the id which got updated. I tried this but its not working.

$stmt1 = $db->prepare("INSERT into User (name,phone, deviceId,registrationId) values(:name,:phone, :deviceId,:registrationId)  ON DUPLICATE KEY UPDATE deviceId = :deviceId, registrationId = :registrationId, id=lastInsertId(id)");

Thank you..

Eduard Uta
  • 2,477
  • 5
  • 26
  • 36
Shridhar Patil
  • 362
  • 1
  • 17
  • 2
    Possible duplicate of [Prepared Statement with ON DUPLICATE KEY](http://stackoverflow.com/questions/22699598/prepared-statement-with-on-duplicate-key) – Criesto Oct 27 '15 at 09:04
  • what is your primery key? i guess on duplicate update is work with primary key, try to set phone number is your primary key then try. – Raja Usman Mehmood Oct 27 '15 at 09:06
  • 1
    As long as your "phone" value is Unique/Primary and as stated in the mysql documentation this should work "INSERT INTO table (a,b,c) VALUES (1,2,3) ON DUPLICATE KEY UPDATE id=LAST_INSERT_ID(id), c=3;" .. – Florian Oct 27 '15 at 09:07
  • You have to add constraint in your table ON UPDATE to NO ACTION for the foreign key. – Shivam Oct 27 '15 at 09:11
  • my primary key is id. and phone is unique – Shridhar Patil Oct 27 '15 at 09:17
  • phone is unique but its not a primary. i have id as primary key – Shridhar Patil Oct 27 '15 at 09:19
  • 1
    Can you be more specific because "its not working" is not very clear. Is the on duplicate not triggering, is the id not returning? What? – dotcomly Oct 27 '15 at 09:21
  • when i try to insert duplicate phone its not getting updated in database – Shridhar Patil Oct 27 '15 at 09:21
  • What is not getting updated? deviceId and registrationId? Is a new record being inserted instead? – dotcomly Oct 27 '15 at 09:23
  • when phone number is already present then deviceId and registrationId should get updated but its not getting updated – Shridhar Patil Oct 27 '15 at 09:38
  • Can you paste the results of the query `show create table User`. Also, is your query returning an error message (use `print_r($db->errorInfo());`) – dotcomly Oct 27 '15 at 09:42
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/93463/discussion-between-dotcomly-and-shridhar-patil). – dotcomly Oct 27 '15 at 09:45

1 Answers1

0

After chatting with the OP the problem was he was not getting the ID when a record was updated. The final solution is to use id=LAST_INSERT_ID(id) so the ON DUPLICATE KEY UPDATE will set the id and then fetch the id with $db->lastInsertId();

$stmt = $db->prepare("INSERT into User (name,phone, deviceId,registrationId) values(:name, :phone, :deviceId,:registrationId)  ON DUPLICATE KEY UPDATE deviceId = :deviceId, registrationId = :registrationId, id=LAST_INSERT_ID(id)");

Then fetch the insert id after.

$id = $db->lastInsertId();
dotcomly
  • 2,154
  • 23
  • 29