[MySQL] Extend Who's online system

Recognize if the on-line user is also a 'friend' of the reader

[thijsnetwork]

Hello everybody,

I'm using this query to retrieve the screen- and usernames of al the members who were on-line in the past 15 minutes. My website has got larger and there's added an option for users to define who their friends are, stored in a new MySQL table called 'thijs_friends'. Now I want this query to select the on-line users whereby it the users who are on-line and also are marked as friends of the user executing the query separates from the 'normal' users.

Anybody knows how this can be done?

The SELECT statement may look something like this 'SELECT ..., is_friend FROM ...' where the fake 'is_friend' column can have the value 0 or 1? Perhaps with a temp-table or another JOIN?

Current query

$time_past = time() - 900;
SELECT thijs_users.screenname, thijs_users.username FROM thijs_online
LEFT JOIN thijs_users ON thijs_online.uid = thijs_users.id
WHERE thijs_online.tor > '$time_past'

MySQL Tables (simplified)

thijs_users
- id
- screenname
- username

thijs_friends
- uid
- fuid

thijs_online
- uid
- tor (time of request)

[coopster]

Hmmm. I assume you would want the original requester to be a part of the *online users* list?

[coopster]

// don't know where your userID is coming from, a session? 
$userID = (isset($_SESSION[userID])) ? $_SESSION[userID] : ''; 
$timelapse = 15; // in minutes

SELECT 
 thijs_online.uid, 
 screenname, 
 username, 
 tor, 
 fuid, 
 IF(fuid,'Y','N') AS is_friend 
 FROM thijs_online 
 LEFT JOIN thijs_friends ON (thijs_online.uid = thijs_friends.fuid) 
 LEFT JOIN thijs_users ON (thijs_online.uid = thijs_users.id) 
 WHERE thijs_online.uid = $userID 
 OR thijs_friends.uid = $userID 
 AND tor > NOW() - INTERVAL $timelapse MINUTE 
;

[thijsnetwork]

Well, the query must output a list of users:

- who were both on-line in the past 15 minutes and friend of the requester. (marked by 'is_friend')
- users who were on-line in the past 15 minutes and aren't friend of the requester.

Each user may not appear more than once in the list.

The requester himself doesn't have to appear in the list beceause he'll know he's on-line at the moment ;)

[ergophobe]

I think tweaking the WHERE a tiny bit will give you what you want

$query ="
SELECT
thijs_online.uid,
screenname,
username,
tor,
fuid,
IF(fuid,'1','0') AS is_friend
FROM thijs_online
LEFT JOIN thijs_friends ON (thijs_online.uid = thijs_friends.fuid)
LEFT JOIN thijs_users ON (thijs_online.uid = thijs_users.id)
WHERE thijs_users.id <> $userID
AND tor > NOW() - INTERVAL $timelapse MINUTE";

That will give you all users online except the current user and every user will have a flag in the 'is_friend' field that will by '1' or '0'

then you can just loop through and test whether or not it's a friend.

$friends = "Friends currently online: "; 
$others = "Others online: "; 
$result = mysql_query($query) 
while ($online = mysql_fetch_assoc($result) 
{ 
 if ($online['is_friend') 
 { 
 $friends .= $online['screenname'] . ", "; 
 } 
 else 
 { 
 $others .= $online['screenname'] . ", "; 
 } 
} 

Tom

[thijsnetwork]

Thanks for the reply!
Unfortunately, the query does executes without errors but it doesn't give any result at all. While there are 2 users online. (manually inserted in 'thijs_online'-table for debugging)

Do you know how to solve it?

[ergophobe]

I tested it with some test data and it worked fine, so I don't know.

[coopster]

I tested the other night but found issues. My first run on my test tables went fine with my original query with a user that was friends with everyone else that was logged in, but then I started running it as if I was one of the other online users that wasn't friends with the others and although the query is running correctly the result set is incorrect (with my original query as well as the update by ergophobe). I haven't had time to play around with it yet.

I've seen this concept before so this particular request certainly intrigued me. That's why I started goofing around with it. Won't be able to get back to it for a bit though...

[ergophobe]

Yup. I tested with slightly more complicated data and I find problems too. The query works but the result set is wrong (some users appear twice). If I have time tomorrow I might take another look at it. It seems so simple in concept, so it can't be that hard to get it right!

Tom

[coopster]

The

WHERE

clause logic should actually be a part of the JOIN. This works against my test data...

$userID = (isset($_POST[userID])) ? $_POST[userID] : ''; 
$timelapse = 15; // in minutes

SELECT 
 thijs_online.uid, 
 screenname, 
 username, 
 tor, 
 fuid, 
 IF(fuid,1,0) AS is_friend 
 FROM thijs_online 
 LEFT JOIN thijs_friends ON 
  (thijs_online.uid = thijs_friends.fuid AND thijs_friends.uid = $userID) 
 LEFT JOIN thijs_users ON (thijs_online.uid = thijs_users.id) 
 WHERE thijs_online.uid <> $userID 
 AND tor > NOW() - INTERVAL $timelapse MINUTE 
;

[thijsnetwork]

Great!

For as far as I can see now, this query works as it was ment to be! Thanks for all of the hard work!