Query Help

treeleaf20

I have three tables: users, friends, pics

each table has user_id as a field in it. I do this query to select name of the friend and then I do the second query to obtain the the friend_id of a user and put that into a link:

	$query = "SELECT f.FName"
         ." FROM friends AS fu"
         ." LEFT JOIN users AS u ON u.user_id=fu.user_id"
         ." LEFT JOIN users AS f ON f.user_id=fu.friend_id"
         ." WHERE u.user_name='".mysql_escape_string($_SESSION[Username])."' AND fu.confirm='Y' ORDER BY fu.date DESC LIMIT 0, 3 ";

					$queryfriend = "SELECT friend_id from friends Where user_name = '$_SESSION[Username]' ORDER BY date DESC LIMIT 0, 3";
					$resultid = mysql_query($queryfriend) or die("Select Failed!");

This works fine....now the problem is I would like to be able to do a seperate query to select the pics of each of the friends....so the query would need to get the friend_id from the friends table and then based on that select the user_id from the pics table.

Please help I can't figure it out at all....

Thanks!

maxpup979

Assuming you already have the friend_id, why not just do a join with friend_id=user_id?

DoctorWho

Have you tried running a 3rd query?

from your result sets, set up a new variable

for instance based on the $querfriend sql statement.....

while ($row = mysql_fetch_array($result3)) {
$friend_id2 = $row['friend_id'];
}

So what this does is create a new variable $friend_id2. That variable will of course be the result of the query you have run above to get the friend_id.

Then create a 3rd Sql statement, this time based on $freind_id2 and show those results.

...select Picture from yourtable where friend_id = $friend_id2

This may not be the best way or most logical way or even the fastest way, but to me it's the easiest way I can grasp it and actually do it.

treeleaf20

I tried the following but it didn't work:

	$querypic = "SELECT pic.pic"
         ." FROM pics AS pic"
		 ." friends AS fr"
         ." LEFT JOIN users AS u ON u.user_id=fr.user_id"
         ." LEFT JOIN users AS f ON f.user_id=fr.friend_id"
		 ." LEFT JOIN pics AS u ON fr.friend_id=pic.user_id"
         ." WHERE u.user_name='".mysql_escape_string($_SESSION[Username])."' LIMIT 0, 3";