Selecting online users from db with mysql command

I use this code to update the user database with the latest time when the user was online:

$postTime = $user_array['last_online']; 

if ($postTime == ""){
  $minutes = 11;
} else {        
  $now = time();
  $seconds = $now - $postTime; // $seconds now contains seconds since post time
  $minutes = ceil($seconds / 60); // minutes since post time
  //$hours = ceil($minutes / 60); //hours
}   

if ($minutes > 10) {
  $con = db_connect2(); 

  $sql = "UPDATE tbl 
             SET last_online='".strtotime("now")."' 
           WHERE userid='" .mysql_real_escape_string($_SESSION['userid']). "'"; 
  mysql_select_db('db',$con);
  $result = mysql_query($sql,$con);           开发者_如何转开发      
}

I am not sure if thats the best way to do it, but I think it works. Now I want to get all the users that have been active within the last 10 minutes with a mysql command. Is this possible. Something like

$time = time() - 600;
$online = $conn->query("select username 
                          from tbl 
                         where last_online < $time"); 

The code in the original post actually loads users who were online more than ten minutes ago, rather than within (less than) ten minutes. So, instead of checking whether last_online is less than ten minutes ago, we want to check whether it is greater than ten minutes ago.

$time = time() - 600;
$online = $conn->query("SELECT username FROM tbl WHERE last_online >= $time");

As your table is populated with more and more users this sort of query will become very slow. I'd recommend creating a new table like online or recent_users and whenever a user refreshes the page INSERT/UPDATE a row within that table. Periodically delete rows from the online table that have a timestamp older than 10 minutes.

Time is not a good column on which you may have an index, but year, month, day is. Good luck!

use SELECT username from tbl where last_online >= DATE_SUB(NOW(), INTERVAL 10 MINUTE)

try this (assuming you are storing the last_online data as unix timestamp):

select username from tbl where last_online < (UNIX_TIMESTAMP(now())-600)

I have one situation here and I think can help.

Actually I use a table acessPermission, on this table I have the columns Id, Ip, Page, Date.

My column Date is a type DateTime when I need t know in my reports about the access I had, I just retrieve the column Date from the database and I explode apart the time, and I compare like this:

Date from database

$exp=explode(" ", $date);
$exp[0]; // here I have the date
$exp[1]; // here I have the time

Today

$today = date("H:i:s"); //I have the time

Subtract the dates

$subt = ($exp[1] - $today);

The result is the subtraction of the hour only, you need to convert in a timestamp the result and compare it again I think, or you need to check the date, after the hour, after the minutes..

Now compare the time

if ($subt<600) {
 block intructions
} else {
 block intructions
}

I hope this can help a little.

Good Luck