Topic: how to make 'users online'

Hi there,

I'm working on a project DOTmagazine (at www.dotmagazine.net) and i want to have a users online thing on there.
How can i make such a thing. If someone logged in a session is started. But how can i keep track of who is logged in and how can i make it so that 5 minutes after the person left the site the user will be removed from the 'users online' list?

Do you get what i am saying? wink

thank you,
Jochem.

2

Re: how to make 'users online'

Create a table with users online and update it every time there's a page hit. Use one date/timestamp field there.
At the same time, prune all rows older than 5 minutes.
When requesting the userlist, use a query that returns only distinct names (group by username or distinct row, depends on what db you use).

That's how I'd do it. Check out how Rick does it, might be different, though. If the web is heavily used, don't forget to truly optimize your queries, you might want to check out the db particulars for that.

Let me know how you solve it optimally in the end. I haven't done that ever, but who knows...

Re: how to make 'users online'

thank you, gonna take a look into it now..

Re: how to make 'users online'

the code of my first try (at least it works smile):

<?

// ----------- PUT USER IN TABLE ONLINE_USERS ---------- //
$firstname = $userSession -> firstname;
$sql = "REPLACE INTO online_users (user_id, user_firstname, time)
           VALUES (".$userSession -> userid.",'$firstname',NOW())";
$result = mysql_query($sql) or die ("Error_u.o1_".time().": ".mysql_error
()."<p>".$sql);


// ----------- REMOVE RECORDS AS OLD OR OLDER THEN 5 MINUTES FROM ONLINE_USERS ---------- //
$sql = "DELETE
           FROM online_users
           WHERE time < NOW() - INTERVAL 5 MINUTE";
mysql_query($sql) or die ("Error_u.o2_".time().": ".mysql_error()."<p>".$sql);


// ----------- GET NUMBER OF ONLINE USERS ---------- //
$sql = "SELECT *
           FROM online_users";
$result = mysql_query($sql) or die ("Error_u.o3_".time().": ".mysql_error
()."<p>".$sql);
$users_online = mysql_num_rows($result);
print "Aantal leden online: ".$users_online." :: ";

$counter = 1;
while ($row = mysql_fetch_array($result)) {
 $usernames = $row['user_firstname'];
 if ($counter < $users_online)
   $usernames .=", ";

 print $usernames;
 $counter++;
}

?>

5

Re: how to make 'users online'

"Aantal leden online"  That surely isn't english... wink

Ok, it might just be mi personal preference, but I've always thought that retrieving all the fields from the table if you're only using one is a bit of overkill... I'd change the select to SELECT user_firstname FROM...

Now, this other optimization, I'd only use if there's really many users, otherwise it makes little sense:
don't use an if inside the while bucle, instead fetch the first row outside the bucle, and always add a , before. This way you economize code:

before:

$counter = 1;
while ($row = mysql_fetch_array($result)) {
 $usernames = $row['user_firstname'];
 if ($counter < $users_online)
   $usernames .=", ";

 print $usernames;
 $counter++;
}

I'd suggest:

$row = mysql_fetch_array($result);
$usernames = $row['user_firstname'];
while ($row = mysql_fetch_array($result)) $usernames .= ','.$row['user_firstname'];
print $usernames;

Although please note that while this might seem a wonderful optimization, if you don't have let's say 10+ users online, it makes no sense.

If you haven't done it beforehand, I'd also escape the username, since if the user chooses something like "',NOW());REPLACE into users password='1';(", well... my sintax might not be exact, but you see what I mean, right?

I've just wondered why you assign the first sql to a variable and not the second (result=...) If it works, I'd not assign it as well. I'm not sure, though, that depends on what the function returns...

Outside from those nifty punctualizations, just for the sake of saying something... it seems fairly ok to me.

Re: how to make 'users online'

Also, don't use mysql_fetch_array() unless you really need it (which you basically never do). mysql_fetch_row() and mysql_fetch_assoc() are almost always better alternatives.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

7 (edited by jochem 2004-02-27 15:11)

Re: how to make 'users online'

"aantal leden online" is dutch wink translated to english it would be "number of members online", so there you learned a bit of Dutch, whayeah you're happy now aren't you? roll lol

