PHP query to retrieve pal(friend) information and list thumbnails with links to pal information

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.

My users table contains fields e.g.

user_id, username, user_first_name, user_last_name...etc

I have a pals table: (user1_id, user2_id are FK of PK user_id in users table)

pal_id  user1_id      user2_id       status     timestamp
6              92            95         1        2011-02-02 21:44:58
7               98            97         0        2011-02-02 21:44:28
8              92            98         0        2011-02-02 21:44:28

pictures table where avatars are retrieved for display. If avatar = '1', that is the users avatar:

picture_id  picture_url                picture_thumb开发者_JAVA技巧_url                 user_id    avatar  timestamp
73  ../User_Images/66983.jpg    ../User_Images/Thumbs/66983.jpg          92           1     2011-02-01 21:41:59
74  ../User_Images/56012.jpg    ../User_Images/Thumbs/56012.jpg          94           0     2011-01-25 12:09:58

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

user1_id in the pals gets the user id of the initiator of the friendship. For pal_id=6, user 92 requested friendship, 95 confirmed it. Had user 95 requested friendship and it had been confirmed, user2_id would be reading 92...

Now, how will I display my PHP queries to show pal thumbnails and info?

My PHP code:

<?php require_once('Connections/connections.php'); ?>
<?php
//query username
$user_id = $_SESSION['UserSession'];
$user_id = mysql_real_escape_string($user_id);
$query_user_info = "SELECT username FROM users WHERE user_id='$user_id'";
$user_info = mysql_query($query_user_info, $connections) or die(mysql_error());
$row_user_info = mysql_fetch_assoc($user_info);

//code for displaying all your pals
$query_pal_no = "SELECT * FROM pals WHERE (user1_id='$user_id' AND status='1') OR (user2_id='$user_id' AND status='1')";
$pal_no_result = mysql_query($query_pal_no, $connections) or die(mysql_error());
$row_pal_no = mysql_num_rows($pal_no_result);

while ($pal_no = mysql_fetch_assoc($pal_no_result))
{
    //get pal info
    $query_pal_info = "SELECT users.user_id, user_first_name, user_last_name, username, picture_thumb_url, avatar FROM users LEFT JOIN picture ON
    users.user_id =  picture.user_id
    AND picture.avatar=1 WHERE users.user_id = {$pal_no['user2_id']}";
    $pal_info = mysql_query($query_pal_info , $connections) or die(mysql_error());
    $totalRows_pal_info = mysql_num_rows($pal_info );

    //echo table with pal information
    echo "\n<table>";
    $j = 5;
    while ($row_pal_info = mysql_fetch_assoc($pal_info))
    {
        if($j==5) echo "\n\t<tr>";
        $thumbnail_user = $row_pal_info['picture_thumb_url'] != '' ? $row_pal_info['picture_thumb_url'] : '../Style/Images/default_avatar.png';
        echo "<td width='100' height='100' align='center' valign='middle'><a href = 'user_view.php?user_id2={$row_pal_info['user_id']}'>
        <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>";
}
?>
<table width="500" border="0">
    <tr>
    <td height="20"><div class="heading_text_18"><?php echo $row_user_info ['username']; ?>'s&nbsp;pals <?php echo $row_pal_no ?></div>  </td>
    </tr>
    <tr>
      <td class="interactionLinksDiv" align="right" style="border:none;"><a href="#" onclick="return false" 
      onmousedown="javascript: toggleInteractContainers('pal_requests');">Pal Requests</a></td>
    </tr>
    <tr>
      <td height="5"></td>
    </tr>
</table>

I know the problem is after the WHERE in the query:

$query_pal_info = "SELECT users.user_id, user_first_name, user_last_name, username, picture_thumb_url, avatar FROM users LEFT JOIN picture ON
    users.user_id =  picture.user_id
    AND picture.avatar=1 WHERE users.user_id = {$pal_no['user2_id']}";

As I currently have it, It shows user 92's only pal who is user 95. This only works because 92 was the initiator and 95 the acceptor. The query would not work if 95 was the initiator as the query would return user 92(himself) as his pal.

What is the right query? Thanks for any help in advance

---

SELECT * FROM users u
INNER JOIN pal p
ON p.user1_id = u.user_id
INNER JOIN pictures pi
ON pi.user_id = p.user2_id
WHERE u.user_id = 92

This should give you all the palsand their thumbnails for user id 92.