Keeping track of Online Status

[doubledee]

What is the best way to keep track of a User's (real-time) online status?

 

Right now I have a Log In script and I set the status in the User's Session, and then "pass" that status from page to page with

session_start();

.

 

However, the problems with this approach include...

1.) A User is idle for hours or days and thus not really online

2.) A User leaves my site and thus is not really online  (Or does closing the window change that?)

 

I want a fairly accurate and real-time reading of whether or not a User is actively using my site so I can set their "Online Status" icon as green (online) or yellow (in active) or grey (offline).

 

Any idea how to accomplish that so I have monitoring like you'd see on an Instant Messenger App?

 

Thanks,

 

 

Debbie

 

 

[creata.physics]

You'd want a mysql table to store data into a database.

When a users session is created you would store that information into a database.

 

You'd have your script check and see if that users session is active, if that session hasn't been active for X amount of time, you'd have a script clearing that record from the database. That would cover a user leaving your site or timing out.

 

You won't need any extra session code other than what you currently have.

[doubledee]

I'm not following you...

 

You'd want a mysql table to store data into a database.

 

When a users session is created you would store that information into a database.

 

What data??

 

 

You'd have your script check and see if that users session is active,

 

How would it do that?

 

By checking something in the database?

 

 

if that session hasn't been active for X amount of time, you'd have a script clearing that record from the database. That would cover a user leaving your site or timing out.

 

You won't need any extra session code other than what you currently have.

 

 

I was thinking of doing this in my "log_in.php" script...

// Initialize Session.
session_start();

// Initialize Variables.
$_SESSION['loggedIn'] = TRUE;
$_SESSION['lastActivity'] = now();

 

 

And then at the top of every script I would also have this pseudocode...

