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 4 of 4
  1. #1
    Senior Coder
    Join Date
    Mar 2003
    Location
    Atlanta
    Posts
    1,037
    Thanks
    14
    Thanked 30 Times in 28 Posts

    If no UPDATE....INSERT

    What is the best way to insert a row if it is not able to be updateable?

    PHP Code:
                    //Build SQL statement to update an appointment for a specified date.
                    
    $updateAppQuery "UPDATE appointments
                                            SET appTitle ='$title', appTime = '$time', appDetails = '$details'
                                                WHERE appDate = '$date'"
    ;
                    
    //Execute built SQL statement.
                    
    $updateAppResult mysql_query($updateAppQuery) or die ("Query 'updateAppQuery' failed. <b> Error Code </b> " mysql_errno() . ' ' mysql_error());

                
    //Record not found in the database so lets insert it.    

                     
    if (mysql_affected_rows($updateAppResult) == 0) {
                        
                        echo 
    "okay insert";

                        
    $insertAppQuery "INSERT INTO appointments (appTitle, appDate, appTime, appDetails) 
                                                               VALUES ('$title' , '$date' , '$time' , '$details')" 
    ;

                        
    $insertAppResult mysql_query($insertAppQuery) or die ("Query 'insertAppQuery' failed. <b> Error Code </b> " mysql_errno() . ' ' mysql_error());

    Most of my questions/posts are fairly straightforward and simple. I post long verbose messages in an attempt to be thorough.

  • #2
    Regular Coder
    Join Date
    May 2005
    Location
    Michigan, USA
    Posts
    566
    Thanks
    0
    Thanked 0 Times in 0 Posts
    LoL... you might not hear anything back on this... then again you might hear alot. I have a function that I have written in ASP to handle this situation. It is based on the assumption that the row you are updating is keyed on the where statment. If it is then you can use RegExp's to get out the info you need and to make it into a valid insert statment. I could show you my ASP code if you think it will help, but I doubt it will help any more then the writeup I just wrote.

    GL
    Note: I do not test code. I just write it off the top of my head. There might be bugs in it! But if any thing I gave you the overall theory of what you need to accomplish. Also there are plenty of other ways to accomplish this same thing. I just gave one example of it. Other ways might be faster and more efficient.

  • #3
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Personally, I prefer more explicit logic. What's one more sql statement?

    pseudocode:

    select count(primarykeycol) from tablename where key = keytoupdate
    if count == 1 then update
    if count == 0 then insert
    if count > 1 then there's a real issue here (or it isn't a primary key)

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #4
    Senior Coder missing-score's Avatar
    Join Date
    Jan 2003
    Location
    UK
    Posts
    2,194
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Or you could use mysql_affected_rows() to find out how many rows were affected in the update query, but the chances are I would use a SELECT statement first, then update/insert depending on the result (Like nikkiH says)


  •  

    Posting Permissions

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