How to count online users?

Question

I am trying to count users that are now online, so I made these 2 queries

When logging in

$id = $_SESSION['id'];
$up = mysqli_query($conn,"UPDATE user_account SET status= 1 WHERE id='$id'");

When logging out

$id = $_SESSION['id'];
$up = mysqli_query($conn,"UPDATE user_account SET status= '0' WHERE id='$id'");

0 value is for offline while 1 is for online so it works fine, but I tried this on incognito browser mode I logged in to an account then I closed the incognito browser mode and the seassion id still exists and never turn 0 is there is way I can prevent that?

**Warning:** You are wide open to [SQL Injections](https://stackoverflow.com/a/60496/1839439) and should use parameterized **prepared statements** instead of manually building your queries. They are provided by [PDO](https://php.net/manual/pdo.prepared-statements.php) or by [MySQLi](https://php.net/manual/mysqli.quickstart.prepared-statements.php). Never trust any kind of input! Even when your queries are executed only by trusted users, [you are still in risk of corrupting your data](http://bobby-tables.com/). [Escaping is not enough!](https://stackoverflow.com/q/5741187) — Dharman, Mar 21 '20 at 12:57
You can implement timeout, but apart from that there is not many ways you can know if a user is actually using your website. You could implement something on client-side which pings your server constantly refreshing the active-time in your session, but this is unreliable. Web is stateless I am afraid. — Dharman, Mar 21 '20 at 13:00
@Dharman according to what a searched useing escape and trim should prevent it i have a function for that but this is for test anyway thanks — mhamad aras, Mar 21 '20 at 13:00
Please do not escape anything. This is a terrible advice which only makes your code complicated and is not guaranteed to prevent SQL injection. Use prepared statements like everyone else. — Dharman, Mar 21 '20 at 13:01

score 2 · Accepted Answer · answered Mar 21 '20 at 13:18

Your server can't ever know what a user does unless the user tells the server. For this reason people implement session timeouts. If there was no activity from the user for a certain period of time you assume they walked away.

You can create additional column in your database table called last_active of type DATETIME and you would update it each time a user makes a request to the server. If you want to count active users you simply make a query to fetch users active recently.

// Do this every time a user makes a request to server
$stmt = $conn->prepare('UPDATE user_account SET last_active=NOW() WHERE id=?');
$stmt->bind_param('s', $_SESSION['id']);
$stmt->execute();

// To fetch count:
$count_of_active_user = $conn->query('SELECT COUNT(*) FROM user_account WHERE last_active >= NOW() - INTERVAL 15 MINUTE')->fetch_row()[0];

Warning: You are wide open to SQL Injections and should use parameterized prepared statements instead of manually building your queries. They are provided by PDO or by MySQLi. Never trust any kind of input! Even when your queries are executed only by trusted users, you are still in risk of corrupting your data. Escaping is not enough!

score -1 · Answer 2 · answered Mar 21 '20 at 12:52

-1

Logically you can never know if a user closed a browser (whether it is incognito or normal mode), turned off computer, or any other possibilities. You only turn the status off when the users normally logs out using the button in your site and none of the other ways are being counted.

One way is to work with all sessions. You can find some info here. This way also does not turn off the status immediately.

Best way:

But I recommend you to use JavaScript on your site to tell the server that the user is online, each minute for example, (or any time the user interacts with the site which includes any request to the sever). And set a DATETIME value in database to store the last activity of each user on the site, so you can set a limit and say for example if it's more than 5 minuets from a user's last activity the the user is offline, else he/she is online.

answered Mar 21 '20 at 12:52

Kamyar Mirzavaziri

818
6
18

i did not wanted to use JavaScript because users can disable JavaScript in their browsers, but if there is not any other ways guess i will use that. – mhamad aras Mar 21 '20 at 12:57
1

@mhamadaras I think those users are really rare as almost all the websites use JavaScript, and you can count them offline :). I cannot tell name of any website without use of JS. – Kamyar Mirzavaziri Mar 21 '20 at 12:59
1

A little bit dated, but [here are several](https://www.webdesignerdepot.com/2018/01/8-sites-that-work-just-fine-without-js-thank-you/) without javascript. Obviously harder to find these days. – Paul T. Mar 21 '20 at 13:14

How to count online users?

2 Answers2