Searching for information!

[Trium918]

I am trying to write a script that will determine how many

users are online. How would I query the MySQL database

in search of active members?

[btherl]

How do you record which members are active?

[Trium918]

How do you record which members are active?

 

I am using sessions. I do not have a field inside the

database table. Should I add a field, and if so what

field type should I use?

[marcus]

You could have a field in a mySQL database that updates everytime a user views a page, then just have a page that selects the data from your database where the time field is less than 15 minutes

[Trium918]

You could have a field in a mySQL database that updates everytime a user views a page, then just have a page that selects the data from your database where the time field is less than 15 minutes

 

Thanks, but wouldn't it be easier to keep up with the user login and

the time the user logs out? Has anyone written a script that keeps up

with active users this way?

[btherl]

The problem with recording user logout is that not all users logout   So you need a timeout mechanism.

 

So let's say you choose 30 minutes as the timeout.  If you only record user login time, how do you know when to time out the user's session?  So you need to record also the last time the user was active.  That lets you time out their login after a period of time.

 

So once you have the "last active" time in the database, you can use that to count how many users are active, using whatever method you choose.  Could be "active in the last 10 minutes" or "last 30 minutes", depending on what is appropriate to your site.

[Trium918]

The problem with recording user logout is that not all users logout   So you need a timeout mechanism.

 

So let's say you choose 30 minutes as the timeout.  If you only record user login time, how do you know when to time out the user's session?  So you need to record also the last time the user was active.  That lets you time out their login after a period of time.

 

So once you have the "last active" time in the database, you can use that to count how many users are active, using whatever method you choose.  Could be "active in the last 10 minutes" or "last 30 minutes", depending on what is appropriate to your site.

 

I have a script already that keeps track of the users last visit. How would

I implement the timeout mechanism using the last visit?

[btherl]

"last visit" meaning the last time they accessed a page?  Is that data stored in the database?  If so, show us either the database table definition, or the queries which access that data, and we can help you with some SQL to find active users.

[Trium918]

"last visit" meaning the last time they accessed a page?  Is that data stored in the database?  If so, show us either the database table definition, or the queries which access that data, and we can help you with some SQL to find active users.

 

last_visit datetime NOT NULL default '0000-00-00:00'

 

When the user logs in, this query is ran.

$sql="UPDATE members_info SET last_visit=NOW() WHERE members_id='{$_SESSION['id']}'";   //now only update it
mysql_query($sql); // no need to assign a variable for an update statement.

[jitesh]

Attached is one script to test.

 

modify class.UsersOnline.inc and queries as per need

 

[attachment deleted by admin]

[btherl]

This query should list the active users:

 

$sql = "SELECT * FROM members_info WHERE last_visit > now() - '15 minutes'";

 

I'm not 100% sure of mysql's interval syntax, since i'm a postgres user, so that '15 minutes' may need to be something else.

 

If you have many users and the query is slow, an index on last_visit may help.

[Trium918]

I am getting no results

 

<?php
$online_query = "SELECT user_name FROM members_info WHERE last_visit > now()";
if($online_result=mysql_query($online_query)){
if (mysql_num_rows($online_result)) {
$num_online = mysql_num_rows($online_result);
echo $num_online;
} else {
      echo "No results found";
  }
} else {
    echo "Query failed<br />$online_query<br />" . mysql_error();
}

?>

[btherl]

now() - '15 minutes', not now() .. there will never be users who last visited in the future

[redarrow]

$query"SELECT user_name FROM members_info

WHERE DATE_SUB(CURDATE(),INTERVAL 15 MINUTES) <= last_visit";

 

[Trium918]

$query"SELECT user_name FROM members_info

WHERE DATE_SUB(CURDATE(),INTERVAL 15 MINUTES) <= last_visit";

 

 

For some reason the query below isn't calculating between the

last_visit and the current time.

 

How would I use the last_visit to calculate the number of users online

last_visit datetime NOT NULL default '0000-00-00:00'

 

<?php
$online_query = "SELECT * FROM members_info
WHERE DATE_SUB(CURDATE(),INTERVAL 15 MINUTE) <= last_visit";
if($online_result=mysql_query($online_query)){
if (mysql_num_rows($online_result)) {
$num_online = mysql_num_rows($online_result);
echo $num_online;
} else {
      echo "No results found";
  }
} else {
    echo "Query failed<br />$online_query<br />" . mysql_error();
}
?>

[Trium918]

This what I have now. Could someone please help me out

here? I am trying to write a script that displays the number

of active users online.

 

<?php
$timeoutSeconds = 900;
$num_online = 0;

$currentTime = time();
$timeout = $currentTime - timeoutSeconds;

$online_query = "SELECT id FROM members_info
WHERE last_visit <= $timeout";

if($online_result=mysql_query($online_query)){
if (mysql_num_rows($online_result)) {
$num_online = mysql_num_rows($online_result);
if($num_online == 1) {
echo "$num_online";
} else {
    echo "$num_online";
   } 
} else {
      echo "No results found";
  }
} else {
    echo "Query failed<br />$online_query<br />" . mysql_error();
}
?>