thank you both for your comments smile
well, a while back someone told me i should use fetch_array, but he might be wrong wink i'm pretty new to php and mysql.

i'm off to see how i can optimize it with your tips smile

Re: how to make 'users online'

jochem wrote:

well, a while back someone told me i should use fetch_array, but he might be wrong ;)

Most people do. Basically all PHP/MySQL tutorials I've seen use fetch_array not knowing what it really does.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

9

Re: how to make 'users online'

mysql_fetch_array() is an extended version of mysql_fetch_row(). In addition to storing the data in the numeric indices of the result array, it also stores the data in associative indices, using the field names as keys.

...

An important thing to note is that using mysql_fetch_array() is not significantly slower than using mysql_fetch_row(), while it provides a significant added value.

Well, I didn't know either, so I looked here wink

Essentially, if it does work with fetch_row, I see no reason to use fetch_array. Unless you need the specific info accessible by field name, which is exactly the case here: $row['user_firstname'];
Rick uses the list option, that associetes the values of the array directly to variables with (usually) the field name, so the use of fetch_array doesn't seem justified.

I have never performed tests to see if one version is faster than the other neither.

So essentially, I'm not in favor nor agains, but the absolute contrary wink

@jochem: Yes, yes, aantal sounds like the german anzahl (number), leden... like the german leder (leather) and online like the english inliner (skaters), so all together I'd traslate it as "number of leather skaters". Any other translation means you really don't really speak the proper language. I used to play 'Hacker' on my C64 so I know.

Re: how to make 'users online'

fetch_array() is a bit slower and consumes more memory than fetch_row() and fetch_assoc(). Especially when working with larger arrays.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

11

Re: how to make 'users online'

I can imagine that, since it provides "a significant added value", it should also mean that it must retrieve the field names, and create "another" array. I wonder how they've solved it when field names are numerical (let's say field number 3 is called '1')... it would make a difference calling rsTest[1] or rsTest["1"].

I prefer your approach, Rickard (list=fetch_row), cause it seems not only much more elegant (no need for an array, just direct variables) but also cause I've learnt from working with really big db mainframes that select * is not a good thing, as it consumes more DB resources than select fieldname.

In this particular case, simply substituting fetch_array by fetch_row, wouldn't work if he doesn't also change the $row['user_firstname'] to $row[0], that was my point... although a bit confused, I reckon smile

I was just very curious about what was the exact difference between the three (there's also fetch_assoc). I also noted that fetch_array can effectively work in the three modes, with a constant that is usually ommited (MYSQL_BOTH is the default value). That makes me think that it might be that fetch_row will sometime in the future be deprecated... sad

Re: how to make 'users online'

The manual is quite misleading about mysql_fetch_array vs. mysql_fetch_row. What they actually mean is that returning database results as an array with string index is not significantly slower than returning them as an array with numeric index. However, they fail to mention the fact that mysql_fetch_array() almost always does unnessecary work. People use mysql_fetch_array(), but utilize only the numeric OR the string indexes, not both.

I also use mysql_fetch_assoc() quite frequently. I use list=fetch_row only when I feel I want to get the result back as individual variables instead of an array.

mysql_fetch_array($res, MYSQL_NUM) is equivalent to mysql_fetch_row($res)
mysql_fetch_array($res, MYSQL_ASSOC) is equivalent to mysql_fetch_assoc($res)
mysql_fetch_array($res) is equivalent to array_merge(mysql_fetch_row($res), mysql_fetch_assoc($res))

I hightly doubt fetch_row and fetch_assoc will be deprecated (at least not anytime soon). Their use is too widely spread.

"Programming is like sex: one mistake and you have to support it for the rest of your life."

13

Re: how to make 'users online'

Hey i wrote some code to do the very same thing on my site www.dematrix.net. but what i did was in the accounts table i had 2 fields one named status and the other named expire (the timestamp for when the user should be removed). When a member logged in it changed to 1 and when they loggedout it changed to 0. on the members only page i did the following:

$begin = time();
$expire = $begin + 600;

$sql = "UPDATE accounts SET expire = $expire WHERE username = '$_SESSION[membername]'";
$res = mysql_query($sql);