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

[jmansa]

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 :-)

[guido2004]

Yes.
Give the layout of your tables please.

[jmansa]

Yes.
Give the layout of your tables please.

Users: userid,username,email
Group: groupid,groupname
GroupUsers: groupid,userid

Hope that's enough... Thanks

[guido2004]

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

[jmansa]

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 ;-)

[guido2004]

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.

[jmansa]

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???

[guido2004]

Add a join to the Group table

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.

[jmansa]

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:

    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:

$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...

[jmansa]

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...

[guido2004]

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.

[jmansa]

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 ;-)