Total Site Users

Hiya! ,

I'm currently working on a script that will allow me to track the total users on my website, and how many of these user are logged in members, or guests.

I need to be able to find out the total users, total logged in, and total guests within the last 15 minutes, and the total users, total logged in, and total guests for today.

Ideally, I would like to user only one table for this, and would also like to group together records in the database that have the same users_id and ip_address, as there's not much point in storing this more than once.

I currently have a table that contains the following fields.

sid - Stores the users session ID.
user_id - Stores the user id of a logged in user. 0 is stored if the user is a guest.
ip_address - Stores the IP address of the user.
last_updated - Stores a timestamp of when the user was last active.

I look forward to reading your responses

Cheers!

 

i think you need to have execute these queries out if it..

on the last 15minutes:

# total users
SELECT COUNT(*) ctr FROM `the_sessions_table` WHERE TIMESTAMP(NOW())-TIMESTAMP(`last_updated`) <= 900; #900 is 15 minuties in seconds
# total logged-in users
SELECT COUNT(*) ctr FROM `the_sessions_table` WHERE TIMESTAMP(NOW())-TIMESTAMP(`last_updated`) <= 900 AND `user_id` != 0;
# total guest users
SELECT COUNT(*) ctr FROM `the_sessions_table` WHERE TIMESTAMP(NOW())-TIMESTAMP(`last_updated`) <= 900 AND `user_id` = 0;

Code (markup):

today:

# total users
SELECT COUNT(*) ctr FROM `the_sessions_table` WHERE DATE(NOW())=DATE(`last_updated`);
# total logged-in users
SELECT COUNT(*) ctr FROM `the_sessions_table` WHERE DATE(NOW())=DATE(`last_updated`) AND `user_id` != 0;
# total guest users
SELECT COUNT(*) ctr FROM `the_sessions_table` WHERE DATE(NOW())=DATE(`last_updated`) AND `user_id` = 0;

Code (markup):