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!
^.^
Querying a second table based on 1st queries results
Moderator: General Moderators
Re: Querying a second table based on 1st queries results
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:
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!
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
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
I think that using JOIN is the preferred method for querying multiple tables.
The query will be something like that:
This query will output all the friends of $_SESSION['myusername'] with their data from the members table (notice the "members.*").
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'
Re: Querying a second table based on 1st queries results
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 '='

#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
Solved this errormattichu 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 '='
Many Thanks Guys!