Track User Login & Spent Time Statistics

[coderzm]

Hi all,

 

I already have a simple login script.

 

However, I am trying to create a new feature that allows me to check the number of logins for a user, and the ability to filter by time periods and total time spent per session.

 

How do I make sure of the SESSION class to do that? Is it possible to do this via pure PHP?

Any advice is appreciated. Thanks.

[iarp]

Add a new column to the users table, call it whatever you want maybe TotalLogins, and within whatever script you use to login the user add a mysql statement

UPDATE USERS SET TotalLogins = TotalLogins + 1 WHERE id = <the user id>

That way you would know just how many times they have logged in.

 

For tracking session length you'll most likely need another table. It'll need to track Users ID, Session ID, Login datetime, Last Activity datetime.

 

When the user logs in, INSERT into this new table using the users id from login, session_id(), now(), now(). Then on every page you need an update statement that says something like

UPDATE UsersSessionTracker SET last_activity = NOW() WHERE users_id = <the users id> AND session_id = <session_id() from php>

Of course you'll need check to ensure the row exists and what to do if it doesn't...etc.

 

This isn't foolproof, it bases the session length on the last page load that they did from your website. If they site on that page for 15 minutes and then leave, you wouldn't know that they sat there for 15 minutes on a single page. For that type of tracking you would need Javascript that sends calls back to the server every x minutes to keep track..etc.

[scootstah]

I agree with iarp's solution, except you don't need the TotalLogins on the user table. You can just count the rows from the session tracking table.

[coderzm]

Hi iarp,

 

Thanks for your help.

 

I implemented TotalLogins  and it works pretty well. 

 

For the second part, I am not very clear about the session_id variable/method. How do I get the session_id and assign it to a variable? 

 

To clarify - The session_id is assigned to a variable at the login page or at the user registration page? 

 

 

Add a new column to the users table, call it whatever you want maybe TotalLogins, and within whatever script you use to login the user add a mysql statement

UPDATE USERS SET TotalLogins = TotalLogins + 1 WHERE id = <the user id>

That way you would know just how many times they have logged in.

 

For tracking session length you'll most likely need another table. It'll need to track Users ID, Session ID, Login datetime, Last Activity datetime.

 

When the user logs in, INSERT into this new table using the users id from login, session_id(), now(), now(). Then on every page you need an update statement that says something like

UPDATE UsersSessionTracker SET last_activity = NOW() WHERE users_id = <the users id> AND session_id = <session_id() from php>

Of course you'll need check to ensure the row exists and what to do if it doesn't...etc.

 

This isn't foolproof, it bases the session length on the last page load that they did from your website. If they site on that page for 15 minutes and then leave, you wouldn't know that they sat there for 15 minutes on a single page. For that type of tracking you would need Javascript that sends calls back to the server every x minutes to keep track..etc.

[iarp]

PHP itself has a function called session_id() that returns a string, use that during the INSERT and UPDATE statements. It will always be unique for every session that is running.

Edited July 6, 2015 by iarp

[coderzm]

Hi iarp,

 

Thanks for your clarification. 

Reference to your earlier example, where 

UPDATE UsersSessionTracker SET last_activity = NOW() WHERE users_id = <the users id> AND session_id = <session_id() from php>

If session_id is different every time a user logs in, how will session_id match current session_id()?

 

EDIT: Okay, I realized I missed a step. I am supposed to insert the session_id() where user logs in, and then update last_activity when a user hits a new page where session_id = session_id()

Edited July 6, 2015 by coderzm