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
    Regular Coder
    Join Date
    Jun 2009
    Posts
    351
    Thanks
    75
    Thanked 0 Times in 0 Posts

    Need help with this simple query

    I want to insert all user and friendlink in friend_list table into friend_req but i only want to insert it if the friendlink is not in the profilelink field in the friend_req table

    Another words i dont want duplication.


    PHP Code:
    INSERT
      INTO friend_req
         
    (user,
         
    profilelink
         
    )
         
    SELECT user,
           
    friendlink
      FROM friend_list
      
     WHERE 
    .................... 
    Last edited by kevinkhan; 09-16-2011 at 09:43 AM.

  • #2
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,447
    Thanks
    71
    Thanked 102 Times in 101 Posts
    You need to use mysql_num_rows to check if a record exists. Such as:

    PHP Code:
    $query = ("SELECT * FROM friends WHERE profilelink='0'");

    if (
    mysql_num_rows($query) == 0) {
    //Insert Record
    }
    else {
    //Error record already exists


  • #3
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,506
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    DO NOT DO THIS FROM PHP!

    You have to loop through all records, one at a time. Ugh. (Besides, what if the programmer doesn't use or know PHP?)

    You want a MySQL answer!!

    Quote Originally Posted by kevinkhan View Post
    I want to insert all user and friendlink in friend_list table into friend_req but i only want to insert it if the friendlink is not in the profilelink field in the friend_req table
    Easy:
    Code:
    INSERT INTO friend_req (user, profilelink)
    SELECT user, friendlink FROM friend_list
    WHERE user NOT IN ( SELECT user FROM friend_req )
    Alternative way (may be faster, depending on what is indexed, etc., but is harder to understand):
    Code:
    INSERT INTO friend_req (user, profilelink)
    SELECT L.user, L.friendlink 
    FROM friend_list AS L LEFT JOIN friend_req AS R
    ON L.user = R.user
    WHERE R.user IS NULL
    Last edited by Old Pedant; 09-16-2011 at 06:04 PM.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #4
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,447
    Thanks
    71
    Thanked 102 Times in 101 Posts
    Old Pedant, that is really rude man. The nice thing about PHP is that there is a dozen different ways of coding. Each one of us has a different style. Just because it's not your way, doesn't mean it doesn't work.

    This code is designed to insert 1 row, due to 1 check. It's not designed for inserting 5,000 rows. My impression is that OP wanted to update single accounts, not everyone. If it is multiple then I misunderstood the OP.

    So get a life man.

  • #5
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,506
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    I want to insert all user and friendlink in friend_list table into friend_req
    Unless you can predict how many records are in the two tables, "all" carries to me the concept of "one heluva lot".

    In any case, this is the MYSQL forum, and how can you assume that he is even using PHP for his work? I use MySQL with JSP and ASP and ASP.NET. And also for stand-alone programming.

    I stand by my answer: Doing it in PHP is wrong on many levels. Not the least of which is efficiency, but there are other reasons it's wrong. I wouldn't have been so emphatic if "bendania" hadn't accepted the answer as gospel truth. But I would still call the answer wrong.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • #6
    Senior Coder
    Join Date
    Apr 2010
    Posts
    1,447
    Thanks
    71
    Thanked 102 Times in 101 Posts
    It could also mean insert all of the information into a record, but in any case. I've done my bit, I am walking away.

  • #7
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    25,506
    Thanks
    77
    Thanked 4,378 Times in 4,343 Posts
    Okay, I don't read it that way, but it's of course possible. I guess we will find out if kevinkhan comes back and tells us.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.


  •  

    Posting Permissions

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