#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3

    On duplicate key update?


    Hi.

    I have a page where a user can enter new items for a menu list of a restaurant, but the page also shows the current ones, when i try to edit a current one and save, it add's a new row..

    I have tried

    sql Code:
    $insert = $db->PREPARE('REPLACE INTO gerechten SET gerecht_id = NULL, gerecht_naam = :naam, gerecht_prijs = <img src="http://images.devshed.com/fds/smilies/tongue.gif" border="0" alt="" title="Stick Out Tongue" class="inlineimg" />rijs, menu_id = :menuid');


    and

    sql Code:
    $insert = $db->PREPARE('INSERT INTO gerechten SET gerecht_naam = :naam, gerecht_prijs = <img src="http://images.devshed.com/fds/smilies/tongue.gif" border="0" alt="" title="Stick Out Tongue" class="inlineimg" />rijs, menu_id = :menuid ON DUPLICATE KEY UPDATE gerecht_naam = :naam, gerecht_prijs = <img src="http://images.devshed.com/fds/smilies/tongue.gif" border="0" alt="" title="Stick Out Tongue" class="inlineimg" />rijs');


    But nothing is succesfull. any ideas? thanks! im really stuck here
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    Originally Posted by da manual
    REPLACE works exactly like INSERT, except that if an old row in the table has the same value as a new row for a PRIMARY KEY or a UNIQUE index, the old row is deleted before the new row is inserted.
    so you are editing an existing row, this existing row gets deleted, and a new row is inserted, right?

    isn't this what you wanted?

    perhaps it isn't finding the old row at all, because you included SET NULL for the auto_increment -- try the REPLACE INTO without that column

    as for INSERT ON DUPLICATE KEY, what UNIQUE key do you have defined besides the auto_increment? probably none, i'm guessing...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2012
    Posts
    204
    Rep Power
    3
    Originally Posted by r937
    so you are editing an existing row, this existing row gets deleted, and a new row is inserted, right?

    isn't this what you wanted?

    perhaps it isn't finding the old row at all, because you included SET NULL for the auto_increment -- try the REPLACE INTO without that column

    as for INSERT ON DUPLICATE KEY, what UNIQUE key do you have defined besides the auto_increment? probably none, i'm guessing...
    I have a unique on multiple columns..



    i tried to change 'dog' to 'cow' and it added a new row..
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,212
    Rep Power
    4279
    those screenshots don't really help

    could you please do a dump of the table -- generate the CREATE TABLE statement along with a few INSERT statements
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo