PHP: SELECT and filtering in 2 tables at once

I would like to make a list to show the user´s friends, that are online.

users
id | firstname | lastname | last_access

users_friends
uID | bID | type | accepted 

That is how my tabels look like.

Firstly I have the user´s id in $USER.

The $USER´s id is stored in a row, in users_friends, at colum开发者_StackOverflown "uID". bID is the id of the friend.

I also need to check for accepted, if it's 1, else the user is "waiting" and havnt been accepted by the friend yet.

type should also be checked for "friend" as Im having other types there too.

To check whether they are online or not: last_access containing an unix timestamp.

500 seconds since last access, is what I call "online" in the community.

Now that is all you would need to know. I tried building the query myself and came out with this:

    $query = mysql_query("
       SELECT *
        FROM users u, users_friends uf
        WHERE 
            u.last_access > UNIX_TIMESTAMP()-500 
            AND 
              uf.uID = '$USER' 
            AND
            uf.type = 'friend'
                 AND 
                 uf.accepted = '1'
");

Now i would like to echo the firstname and lastname (from users) of the online friends.

I tried do a while()

while($get = mysql_fetch_array($query)){
echo $get["bID"]."<br>";
   echo $get["firstname"]."<br>";
}

I am getting my own user´s name($USER) displayed. But the echo bID is the right id´s for my friends, although it shows all (not filtering out the ones who are online).

How can I do this right, what am I missing?

I think you are missing to specify the relationship between the users and users_friends table

$query = mysql_query("
   SELECT *
    FROM users u
    INNER JOIN users_friends uf on u.id=uf.bid
    WHERE 
        u.last_access > UNIX_TIMESTAMP()-500 
        AND 
          uf.uID = '$USER' 
        AND
        uf.type = 'friend'
             AND 
             uf.accepted = '1'
");

Do

 $query = mysql_query("
   SELECT u.* FROM 
       users AS u
   INNER JOIN
        users_friends AS f
   ON u.id = f.uID
   WHERE 
        u.last_access > UNIX_TIMESTAMP()-500 
        AND 
            f.bID = '$USER' 
        AND
            f.type = 'friend'
        AND 
            f.accepted = '1'
");

This should give you your friends rows. Be aware of SQL injections with that $USER variable though.

Note: This will give you a list of people that are friends to you (you are on their friends list). If "friendship" is not bidirectional and you want to see only friends that are on your friends list you would exchange f.uID with f.bID and vice versa.

SELECT DISTINCT *
FROM users_friends uf
LEFT JOIN users u ON uf.bID =  u.ID
WHERE uf.uID = $USER
AND u.last_access > UNIX_TIMESTAMP()-500 
AND uf.type = 'friend'
AND uf.accepted = '1'