Total Site Users

[FishSword]

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!

 

[mikesta707]

If you want to do this efficiently you should use multiple tables. If you try to cram all the information you need in 1 table, traversing it will become a nightmare.

 

Now, lets take your problem step by step.

 

Total number of users is easy. Its simply the count of the number of registered users in your database. You can use the mysql COUNT() function or phps mysql_num_rows function to get this number.

 

For total logged in, and guests within last 15 minutes, i would have a seperate table for all the people currently logged in. This would contain an ip address column, as well as a column for username (or better, user id) with blank being a guest, and a timestamp of the last page activity. Every time someone visits a page, you look at their IP, and if it isn't in the logged in table, they are added. The total count of this table is the total amount of users (guests and registered) For guests, you can easily construct a query using the last page activity timestamp, and the user_id column (it should be blank, or equal to "", or perhaps -1. whatever makes the most sense to you).

 

Of course, you would then need to figure out a way of automatically taking people off of that table (perhaps after 30 minutes of inactivity you remove them from the table.)

[FishSword]

I need to know the total users, for both the last 15 minutes, and today,

Isn't it best to keep this information in just one table if possible, else aren't you duplicating information?

[mikesta707]

Yes and no. You are duplicating user_ids, but otherwise, doing a search on such a large table with a bunch of columns will make your queries less efficient. Giving up a little bit of space (the amount of which is negligible unless you are storing a huge amount of information) is usually worth the increase in efficiency. If you want to keep all the info in 1 table, thats fine I guess. It won't change the underlying logic much regardless. I was under the assumption that you stored more information on users than just the 4 columns you listed in your OP for some reason, so in your case it really doesn't matter too much. The only problem I would see is your are storing users with guests (or are users/guests the same? is there a registration process?)

 

for total users in the last 15 minutes would be the same logic for finding guests in the last 15 minutes except you dont user the column that specifies whether they are a user or a guest (assuming the two are different)

[FishSword]

This information is in it's own table. This table only contains information about the sessions, not user information such as username, and password etc.

 

When I say one table I mean can the total number of users online in the last 15 minutes and today be calculated from the same table, rather than duplicating information? I need to know the total users, total logged in and total guests for both.

 

I'm a little stuck with the guests, as what happens if the users logs out?