Page 1 of 1

Querying a second table based on 1st queries results

Posted: Fri Sep 02, 2011 8:02 pm
by mattichu
hi

I have 2 tables (members & friendship)



Members table columns:
--------------------------------------------
username | password | email | etc ....
--------------------------------------------
Person 1 xxxxxx x@x.com
person 2 yyyyyy y@y.com

Friendship columns:

---------------------------------------
username | friend | status |
---------------------------------------
person 1 person 2 friends
person 2 person 1 friends
person 1 person 3 pending



I want to query the friendship table: "SELECT friend FROM friendship WHERE username ='$_SESSION['myusername'];' AND status='friends' "

(so its basically getting the user names of anyone who is the logged in users friend)

And then use the returned user names to select their data from the members table.

Any help much appreciated!

^.^

Re: Querying a second table based on 1st queries results

Posted: Fri Sep 02, 2011 10:43 pm
by 2-d
Hey there,

The best way to do this wouldn't be to run multiple queries. You should just do a mysql join and do it all in one. So you could do something like this:

Code: Select all

SELECT m.username FROM members m, friendship f WHERE f.username = '$_SESSION[myusername]' AND f.status = 'friend' AND f.friend = m.username
The idea behind this is to get the data from both columns and make sure the field friend from the friendship table matches the username of the members table. You may need to modify it to work the way you want though, I didn't test that out.

You can also google search mysql joins to get a better understanding.

Best of luck!

Re: Querying a second table based on 1st queries results

Posted: Sat Sep 03, 2011 12:29 pm
by ok
I think that using JOIN is the preferred method for querying multiple tables.

The query will be something like that:

Code: Select all

SELECT
  friendship.friend, members.*
FROM friendship
LEFT JOIN members
  ON friendship.friend = members.username
WHERE
  friendship.username ='$_SESSION['myusername']' AND friendship.status='friends'
This query will output all the friends of $_SESSION['myusername'] with their data from the members table (notice the "members.*").

Re: Querying a second table based on 1st queries results

Posted: Sun Sep 04, 2011 9:12 am
by mattichu
while trygin both these methods I get the following error:

#1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

:(

Re: Querying a second table based on 1st queries results

Posted: Sun Sep 04, 2011 9:53 am
by mattichu
mattichu wrote:while trygin both these methods I get the following error:

#1267 - Illegal mix of collations (latin1_general_ci,IMPLICIT) and (latin1_swedish_ci,IMPLICIT) for operation '='

:(
Solved this error :)

Many Thanks Guys!