session_start();
$_SESSION['lastActivity'] = now();
if (($_SESSION['loggedIn'] == TRUE;) AND ($_SESSION['lastActivity'] - now() > 30 minutes){
	echo statusOnlineActive.png;
}

 

But that won't work because I'm check now() against now() ?!

 

I guess I need to figure out wha actions make a User "active"?! 

 

 

Debbie

 

[l0gic]

session_start();
if (($_SESSION['loggedIn'] == TRUE;) AND ($_SESSION['lastActivity'] - now() > 30 minutes){
  echo statusOnlineActive.png;
  $_SESSION['lastActivity'] = now(); // If they're online, then update their last activity.
}

 

Along these lines, but store the information in a database rather than a session variable. As when you have a 'lastActivity' field in your users table you can then loop through all of your users and check when they were last online or not.

 

<?php
...
$result = mysql_query("SELECT * FROM users");

while($row = mysql_fetch_array($result)) {
  if($row['lastActivity']+300 < now()) { // if lastActivity plus five minutes was longer ago then now
    echo $row['userName'] . " is online<br>";
    // Green light
  }else {
    echo $row['userName'] . " is offline<br>";
    // Red light
  }
}
...
?>

[litebearer]

thinks about its (rhetoricals)...

 

1. what happens when someone visits a site? browser sends request to site, site send response to browser. Is browser still connected to site?

 

2. From some of your previous posts, you have articles people read. How much time must pass before they are considered inactive as opposed to online?

 

3. User reads article, opens second browser window to google info in your article, but switches back and forth between windows. Are they active, inactive or offline?

 

many more possible scenarios to consider

[doubledee]

I am trying to keep track of whether or not a User is...

- Online and Active

- Online but Idle

- Offline

 

 

Not sure exactly how to do this but I think I will need to pull up a User's "Last Activity" in MySQL and compare it to a Date & TIme in my PHP script.

 

As such, I am not sure whether to use DATETIME or TIMESTAMP in MySQL and whether to use MySQL's native date/time format or the Unix timestamp format thingy?!

 

I suppose it is also important to keep track of the Time Zone so there aren't any "Back To The Future" moments?! 

 

Can someone help me get this straightened out?!

 

Thanks,

 

 

Debbie

 

[l0gic]

I am trying to keep track of whether or not a User is...

- Online and Active

- Online but Idle

- Offline

 

As I posted above, and working in something like this http://www.brightcherry.co.uk/scribbles/jquery-auto-refresh-div-every-x-seconds/ to update the database while the user is still on the page but idle.

 

All you need is now in this thread.

 

- Online and Active: Compare database lastactivity to now(), if it's less than five (or however many minutes) difference then they're online

- Online but Idle: Compare database lastactivity to now(), and work in the jquery-type of stuff the link above, set it too 300 seconds for five minute 'checks'

- Online and Active: Again compare database lastactivity to now(), if last activity is longer than five minutes ago then they're offline

 

Now the learning part, make it happen!

 

Get some code together and see if you can get it working, if not come back with the code you're using and we'll see what we can do.

 

P.S. Personally I'd store the time as now() (a unix timestamp, a count of seconds, more recent times will always be a higher number) in the database in an INT field then use basic maths to sort it out, no need for time zones or anything.

[doubledee]

As I posted above, and working in something like this http://www.brightcherry.co.uk/scribbles/jquery-auto-refresh-div-every-x-seconds/ to update the database while the user is still on the page but idle.

 

Thanks for the link, but I am so totally not ready to learn JavaScript?!

 

-----

 

As far as "User Status" I think I left out something important...

 

When the current User reads an Article, at the bottom there can be Comments.  Next to each Comment is the Poster's Username, Online Status, Picture, and Comments.

 

So I want the READER to see who made each Comment and whether or not each Poster is Online/Offline.  (My site isn't like here where every page has Posters Info on it.  Just beneath Articles!)

 

That being said, I think what is reasonable for now is maybe something like this...

 

- Poster logs in and I update "logged_in" to 1 and "last_activity" to NOW() in the database

- Poster logs out and I update "logged_in" to 0 and leave "last_activity" alone in the database

- Poster navigates to a new page, so I update "last_activity" to NOW() in the database

- Poster sits idle drinking coffee, I do nothing with "logged_in" or "last_activity" in the database.

 

- READER lands on Article/Comments page and see current info on all Posters including coffee drinker since Now()-last_activity is now 20 minutes

- Reader sits idle for whatever reason, then the Reader only sees Posters' Statuses as of when they landed on page originally.

- Reader navigates to new Article and then sees current info on all Posters including those who have been idle.

 

Sound right?

 

Not completely "real-time", but I think more than sufficient for now.

 

Thoughts?

 

 

BTW, here is the code I am using to update the User's record...

// Build query.
$q2 = "UPDATE member
		SET logged_in=?,
			last_activity=NOW(),
			updated_on=NOW()
		WHERE id=?
		LIMIT 1";

 

In the database I see 2012-03-04 20:28:26.

 

What format is that?

 

Is that a native MySQL Date/Time Format or a Unix Date/Time?

 

And if I want to do a Date/Time difference comparison, how would I do that?  (e.g. Looking for 5 minute difference.)

 

 

Now the learning part, make it happen!

 

Get some code together and see if you can get it working, if not come back with the code you're using and we'll see what we can do.

 

I have it working so I see either a green or gray light when Posters are Online or Offline based on being "Logged In" or "Logged Out", but I haven't done anything yet with looking at "last_activity".

 

I'm not sure how fancy I even need to get?!    The goal is just to "tease" Readers with the idea that other Posters may be online and commenting while they are reading the Article.  That will hopefully encourage them to add more Comments...

 

 

P.S. Personally I'd store the time as now() (a unix timestamp, a count of seconds, more recent times will always be a higher number) in the database in an INT field then use basic maths to sort it out, no need for time zones or anything.

 

I chose DATETIME in MySQL for "last_activity", but see my questions/confusion above.

 

Thanks,

 

 

Debbie

 

[l0gic]

Sorry when I said now() earlier, I actually meant time().

 

Anyway, sounds like you're making pretty good headway.

 

The jquery stuff is essentially a copy/paste job, make these two files and put them in the same directory and then run them.

 

now.php

<script src="http://code.jquery.com/jquery-latest.js"></script>
<script>
$(document).ready(
function() {
  $("#updatecontainer").load("updateonline.php");
  var refreshId = setInterval(function() {
    $("#updatecontainer").load("updateonline.php");
  }, 5000); // The '5000' here is the time in miliseconds 5000 = 5 seconds, etc.
  $.ajaxSetup({ cache: false });
  }
);
</script>

<div id="updatecontainer">
In five seconds this will start updating every five seconds.
But you wont see any of this text and if you don't echo the
SQL in updateonline.php then you wont see any text at all!
</div>

 

updateonline.php

<?php
// Connect to your database server.
// $con = mysql_connect("localhost","user","pass");

// Select your database.
// mysql_select_db("my_db", $con);

// Build your SQL update string.
$sql = "UPDATE table SET lastactive = " . time() . " WHERE id = ?";

// Echo out your update string (remove this from final product).
echo $sql;

// Run your SQL update.
// mysql_query($sql);

// Close your database connection.
// mysql_close($con);
?>

 

If you watch it for 30 seconds or so you'll soon see what's happening, the SQL string is updating without the page refreshing and without you (the user) having to do anything. So aslong as the now.php page is open you can update your database to say they're online. I wouldn't run it every five seconds though, that's just a short time to show you how it works.

 

The second thing you'll see is why I prefer to use time() instead of any other time or date format, and why I store it in a regular INT field in my database. You don't need to know time zones or anything when measuring time like this and it makes it very basic maths.

 

As an example say I logged in at 1330935125 and my last activity update was at 1330935160 you can easily do this:

 

1330935160 - 1330935125 = 35

 

So I was online for 35 seconds, easy as!

 

So then you could figure out your online people by looping through them and marking them as online

 

if ( time() - lastactivity < 300 ) {

  user is online

}else {

  user is offline

}

 

You could include the updateonline.php OR code from now.php (above) on every page of your site except the log out page and your database will stay up to date. Including them both on one page would have it run updates twice per page load, which we don't want creating extra stress on your server.

 

I hope this helps anyway?

[doubledee]

Sorry when I said now() earlier, I actually meant time().

 

What format do I need to store time() in MySQL?

 

 

Anyway, sounds like you're making pretty good headway.

 

A little.

 

 

The jquery stuff is essentially a copy/paste job, make these two files and put them in the same directory and then run them.

 

I appreciate the code, but would like to try to do this without JavaScript...

 

Last night, I added some PHP that enters a "1" in the new field "logged_in" when a User logs in.  (I also added some PHP that enters a "0" in the field "logged_in" when the User logs out.)

 

This works quite well when I display all Users that posted a Comment under my Article, however I am realizing this problem...

 

If a User logs in and I set logged_in = 1 and then they close their browser window and/or leave that field is still set in the database?!  :-\

 

I guess there isn't really any way to update my database if the User closes their browser, right?  (I mean without using JavaScript.)

 

 

Debbie

 

 

[l0gic]

What format do I need to store time() in MySQL?

Just as INT.

 

I appreciate the code, but would like to try to do this without JavaScript...

You could do it without, you just wouldn't be able to 'update' anything until the user refreshed or requested another page.

 

Last night, I added some PHP that enters a "1" in the new field "logged_in" when a User logs in.  (I also added some PHP that enters a "0" in the field "logged_in" when the User logs out.)

 

This works quite well when I display all Users that posted a Comment under my Article, however I am realizing this problem...

 

If a User logs in and I set logged_in = 1 and then they close their browser window and/or leave that field is still set in the database?!  :-\

 

I guess there isn't really any way to update my database if the User closes their browser, right?  (I mean without using JavaScript.)

Now I think about it some more, I don't think I'd really use a 'logged_in' field in the database for that very reason, you'd only really get tto change it is the user used the log-out feature/link.. I'd stick to the database having 'last_activity' and use a session variable for 'logged_in'. I'd also try to use true/false rather than 1/0, but it doesn't really matter there I guess.

[doubledee]

l0gic,

 

I had an idea of how to do what I want just using PHP, but I am tired so it may have to wait until tomorrow?!

 

In the mean-time...

 

If I have a field called "logged_in" in my database and it is set to "1" by clicking on the "Log In" link, and it is set to "0" by clicking on the "Log Out" link - which most Users will NOT do, then is there some automated way to log out Users who do not click the "Log Out" link?!

 

Kind of radical, but you could run a cron job - or whatever - that looks for all "Logged In" Users whose "last_activity" is greater than 4 hours and log them out?!

 

I've heard lots of people over time tell me to store "Logged In"/"Logged Out" in the database, but logging Users out seems problematic?!

 

Thanks,

 

 

Debbie

 

 

 

 

[creata.physics]

Using a cron job to handle user sessions seems a bit too extreme, i'd never do it. Is the logged_in field inside the same table that registered users info is stored in?  Or do you have your own separate mysql table to handle user sessions?

 

Storing "Logging In"/"logged Out" in the database, what does that even mean?

 

There is a way to log users out automatically depending on how your script is set up.  I'd want to know how you're keeping track of a users session, I've already gathered that it's from a database, but I'm not sure what table contains what data.

 

If a user is logged in, and you have some information to check and validate they are logged in

if( $user['logged_in'] == 1 )

 

then have the script check and see how long ago their session was active

if ( time() - $user['last_page_refresh'] > 3600 )

 

What to execute within the arguments depends on how you store users sessions.  So if you have a field called logged_in being the only thing that determines a users online status, you'd want an update query to change that value to false, indicating that they are no longer offline.

[kicken]

You can have a flag field in your database indicated if they are logged in or out (though it's not strictly necessary).  You can update it either via a cron job or via an action on each page load (regardless of who did it).  I tend to do something like that by just putting a query for it in a common include file so it is run on each page load.  The query would be something fairly simple like:

 

UPDATE users SET
    logged_in=0
WHERE
   logged_in=1 
  AND DATE_ADD(last_activity, INTERVAL 5 MINUTE) < NOW()

 

That will find any logged in users who have not had activity in the last 5 minutes and mark them as logged out.

 

[l0gic]

I really think the long way around has been taken here..

 

A cron-job is overkill, as is the code on each page load. The means to track online status has already been explained earlier in the thread.

 

Personally, this is how I would do it.

 

User logs in:

$_SESSION['logged_in']=true;

$_SESSION['user_id']=1;

$_SESSION['user_name']="Bob";

..and any other session variables you'd want to set.

 

Now I have logged_in as a session variable, what would be the point of duplicating that logged_in value in a database when it's only going to remain true while the session is active/open. There really isn't one, I'd rather store the last_active time in the database because we can manipulate that much easier to suit out needs..

 

Database - Users Table (You'll have more fields than this but we're only ineterested in these few for now)

user_id   user_name   last_active
1         Bob         1331149677
2         Joe         1331153547
3         Sam         1331156466
4         Tim         1331153320
5         Dan         1331161333

 

From that you already have all the information you need to find out if a user is online or active. i.e.

if( last_active > time()-300 ) {
  user has been active within the last 300 seconds (five minutes)
}

 

And the last_active field would simply be updated with time() whenever they load or refresh a page..

UPDATE users SET last_active=time() WHERE user_id=$_SESSION['user_id'] AND username='$_SESSION['user_name']'

..with the user_id and user_name being a session variable that was set when the user logged in.

 

Now whenever your user loads a page the session remains active, so $_SESSION['logged_in'] remains true and their last_active time gets set to the current time with time() and that's all the information we need to capture for this aspect of the project.

 

Which makes displaying online or active users VERY easy on any of our other pages, for example seeing who is online in the list of comments about an article, and lets say its an article with the id of 333 in your database and your comments table shares a structure like this

comment_id   article_id   user_id   comment   date_posted

cooment_id is the unique identifier

article_id is the article to which the comment was posted on

user_id is the user id of the user that posted the comment

comment is the comment

date_posted is the date the comment was posted

 

$comments = mysql_query("SELECT user_id, comment, date_posted FROM comments WHERE article_id=333");

while($comments_row = mysql_fetch_array($comments )) {
  $user_row = mysql_fetch_assoc(mysql_query("SELECT user_name, last_active FROM users WHERE user_id = $comments_row['user_id']"));
  if($user_row['last_active'] > time()-300) { $user_status = "Online"; } else { $user_status = "Offline"; }
  echo $user_row['user_name'] . "(". $user_status . ") said: " . $comments_row['comment'] . " on . " $comments_row['date_posted'];
}

 

You can easily change $user_status = "Online" to a green circle image or whatever, same with the offline.

 

Hopefully this helps.. It may not be copy/paste/working as I didn't do any of this on my dev machine and I haven't even had my first coffee of the day but it should give you an understanding of how simple this should be to implement. Bascially the session variable for logged_on, the only thing you should be using that for is so that you know the user is logged on and can have access to certain data or features of the site, if they close the window or their session times out, it's not your problem. They'll just have to log in again.

 

Well, I hope it makes sense anyway.

 

You have all you need to know to get this working, and the only future questions I can imagine would be comparative to.. "How do I turn my Horse into a Unicorn?"

 

(In english: How do I make something that can't do 'this'.. Do 'this'?)

 

With the answer being along the lines of, "It doesn't work like that."

[kicken]

There are a couple benefits to using a flag vs calculating the value each time.

 

1) It can be indexed to improve query speed.  Not needing to calculate it every query would also be a plus.

2) It can simplify your queries and PHP code by not having to have logic to determine online/offline scattered in them

3) It gives you a central place to adjust the timeout if you decide to raise/lower it

 

For example re-writing your query for comments:

$sql = 'SELECT u.user_id, u.user_name, c.comment, c.date_posted, u.logged_in FROM comments c INNER JOIN users u ON u.user_id=c.user_id WHERE article_id=333');
$comments = mysql_query($sql);

while($row = mysql_fetch_array($comments )) {
  echo $row['user_name'] . "(". ($row['logged_in']?'Online':'Offline') . ") said: " . $row['comment'] . " on . " $row['date_posted'];
}

 

or without the flag and just using the activity field:

$sql = 'SELECT u.user_id, u.user_name, c.comment, c.date_posted, CASE WHEN DATE_ADD(u.last_activity, INTERVAL 5 MINUTE) > NOW() THEN 1 ELSE 0 END as logged_in FROM comments c INNER JOIN users u ON u.user_id=c.user_id WHERE article_id=333');
$comments = mysql_query($sql);

while($row = mysql_fetch_array($comments )) {
  echo $row['user_name'] . "(". ($row['logged_in']?'Online':'Offline') . ") said: " . $row['comment'] . " on . " $row['date_posted'];
}

 

 

Granted for a small site, any of the methods would work fine.  On a larger site or active site such as a busy forum, it would likely be worth while to add a logged_in type field and modify it with a cron each minute or two to keep your queries simple and quick.