PHP Count Logged-In Users

Question

I'm having trouble figuring out how I can count the number of logged-in users in my application.

What I have: When a user logs in, they get a session (which is used when the user wants to visit a protected page) and the IsLoggedIn column for the user in the users table is set to 1 to indicate that the user is logged in. When the user logs out, the value is set back to 0. Counting the number of 1s in the users table makes it easy to return the number of users logged-in. But...

The Problem: If the user closes the browser without logging out, the value in the database stays 1, indicating that the user is still logged in even though their session has ended when they closed the browser.

Question: Could some one suggest a proper way of doing this?

Answer 1

Rather than a IsLoggedIn column, you should add a LastTimeSeen column. Any time a person visits a page you update the column:

UPDATE members SET LastTimeSeen = NOW() WHERE id = $the_user_id

Then to get how many people are on the site at any given moment you use the query:

SELECT COUNT(*) FROM members WHERE LastTimeSeen > DATE_SUB(NOW(), INTERVAL 5 MINUTE)

That shows how many people have viewed a page in the past 5 minutes, which is the best you're gonna get without a much more complicated solution.