5 minutes of inactivity 1

[andyb2005]

Hi

Can anyone help with the following.

I have a sales_person table in a mysql db with a field called last_access.

What I'd like to be able to do is to display a list of sales people who are currently logged in (i.e still accessing pages within the last 5 minutes) to display a sort of 'whose online'.

 

[jpadie]

you can do this within the mysql query (assuming last_access is a datetime field type.

select * from sales_person where (DATE_SUB(CURTIME(), INTERVAL 5 MINUTES)<`last_access`)

 

[andyb2005]

Hi

I have tried the following:

$timeQuery  = "SELECT * from sales_person 
               WHERE (DATE_SUB(CURTIME(), INTERVAL 5 MINUTE) <= 'last_access')";

But the query isn't returning any rows, even though I can browse the table and see that my last_access time is there and is less than 5 minutes.

Any pointers?

 

[jpadie]

one point and two questions:
1. add an "s" to the end of MINUTE
2. what is the datatype of 'last_access'?
3. what version of mysql are you using?

 

[andyb2005]

Was getting errors when using MINUTES hence the reason for using MINUTE.

last_access is a datetime datatype.

Database server MySQL 3.23.56

 

[jpadie]

my apologies, MINUTE is correct

see

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

for more details on the functions in question.

sounds stupid but this should work! failing a myswl solution would you be ok with a php solution?

$now = strtotime("now");
$timeQuery  = "SELECT * from sales_person 
               WHERE (DATE_SUB(CURTIME(), INTERVAL 5 MINUTE) <= 'last_access')";
$result = mysql_query($timeQuery);
while ($row = mysql_fetch_assoc($result))
{
  if ((strtotime($row['last_access']) - $now) <= (5*60))
  {
     $resultset[] = $row;
  }
     //else do nothing
}
//you now have a resultset of only recent logons

 

[andyb2005]

Hi, thanks for the help thus far!

with the above code in place I don't get anything displayed, even though the last_access datetime is going into the database.

Could there be anything else wrong?

 

[jpadie]

it doesnt display anything. it just creates the array for you to manipulate.

use the array like this to display just a list of people. note the comment in the code

foreach ($resultset as $key=>$val)
{
   echo $val['name'] . "<br />"; //assume there is a field called name
}

 

[andyb2005]

Hi

so should the following display the results?

while ($row = mysql_fetch_assoc($result7))
{
  if ((strtotime($row['last_access']) - $now) <= (5*60))
  {
     $resultset[] = $row;
	 foreach ($resultset as $key=>$val)
      {
       echo $val['sales_person'] . "<br />"; 
      }
	 
	 
  }
     //else do nothing
}

 

[jpadie]

no you put the foreach loop outside the while loop otherwise the foreach will repeat the info every loop.

if you don't need to use the variable (other than to print) then you could delete the foreach and delete the line staring $resultset and replace with

echo $row['sales_person'] . "<br />";

 

[andyb2005]

Hi

Still not showing anything! I have added a check to test for the rows returned from the query and there aren't any.

It looks like the query must be wrong!

$now = strtotime("now");
$timeQuery  = "SELECT * from sales 
               WHERE (DATE_SUB(CURTIME(), INTERVAL 5 MINUTE) <= 'last_access')";

 

[andyb2005]

Hi

Still not showing anything! I have added a check to test for the rows returned from the query and there aren't any.

It looks like the query must be wrong!

$now = strtotime("now");
$timeQuery  = "SELECT * from sales_person 
               WHERE (DATE_SUB(CURTIME(), INTERVAL 5 MINUTE) <= 'last_access')";

 

[jpadie]

oops
delete the where clause.

 

[andyb2005]

errors in the SQL without it.

 

[jpadie]

post the whole code

 

[andyb2005]

$now = strtotime("now");
$timeQuery  = "SELECT * from sales where DATE_SUB(CURTIME(), INTERVAL 5 MINUTE) <= 'last_access'";
			   
$result7 = mysql_query($timeQuery) or die (mysql_error());
$num_rows = mysql_num_rows($result7);

if ($num_rows == 0)
{
echo "no rows from query";
}

while($row = mysql_fetch_array($result7))
{


 echo "getting into the WHILE statement";

  if ((strtotime($row['last_access']) - $now) <= (5*60))
  {
     echo "getting into the IF statement";
 	 echo "Sales Person Online is: " . $row[sales_person] . "<br>";
  }


}

 

[jpadie]

$now = strtotime("now");
$timeQuery  = "SELECT * from sales";
               
$result7 = mysql_query($timeQuery) or die (mysql_error());
$num_rows = mysql_num_rows($result7);

if ($num_rows === 0)
{
echo "no rows from query";
}

while($row = mysql_fetch_assoc($result7))
{


 echo "getting into the WHILE statement";

  if ((strtotime($row['last_access']) - $now) <= (5*60))
  {
     echo "getting into the IF statement";
      echo "Sales Person Online is: " . $row['sales_person'] . "<br />";
  }


}

 

[andyb2005]

Now we're getting somewhere ;o)

It simply pulls all the sales people - so it seems that it is not comparing the dates correctly. Could there be a problem with the value of $now. I just printed it as:1109077001

 

[andyb2005]

Thanks jpadie! Finally got it!

$last_access = strtotime($row['last_access']);
 
  if ($now - $last_access <= (5*60))

Just converted the string outside of the IF statement and reversed the division.

Many thanks for your kind help!

Regards

 

[jpadie]

How embarassing. got the subtraction the wrong way round, did I? I came top in the country in mathematics a-level too (albeit 20 years ago...)

glad you're working now.

Justin