SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Getting users from table depending on another table in 1 query?!?

    I want to be able to do this:

    Current user (u1) is member of 2 groups (g1,g2)

    g1 users: u1, u3, u4, u5
    g2 users: u1, u2, u5, u6

    users info is stored in another table (users: username)

    Now I want to be able to get the current users group friends printet out in a loop with there username but with no duplicates and without the current user... As you can see u5 is present in both groups...

    Can this be done in a single query, and if yes... Please tell me how :-)

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,396
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Yes.
    Give the layout of your tables please.

  3. #3
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Yes.
    Give the layout of your tables please.
    Users: userid,username,email
    Group: groupid,groupname
    GroupUsers: groupid,userid

    Hope that's enough... Thanks

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,396
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Code MySQL:
    SELECT
        Users.userid
      , Users.username
      , Users.email
    FROM Users
    INNER JOIN GroupUsers
    ON GroupUsers.userid = Users.userid
    INNER JOIN
      (SELECT
           groupid
       FROM GroupUsers
       WHERE userid = $userid
      ) AS g
    ON g.groupid = GroupUsers.groupid
    WHERE Users.userid <> $userid

  5. #5
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Code MySQL:
    SELECT
        Users.userid
      , Users.username
      , Users.email
    FROM Users
    INNER JOIN GroupUsers
    ON GroupUsers.userid = Users.userid
    INNER JOIN
      (SELECT
           groupid
       FROM GroupUsers
       WHERE userid = $userid
      ) AS g
    ON g.groupid = GroupUsers.groupid
    WHERE Users.userid <> $userid
    Works like a charm... Thanks. 1 question though... Is it possible also in the same query to get the groups out in the loop...? Just wandering ;-)

  6. #6
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,396
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Yes, but if someone is in more than 1 group, you'll get duplicate users
    That's no problem of course, just something to take care of in your loop, but you explicitly told in your OP that you didn't want duplicates.

  7. #7
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Yes, but if someone is in more than 1 group, you'll get duplicate users
    That's no problem of course, just something to take care of in your loop, but you explicitly told in your OP that you didn't want duplicates.
    But how do you get the groupnames into the loop with the users name???

  8. #8
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,396
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    Add a join to the Group table
    Code:
    SELECT
        Users.userid
      , Users.username
      , Users.email
      , `Group`.groupname
    FROM Users
    INNER JOIN GroupUsers
    ON GroupUsers.userid = Users.userid
    INNER JOIN
      (SELECT
           groupid
       FROM GroupUsers
       WHERE userid = $userid
      ) AS g
    ON g.groupid = GroupUsers.groupid
    INNER JOIN `Group`
    ON GroupUsers.groupid = `Group`.groupid
    WHERE Users.userid <> $userid
    Hmmm, it looks like 'group' is a reserved word in MySQL. You'd better rename that table to Groups or something like that, or use backticks around it in your queries.

  9. #9
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    Add a join to the Group table
    Hmmm, it looks like 'group' is a reserved word in MySQL. You'd better rename that table to Groups or something like that, or use backticks around it in your queries.
    Changed the tables so it looks like this:
    Code:
        SELECT
    			u.new_userid
    		  , u.fname
    		  , u.lname
    		  , u.email
    		  , cl.clubname
    		FROM fb_users u
    		INNER JOIN fb_club_users cu
    		ON cu.new_userid = u.new_userid
    		INNER JOIN
    		  (SELECT
    			   clubid
    		   FROM fb_club_users
    		   WHERE new_userid = $userid
    		  ) AS g
    		ON g.clubid = cu.clubid
    		INNER JOIN fb_club cl
    		ON cu.clubid = cl.clubid
    		WHERE u.new_userid <> $userid
    One problem though... How do I print out the clubs with the users...

    If I do this:
    PHP Code:
    $result mysql_query($sql);
    while(
    $row mysql_fetch_array($result)){ 
          echo 
    $row['new_userid'].' - '.$row['fname'].'<br>';

    Then I only get the the usernames... How do I get both the usernames and the clubs out of the same loop... with the clubs and the clubs and the cluids???

    Hope you can help with this last thing... So close...

  10. #10
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just to clarify it a little bit.

    If current user(u1) is member of 2 clubs (club1, club3) and the 2 clubs users are like this:

    club1: u1,u2,u4,u5,u9
    club3: u1,u3,u5,u6,u9,u12

    Then the output should be like this:

    club1,club3,u2,u3,u4,u5,u6,u9,012

    Hope this make sense...

  11. #11
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,396
    Mentioned
    147 Post(s)
    Tagged
    4 Thread(s)
    In the mysql result set, there's 1 club and 1 user per row. So if you want to display first all clubs, and then all users, you'll have to loop through the result set and for example save the data in 2 arrays (clubs and users), and then loop through the arrays and echo the data.

  12. #12
    SitePoint Guru
    Join Date
    Dec 2005
    Posts
    964
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by guido2004 View Post
    In the mysql result set, there's 1 club and 1 user per row. So if you want to display first all clubs, and then all users, you'll have to loop through the result set and for example save the data in 2 arrays (clubs and users), and then loop through the arrays and echo the data.
    Ahhh Yes... That makes sense :-)

    Thanks alot for your time ;-)


Bookmarks

Posting Permissions

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