1

I have the following code:

$put_tag_rq = $DBH->prepare( "
  INSERT INTO `tags`
  SET
    `name` = :name
  ON DUPLICATE KEY UPDATE
    `id` = `id`
" );
$put_tag_rq->execute( array(
  'name' => $tag,
) );
$tag_id = $DBH->lastInsertId();
echo "***********Last insert ID = $tag_id<br>";

Please note that the name is also the tab's key.

This code works fine if the inserting name tag is not yet presented in the table. But lastInsertId() returns 0 if name already there.

Of course I could do two requests: first to check if the name tag already presented in the tab (and obtain its ID) and the second to insert the name is already presented in the tab.

But I think that it should be simpler. Isn't it?

user2525317
  • 177
  • 1
  • 10
  • 1
    What are you trying to update? You have set the `id` to its existing value, so MySQL won't modify the row at all. The value of `affectedRows()` would differ, 1 if a new insertion made, 2 if an existing row modified, 0 if no change made. – Michael Berkowski Sep 29 '14 at 20:55
  • 1
    Or is it that you want to _return the `id`_ of the updated row? – Michael Berkowski Sep 29 '14 at 20:56
  • @MichaelBerkowski I've updated the question that `name` is also the key, so in case `name` already exist - UPDATE statement should work. – user2525317 Sep 29 '14 at 21:03
  • I understand that `name` is the unique index, but what is the purpose of `id = id`? You are not making a change. – Michael Berkowski Sep 29 '14 at 21:05
  • @MichaelBerkowski that's right: if the `name` already in the tab SQL request shouldn't make any change. I'd leave ON DUPLICATE KEY clause empty but it's not allowed by the MySQL. – user2525317 Sep 29 '14 at 21:10

1 Answers1

1

This is the MySQL that you're looking for: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

And more info on how to do it in php: PHP MYSQL UPDATE if Exist or INSERT if not?

Community
  • 1
  • 1
Austen Chongpison
  • 3,966
  • 1
  • 20
  • 17