X users online

[mikawhat]

Hi,

I'm working on a script that displays the number of registered users online and their usernames. Once the users log in i add their details into a db.

Here's the relevant code for login.php

$query = "SELECT user_id, first_name, username FROM users WHERE username='$u' and password=PASSWORD('$p')";
$result = @mysql_query($query);
$row = mysql_fetch_array($result, MYSQL_NUM);

if ($row) { 

$_SESSION['first_name'] = $row[1];
$_SESSION['user_id'] = $row[0];
$_SESSION['username'] = $row[2];

$query = @mysql_query("INSERT INTO useronline (time, ip, server, username)	VALUES (CURTIME(),'$REMOTE_ADDR','$PHP_SELF', '$row[2]')");

ob_end_clean(); 

header ("Location: http://" . $_SERVER['HTTP_HOST'] .
dirname($_SERVER['PHP_SELF']) . "/index.php");
exit();

and here's the query in logout.php to delete the user from the db

$query = @mysql_query("DELETE FROM useronline WHERE ip='$REMOTE_ADDR'");

My first question is: if there are two users with the same ip and only one of them logout, then they will be both deleted from the db. So i want to make the query WHERE ip='$REMOTE_ADDR' and username='$username'. How can i retrieve the username from $_SESSION['username'] ?

My second question is: the script works fine as long as the users logout. but not all the users logout evarytime, most of them just close the browser, so their entry is still going to be in the db. Is there a way to delete those entries after a period of time?

[macpaul]

mikawhat wrote:

My first question is: if there are two users with the same ip and only one of them logout, then they will be both deleted from the db. So i want to make the query WHERE ip='$REMOTE_ADDR' and username='$username'. How can i retrieve the username from $_SESSION['username'] ?

Er.. could you not just say username=$SESSION['username'] ? Maybe i'm misunderstanding your question, but normally working with $SESSION vars is as easy as working with normal vars, near enough.

mikawhat wrote:

My second question is: the script works fine as long as the users logout. but not all the users logout evarytime, most of them just close the browser, so their entry is still going to be in the db. Is there a way to delete those entries after a period of time?

My approach would be to write some code that runs when a page is loaded up, as it's not possible (I believe) to do anything if a person closes their browser window.

When your login page loads, run a routine that compares the records in the db of people that are supposed to be online, against the session variables. Remove any records that can't be found in the active session.

Hope that helps!

[mikawhat]

i've tried username=$_SESSION['username'] but is not working..

I think your thought is probably the only one could be used as i believe too there's nothing to do when users closing their browsers.

Thanx for the reply.

[macpaul]

mikawhat wrote:

i've tried username=$_SESSION['username'] but is not working..

Have you tried preceding your call with session_start() ? I believe that trying to access any $_SESSION vars should implicitly start a session, but it doesn't always work for me, so I call session_start() first. Try that.

[mikawhat]

OK is working only with $_SESSION. It was my mistake, i didn't include it inside the {}. One more thing that i'd like to ask... i managed to update the time for every user after logging in, so now i want to include something in every page that will do the following:

var = current_time - session_time (where session_time is the time stored in the db)
if var > 5 minutes then delete user's session from db

I have a problem translating this into php because of the time format (hh:mm:ss). Can anybody help? thanks!

[bogu]

If u keep only the time, how do u determin thoes 5 min if the user session_time is 23:59:59 and the current_time is 00:01:59?

[mikawhat]

so i guess i should keep the date as well right? now it gets more complicated though...
var = 2006-06-15 23:59:59 - 2006-06-16 00:01:59

[bogu]

When u extract session_time u extracted as UNIX_TIMESTAMP and check :

var = mktime() - session_time <-- the result should be in seconds and if is bigger that (5*60 <- 5 min), the user has logged out ...

[macpaul]

mikawhat wrote:

I have a problem translating this into php because of the time format (hh:mm:ss). Can anybody help? thanks!

It's probably a good idea to convert both times into a timestamp and work out the difference:

now = time(); // gets timestamp for NOW

$sessionTS = strtotime("17:12"); // put your $_SESSION time here.

$minutesDifference = ($now - $sessionTS) / 60; // divide by 60 for minutes

A positive value means in the past, a negative value means in the future, though that's not likely to happen!

[mikawhat]

what's the correct syntax to extract in timestamp from the db? i tried several ways but i can't get it to work.

[macpaul]

It would help to see some code that you currently have. Are you using MySQL?

[mikawhat]

$now = time(); 
$ses = time stored in the db
$var = $now - $ses;
echo "<p>$var</p>";

i just want to get that value from the database..

[macpaul]

are you placing/retrieving data from the database already in your code? There's no 'fancy' way of retrieving this data, I'm just trying to work out whether you're already doing some database work on this project.

[mikawhat]

i'm just updating the times in the database by:

if (isset($_SESSION['username'])) {
	$user = $_SESSION['username'];
	$query = @mysql_query("UPDATE useronline SET time=CURRENT_TIMESTAMP() WHERE username = '$user'");
}

[macpaul]

OK then, what about

$query = mysql_query("SELECT timefield from useronline WHERE ...");
$row = mysql_fetch_array($query);
$storedTime = $row['timefield'];

Forgive me if I've misunderstood the question. I can't see the problem with extracting the data from the database. Is it that you've not yet extracted any data, only put it in a database? :-/

[mikawhat]

ok. with you code you extract the data, but what's the format?

[macpaul]

http://dev.mysql.com/doc/refman/4.1/en/time.html

The format is varied, but it seems by default that it's hh:mm:ss. if you extract this as shown in my code, you'll end up with a string. pass this through strtotime and get a timestamp. Then you can compare this to time(), which will give you a timestamp for now.

[mikawhat]

so this should be working?

$now = time(); 
$ses = strtotime($row[0]); 
$var = $now - $ses;

[macpaul]

In theory that should work. It should give you the difference between the two, in seconds. strtotime() simply looks at the string you give it and if it can make a valid date or time out of it, it will. Examples from the documentation:

<?php
echo strtotime("now"), "\n";
echo strtotime("10 September 2000"), "\n";
echo strtotime("+1 day"), "\n";
echo strtotime("+1 week"), "\n";
echo strtotime("+1 week 2 days 4 hours 2 seconds"), "\n";
echo strtotime("next Thursday"), "\n";
echo strtotime("last Monday"), "\n";
?>

To check for failure, use ===false (that's right, 3 = )

[mikawhat]

OK...with this code:

$now = time(); 
$ses = strtotime($row[0]); 
$var = $now - $ses; 

i echoed each variable and i saw that $var is 25200 . I don't know why though, as $now and $ses should be almost the same (maybe a couple of seconds different). Anyway with the following code it works, and after 5 minutes ($var > 25500) of inactivity the session expires..

if ($var > 25500) {
	$query = @mysql_query("DELETE FROM useronline WHERE username='$user'");
	$_SESSION = array(); 
	session_destroy();
}

so now it's fine and the problem has been resolved, even if i haven't yet understood why i get that 25200...

Thanks a lot for you help mate!