PHP: Online Offline Status

Question

Hi since 3 hour I am trying to make this work but not getting the result as I want. I want to display user list with online and offline status.

Here is the table

and here what I tried to get status result.

$loggedtime = time() - 300; // 5 minutes

$query = 'SELECT userid, handle FROM ^users WHERE loggedin = '.$loggedtime.' ORDER BY userid ASC';

// below are scripts function qa_ pleses refer this http://www.question2answer.org/functions.php

$result = qa_db_query_sub($query);  
$users = qa_db_read_all_assoc($result); 

$row = mysql_fetch_array($result);

if($row['userid'] > $loggedtime){
    echo $row['handle'].' is online';
} else {
    echo $row['handle'].' is offline';
}

NOT THIS TOO

foreach($users as $user){
    if($user['userid'] > $loggedtime){
        echo $user['handle']. ' is online';
    } else {
        echo $row['handle'].' is offline';
    }
}

None of above code working. I am new to MYSQL and PHP just know basic so please help me to solve this.

EDIT: I have tried now this but not working

foreach($users as $user){
                if($user['loggedin'] > $loggedtime){
                    echo $user['handle']. ' is online';
                } else {
                    echo $row['handle'].' is offline';
                }
            }

EDIT 2

$query = "SELECT
    userid, handle, 
    CASE
        WHEN TIMESTAMPDIFF(SECOND, loggedin, NOW()) < 300
            THEN 'Online'
        ELSE 'Offline'
    END AS 'status'
FROM ^users
ORDER BY userid";

$result = qa_db_query_sub($query); 

while($user = mysql_fetch_array($result)){
    echo $user['handle'] . '<BR/>';
}

NEW APPROACH

Please check this for new approach User online offline status - offline status issue

Answer 1

Why not just check on the database side?

SELECT
    userid, handle, 
    CASE
        WHEN TIMESTAMPDIFF(SECOND, loggedin, NOW()) < 300
            THEN 'Online'
        ELSE 'Offline'
    END AS 'status'
FROM ^users
ORDER BY userid

You can use TIMESTAMPDIFF(unit,datetime_expr1,datetime_expr2) to return datetime_expr2 – datetime_expr1, where datetime_expr1 and datetime_expr2 are date or datetime expressions. One expression may be a date and the other a datetime; a date value is treated as a datetime having the time part '00:00:00' where necessary. The unit for the result (an integer) is given by the unit argument. The legal values for unit are the same as those listed in the description of the TIMESTAMPADD() function.

Take a look at the MySQL Date and Time Functions.

Also, I strongly advise using reserved words for table names.

Answer 2

Your structure looks funny to answer the question. Your loggedin field actually looks more like a "the last time they logged in". Just because you know when they logged in doesn't necessarily mean they are "online".

The reason your query isn't working is because you are comparing a UNIX timestamp to a mysql datetime. In addition, you are using = so unless they logged in EXACTLY five minutes ago, this will not work.

At minimum.

SELECT userid, handle FROM ^users WHERE loggedin > '.date('Y-m-d h:i:s', time()-300).'ORDER BY....

Answer 3

Since you fixed the user id comparison, let's address the next issue..

You're trying to compare a string DATE versus a unix timestamp. Let's make them the same type and compare:

foreach($users as $user)
{
  $user_time = strtotime($user['loggedin']);
  if($user_time > $loggedtime)
  {
    echo $user['handle']. ' is online';
  } else {
    echo $row['handle'].' is offline';
  }
}

Overall not the best way to approach this problem, but it might get this working for you. The database solution above is probably best.