User online offline status - offline status issue

Question

First this is related question of PHP: Online Offline Status But as my approch is little different and issue too so I thought new question would be better.

Now I have added one column to qa_users table called "online" which insert/update logging time and keep update upon user interaction (i am not sure is it right or not)

Now it is displaying user online as soon as they logged in but issue is after logged out they are keep display status as online and never go offline status.

I think something wrong with my time comparison conditional code but what I don't know.

Please find below code what I am using.

$loggedtime = date('Y-m-d h:i:s', time()-300); // 5 minutes

$query = 'SELECT userid, handle, online FROM ^users ORDER BY userid ASC';           
$result = qa_db_query_sub($query);  

while($ids = mysql_fetch_array($result)){

    $online = $ids['online'];
    $userid = qa_get_logged_in_userid();

    if(qa_is_logged_in()){
        $update = 'UPDATE ^users SET online = NOW() WHERE userid = '.$userid.'';

        qa_db_query_sub($update);
    }

    $time = $ids['online'];

    if ($time >= $loggedtime){ // i have tried with $loggedtime > $time too
        echo '<li>'.$ids['handle'].' online</li>';
    } else {
        echo '<li>'.$ids['handle'].' offline</li>';
    }               

}

Answer 1

Without knowing your DB structur it's kind of a guess.

if ($time >= $loggedtime)

You are comparing a string like '2012-11-01 10:10:10' with whatever $time is in your DB. This seems to be the problem here. You could/should use UNIX timestamps. They can be compared easily.

If $time were a UNIX timestamp you could just do:

if ($time >= time()-300)

EDIT:

Change your query to get a UNIX timestamp for online

$query = 'SELECT userid, handle, UNIX_TIMESTAMP(online) as online FROM ^users ORDER BY userid ASC';

EDIT2: To make it more clear: In your first version you were comparing two Dates in the form '2012-11-01 10:10:10'

if ('2012-11-01 10:10:10' < '2012-11-02 10:10:10')

This can't work in PHP - it's like doing:

if ('apples' < 'bananas')

You have to compare numbers. Therefore i suggested using unix timestamps which can be easily compared.

Answer 2

Let me start with this:

Please, don't use mysql_* functions in new code. They are no longer maintained and the deprecation process has begun on it. See the red box? Learn about prepared statements instead, and use PDO or MySQLi - this article will help you decide which. If you choose PDO, here is a good tutorial.

---

The approach I used for this sort of thing, is to not only save the logged in status of the user, but to save his last activity time as well.

Where activity may mean:

• Vitied a page.
• Submitted a form.
• Did anything on your site

A user is considered logged out if one of the following apply:

• User has logged out via the "Log Out" link.
• User has not been active for N seconds.

To check activity, you compare the last activity time against the current time. If the difference ($currentTime - $lastActiveTime) is greater than N, you consider the user as logged out.

The list of logged in users would be refreshed every couple of seconds (using a caching mechanism of some sort), where Inactive users would be UPDATEd to "Logged Out" in the database, thus saving you some query time and later processing time.