How to track user usage on site?

Question

I am using PHP/MYSQL. Each time a user logs in we insert the time they logged in and the date they logged in. While its easy for us to tell how many users logged in a particular day i am not sure how to calculate how much time they spent on the site.

For instance, just like a user signs in and we insert record into login_tracking table we also update that record when the user hits "logout" with the time they logged out. That way we subtract the time logged in and logged out and we get each persons time spent, but what happens if majority of people don't hit "logout" and instead clear cookies/cache or just close the window/tab the site was open in?

Is there a better way to do this? I am using Google Analytics, which gives me users and visits that showed up everyday but i want something proprietary to track usage?

Any ideas what i can do?

Update: @Paul asked if i was using Sessions. I am and this is how i am using it:

$username = $_SESSION["username"];

Answer 1

I suggest you use a "last used" field instead the field that holds the logout date/time, and a "first used" field instead of the field that holds the login date/time.

Whenever the user requests a page, you will check the "last used" value, if it was not long ago (i.e: less than 15 minutes for example), you will update it to the current date/time, else, you will have to create a new record in the database (assume that the user is in a new "session" right now, but don't have to create a new one). And, of course, associate the session with the newly created record.

This is a pseudo code:

Page Requested
if new session
    create session 
    create database record (first used=now, last used = now)
    associate the session with the record
else
    query the record associated with the current session
    if long ago
        create a new record (first used=now, last used = now)
        associate the new record with the current session
    else
        update the existing record (last used = now)

It looks easy, I didn't try to implement it though.

Answer 2

You can do this by setting a date time at the point of when they log on.

So when a user log on you update the mysql field which is DATETIME, something like...

 $q = $dbc -> prepare("UPDATE accounts SET loggedOn = NOW() WHERE username = ?");
 $q -> execute(array($_SESSION['username']));

Now all you would have to do is create a few more columns in your database table one called totalTime, and one called active or whatever you like upto you really.

On every page load the update active which is a DATETIME column also with NOW(),

 $q = $dbc -> prepare("UPDATE accounts SET active = NOW() WHERE username = ?");
 $q -> execute(array($_SESSION['username']));

This ensures that you know if the user is still online or not, you can check when they were last active. Now say if after 5 minutes of inactivity you want to find the total time you simply do,

 $q = $dbc -> prepare("UPDATE accounts SET totalTime = ADDDATE(totalTime, DATEDIFF(NOW(), loggedOn)) WHERE active < DATE_SUB(NOW(), INTERVAL 5 MINUTE)");
 $q -> execute();

What the above does is if users inactive for 5 minutes, it will take the loggedOn time minus it from the current time, leaving you with the total time on the site and add it to the DATETIME column totalTime.

Simply put the last query as a cron set too every 5 minutes and you have a quite accurate measure of peoples time on your site.

Answer 3

I made a simple website chat script doing something kind of like this.

What I did was store their session id and the time stamp in a table. Every page they visit, I would update the timestamp. When they log out, I destroy their log. I also had the update script check for other users that were "inactive" for 5 minutes and deleted them. You could accomplish the same thing by setting up a cron job to run every 5 or 10 minutes and check those timestamps which are older than 5-10 minutes and mark those users as inactive and then calculate total time spent on the site.

If you just want a count of users active at a specific time, I would look into http://whos.amung.us/. Its a simple plugin that counts the number of people online at any given time and where they are coming from.

Answer 4

Adjust your login_tracking table to include:

• session_id (varchar 100)
• user_id
• time (updates every 10 minutes for as long as there is activity)
• start (datetime, when they first login)
• end (datetime, when the session is killed either by closed browser or logout)

Code:

$date = date('Y-m-d H:i:s');
$time=time();
$time_check=$time-600; // user will be marked online if they have any activity for 10 minutes
$session=session_id();

// check if the user is currently listed as online in the database
$getactiveession = mysql_query("SELECT * FROM login_tracking WHERE session = '$session' AND end IS NULL");
$active_session = mysql_num_rows($getactivesession);

// if they are not listed as online, insert a row for them
if($active_session == "0"){
$make_active = mysql_query("INSERT INTO login_tracking (session, user_id, time, start) VALUES('$session', '$user', '$time', '$date')");
}
else {
// UPDATE user's current time if they are already currently logged in
$update_session = mysql_query("UPDATE login_tracking SET time = '$time', user_id = '$user' WHERE session = '$session'");
}

// get total users online
$gettotalonline = mysql_query("SELECT * FROM login_tracking");
$total_online = mysql_num_rows($gettotalonline);


// if over 10 minutes and no new activity (time column has not been updated), update end session time
$end_session_time = mysql_query("UPDATE login_tracking SET end = '$date' WHERE time<$time_check");

With this you can display a list of all online users as well with links to their profile, avatars, etc.

only thing I can think of is when inserting the new row for a new logged in user, you might have to do "WHERE end = '0000-00-00 00:00:00" instead of "WHERE end IS NULL", not sure if you can do a IS NULL on a datetime.

Response to your Comment #1

I have a SIMILAR thing as a function on my site which is located on every page therefor no matter what page my user is browsing they will be considered logged in. So it's easy to implement everywhere.

However, you will probably need to set part of it up as a cronjob to run every minute or 10 minutes to check for records over 10 minutes. I say this because...if a user closes their browser but does not log out, it will not update their record in the database until another person (logged in or guest) accesses the page in which the above code is being run on. If you have a cronjob running it then there would be no problem. (this also addresses your comment about the end session time getting executed, my site doesn't require that so happy you brought up the point, therefore a cronjob for you would be a must I think). As for redirecting them to the logout page, I'm not sure how that would work in this case seeing as their page is not being refreshed. That would probably need to be ajax or something.