CODEWITHSUNDEEP
php
Johnson
Johnson

Reputation: 818

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 column "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?

Upvotes: 1

Views: 154

Answers (3)

Ted
Ted

Reputation: 2239

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'

Upvotes: 0

Henrik
Henrik

Reputation: 3704

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'
");

Upvotes: 1

Daniel Baulig
Daniel Baulig

Reputation: 10989

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.

Upvotes: 0

Related Questions