Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 7 of 7
  1. #1
    ubh
    ubh is offline
    Regular Coder ubh's Avatar
    Join Date
    Apr 2008
    Location
    Portland, Oregon U.S.A.
    Posts
    443
    Thanks
    108
    Thanked 15 Times in 14 Posts

    INSERT INTO only if no record exsist

    Hey I am trying to figure out a way to preform either an UPDATE or a INSERT INTO depending on if a row exists or not.

    I am running an AJAX process that sends a series of elements inline CSS style values into my database.

    Problem is that there should only be one row for each element and then its CSS values should be stored in the proceeding columns. What I get however, because my AJAX process runs in a for loop and loops over all elements, is multiple rows each containing that particular loops sent CSS style.

    Looks like this:

    Code:
    |element_id|width|height|background-color|
    ------------------------------------------
    |first     |null |null  |#000000         |
    |first     |null |100px |null            |
    |first     |200px|null  |null            |
    What I need is to somehow in my php file, try and run a UPDATE process first, and if no row exists then perform an INSERT INTO query.

    Anyone know how to conditional a MySQL query like this??

  • #2
    Senior Coder CFMaBiSmAd's Avatar
    Join Date
    Oct 2006
    Location
    Denver, Colorado USA
    Posts
    3,011
    Thanks
    2
    Thanked 312 Times in 304 Posts
    If you are learning PHP, developing PHP code, or debugging PHP code, do yourself a favor and check your web server log for errors and/or turn on full PHP error reporting in php.ini or in a .htaccess file to get PHP to help you.

  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    The best way to do this is with a Stored Procedure.

    You just attempt the UPDATE and, if it fails, then you use an INSERT instead.

    Doing this all within a single simple SP is efficient and relatively simple.

  • #4
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    try insert ignore.

    it will insert if there is no duplicate and will ignore the insert if there would be a duplicate.

    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link

  • #5
    ubh
    ubh is offline
    Regular Coder ubh's Avatar
    Join Date
    Apr 2008
    Location
    Portland, Oregon U.S.A.
    Posts
    443
    Thanks
    108
    Thanked 15 Times in 14 Posts
    Ok maybe its me but I am not understanding how to work with this syntax here.

    This is what I assume its trying to come across as, but I still wont work.


    PHP Code:
    mysql_query("INSERT INTO structureandstyle
    (id, $rules, prevSibling, nextSibling) VALUES('$id', '$styles', '$prevSibling','$nextSibling') 
    ON DUPLICATE KEY UPDATE structureandstyle SET id='$id', $rule='$rule', prevSibling='$prevSibling', nextSibling='$nextSibling' WHERE id='$id' 
    "
    )or die(mysql_error()); 

  • #6
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,028
    Thanks
    75
    Thanked 4,325 Times in 4,291 Posts
    That's only going to work--and Bazz's suggestion is only going to work--*IF* the field that you don't want to duplicate is INDEXED and the index is specified as NO DUPLICATES allowed.

    There's nothing "natural" in a database that says you can't have duplicate values. Only a "unique index" can enforce that.

  • #7
    Master Coder
    Join Date
    Apr 2003
    Location
    in my house
    Posts
    5,211
    Thanks
    39
    Thanked 201 Times in 197 Posts
    Quote Originally Posted by ubh View Post
    Code:
    mysql_query("INSERT INTO structureandstyle
    (id, $rules, prevSibling, nextSibling) VALUES('$id', '$styles', '$prevSibling','$nextSibling') 
    ON DUPLICATE KEY UPDATE structureandstyle SET id='$id', $rule='$rule', prevSibling='$prevSibling', nextSibling='$nextSibling' WHERE id='$id' 
    ")or die(mysql_error());
    maybe the red bit should be changed by getting rid of the $.

    What error are you getting?
    bazz
    "The day you stop learning is the day you become obsolete"! - my late Dad.

    Why do some people say "I don't know for sure"? If they don't know for sure then, they don't know!
    Useful MySQL resource
    Useful MySQL link


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •