PHP query to select information from 3 tables about a user and his pals

I have these 3 tables:

users table(PK user_id)

Fields: user_id, user_first_name, user_last_name, username, user_email...etc

pals table (FK user1_id, user2_id from PK user_id in users table)

pal_id    user1_id    user2_id          status        timestamp
7           98              97               0     2011-02-02 21:44:28
8           92              98               1     2011-02-04 08:06:00
9           95              92               0     2011-02-04 08:05:54
10          97              92               1     2011-02-04 08:05:28
11          97              95               1     2011-02-04 08:06:33
9           92              93               1     2011-02-04 08:05:54
10          79              92               1     2011-02-04 08:05:28
11          97              95               1     2011-02-04 08:06:33

picture table (FK user_id from PK user_id in users table)

picture_id  picture_url             picture_thumb_url               user_id  avatar         timestamp
73      ../User_Images/66983.jpg    ../User_Images/Thumbs/66983.jpg     92     0    2011-02-03 21:52:02
74      ../User_Images/56012.jpg    ../User_Images/Thumbs/56012.jpg     93     1    2011-01-25 12:09:58
75      ../User_Images/58206.jpg    ../User_Images/Thumbs/58206.jpg     95     0    2011-01-22 22:12:35
76      ../User_Images/85428.jpg    ../User_Images/Thumbs/85428.jpg     98     0    2011-01-23 23:50:16
77      ../User_Images/42325.jpg    ../User_Images/Thumbs/42325.jpg     98     0    2011-01-24 00:11:15
78      ../User_Images/73154.jpg    ../User_Images/Thumbs/73154.jpg     98     1    2011-01-24 00:11:15
81      ../User_Images/92865.jpg    ../User_Images/Thumbs/92865.jpg     92     0    2011-01-31 18:24:34
82      ../User_Images/75611.jpg    ../User_Images/Thumbs/75611.jpg     92     0    2011-01-26 18:08:52
83      ../User_Images/74829.jpg    ../User_Images/Thumbs/74829.jpg     95     0    2011-02-01 20:48:48
84      ../User_Images/5987.jpg     ../User_Images/Thumbs/5987.jpg      92     1    2011-02-03 21:52:02

I am making a social networking site where I want to have a user's pals shown in a PHP generated table. I want to show pal thumbnails and other information below these thumbnails so that if you click on a thumbnail, it takes you to that user's profile.

From above, user 92 is pals with user 98 because they have confirmed friendship(shown as status = '1')

User 92 has 4 pals...he requested 2 and other 2 users sent him a request.

His pals are users 98, 97, 93 and 79.

Take a look at this:(explains how pals are added)

'A' added 'B'

user1 will be 'A' and user2 will be 'B' on pal table

and if 'B' added 'A'

user1 will be 'B' and user2 will be 'A' on pal table

Users can have pictures, stored in the picture table above. A user selects his avatar from his pictures...that is done by setting one of his picture.avatar = '1'.

Our user 92 has selected his own uploaded avatar pic. So have users 98 and 93. The other two pals have default avatar images. (all images are stored in a folder User Images).

MY Problem.

So far, e.g, in user 92's profile, I can only retrieve pal info for users that have avatars. How do I change my code to be able to get pals that have avatars and those that dont?

My PHP code:

<?php
//get pal info
$query_pal_info1 = "SELECT pals.user2_id AS pals_id1, users.user_first_name AS pals_first_name, users.user_last_name AS pals_last_name, picture.picture_thumb_url AS 
picture, picture.avatar AS avatar FROM pals INNER JOIN (users LEFT JOIN picture ON picture.user_id = users.user_id) ON users.user_id = pals.user2_id WHERE 
pals.user1_id = '$user_id' AND picture.avatar = 1 GROUP BY pals_id1";
$pal_info1 = mysql_query($query_pal_info1 , $connections) or die(mysql_error());

$query_pal_info2 = "SELECT pals.user1_id AS pals_id1, users.u开发者_如何学JAVAser_first_name AS pals_first_name, users.user_last_name AS pals_last_name, picture.picture_thumb_url AS 
picture, picture.avatar AS avatar FROM pals INNER JOIN (users LEFT JOIN picture ON picture.user_id = users.user_id) ON users.user_id = pals.user1_id WHERE 
pals.user2_id = '$user_id' AND picture.avatar = 1  GROUP BY pals_id1";
$pal_info2 = mysql_query($query_pal_info2 , $connections) or die(mysql_error());

//echo table with pal information
echo "\n<table>";
$j = 5;
while (($row_pal_info1 = mysql_fetch_assoc($pal_info1)) && ($row_pal_info2 = mysql_fetch_assoc($pal_info2)))
{
    if($j==5) echo "\n\t<tr>";
    $thumbnail_user = $row_pal_info1['picture'] != '' ? $row_pal_info1['picture'] : '../Style/Images/default_avatar.png';
    echo "<td width='100' height='100' align='center' valign='middle'><a href = 'user_view.php?user_id2={$row_pal_info1['pals_id1']}'>
    <img src='/NNL/User_Images/$thumbnail_user' border='0'/></a></td>\n";
    $thumbnail_user = $row_pal_info2['picture'] != '' ? $row_pal_info2['picture'] : '../Style/Images/default_avatar.png';
    echo "<td width='100' height='100' align='center' valign='middle'><a href = 'user_view.php?user_id2={$row_pal_info2['pals_id1']}'>
    <img src='/NNL/User_Images/$thumbnail_user' border='0'/></a></td>\n";
    $j--;
    if($j==0) {
    echo "\n\t</tr>\n\t<tr>";
    $j = 5;
    } 
}
if($j!=5) echo "\n\t\t<td colspan=\"$j\"></td>\n\t</tr>";
echo "\n</table>";
?>

In my queries: $query_pal_info1 and $query_pal_info2, I have ...AND picture.avatar = 1. I understand that this brings only my pals that have avatars. How do I change this statement to be able to get all pals, with or without images. Any help will be appreciated.

Remove AND picture.avatar = 1 from the query.

In the future, it doesn't hurt to copy the query into a sql editor, play around a bit, and see what predicate you need to remove to get the desired results.