Trium918 Posted July 26, 2007 Share Posted July 26, 2007 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? Quote Link to comment Share on other sites More sharing options...
btherl Posted July 26, 2007 Share Posted July 26, 2007 How do you record which members are active? Quote Link to comment Share on other sites More sharing options...
Trium918 Posted July 26, 2007 Author Share Posted July 26, 2007 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? Quote Link to comment Share on other sites More sharing options...
marcus Posted July 26, 2007 Share Posted July 26, 2007 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 Quote Link to comment Share on other sites More sharing options...
Trium918 Posted July 26, 2007 Author Share Posted July 26, 2007 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? Quote Link to comment Share on other sites More sharing options...
btherl Posted July 26, 2007 Share Posted July 26, 2007 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. Quote Link to comment Share on other sites More sharing options...
Trium918 Posted July 26, 2007 Author Share Posted July 26, 2007 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? Quote Link to comment Share on other sites More sharing options...
btherl Posted July 26, 2007 Share Posted July 26, 2007 "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. Quote Link to comment Share on other sites More sharing options...
Trium918 Posted July 26, 2007 Author Share Posted July 26, 2007 "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. Quote Link to comment Share on other sites More sharing options...
jitesh Posted July 26, 2007 Share Posted July 26, 2007 Attached is one script to test. modify class.UsersOnline.inc and queries as per need [attachment deleted by admin] Quote Link to comment Share on other sites More sharing options...
btherl Posted July 26, 2007 Share Posted July 26, 2007 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. Quote Link to comment Share on other sites More sharing options...
Trium918 Posted July 26, 2007 Author Share Posted July 26, 2007 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(); } ?> Quote Link to comment Share on other sites More sharing options...
btherl Posted July 26, 2007 Share Posted July 26, 2007 now() - '15 minutes', not now() .. there will never be users who last visited in the future Quote Link to comment Share on other sites More sharing options...
redarrow Posted July 26, 2007 Share Posted July 26, 2007 $query"SELECT user_name FROM members_info WHERE DATE_SUB(CURDATE(),INTERVAL 15 MINUTES) <= last_visit"; Quote Link to comment Share on other sites More sharing options...
Trium918 Posted July 26, 2007 Author Share Posted July 26, 2007 $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(); } ?> Quote Link to comment Share on other sites More sharing options...
Trium918 Posted July 26, 2007 Author Share Posted July 26, 2007 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(); } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.