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'
精彩评论