Querying a second table based on 1st queries results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mattichu
Forum Newbie
Posts: 5
Joined: Fri Sep 02, 2011 8:00 pm

Querying a second table based on 1st queries results

Post 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!

^.^
2-d
Forum Newbie
Posts: 6
Joined: Fri Sep 02, 2011 9:50 pm

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

Post 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!
User avatar
ok
Forum Contributor
Posts: 393
Joined: Wed May 31, 2006 9:20 am
Location: The Holy Land

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

Post 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.*").
mattichu
Forum Newbie
Posts: 5
Joined: Fri Sep 02, 2011 8:00 pm

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

Post 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 '='

:(
mattichu
Forum Newbie
Posts: 5
Joined: Fri Sep 02, 2011 8:00 pm

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

Post 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!
Post Reply