0

I am using a really awesome piece of code that inserts AND on duplicate key, it updates the duplicate id and gets the last insert id regardless of duplicate or not. Therefore I get the id of the last updated or the duplicate id. But is there a way to distinguish the two?

$prepare=$connection->prepare("
   insert into category(related,text) values(?,?) 
   on duplicate key update id=last_insert_id(id)");
$prepare->execute([1,"property"]);
print_r($connection->lastinsertid());
// this is both the update id and duplicate update id

This will give me an ID regardless of duplicate error or not. But I would like to distinguish if the id is a duplicate or not. Something like:

$prepare=$connection->prepare("
   insert into category(related,text) values(?,?) 
   on duplicate key select id");
$prepare->execute([1,"property"]);
$fetch=$prepare->fetch();
if(!empty($fetch)){
   echo 'DUPLICATE';
   $id=$fetch['id'];
   // this is the duplicate update id
}
else{
   $id=$connection->lastinsertid();
   // this is the update id
}
Maciek Semik
  • 1,872
  • 23
  • 43
  • I think one approach would be to have both created and last updated time stamps, and see which one was affected- something like that – Strawberry Aug 08 '17 at 23:45
  • Can you provide the table definition of category? is id an additional field as AUTOINCREMENT? Where is the unique constraint defined?on related or text? – Myonara Aug 09 '17 at 00:37
  • id is a auto_increment primary key while unique index is for both related and text together. Therefore there can never be two sets of related and text. – Maciek Semik Aug 09 '17 at 16:11

0 Answers0