[PHP & MySQL] Realtime users online script

Introduction
I have been searching the internet for guide how to make a script to show how many users are connected. However, i didn't manage to find one, so i made it myself.

I am creating this guide for people who also aren't really experienced with PHP and is searching for something like this. This script may be a mess, but it works. :)

Creating a column in database

You have to create a new column in table (preferably at the end of a table). I used column named "online", so i will do the same here.
Important: Don't forget to edit your register.php and add one more ,' ' in registration code...
http://img715.imageshack.us/img715/4602/online1g.jpg


Editing login.php (it is going to change online column value from 0 to 1)
Since i think, that you already created database connection in login script, i think that this will be enough to show you. Thus, you should add it before you echo that login was successful.

Code:

---

// inserting online state
                mysql_query("
                UPDATE yourtable SET online='1' WHERE username='$username'"
                ) or die(mysql_error());

---

Editing logout.php (from 1 to 0)
I think that you haven't made any connections to database in this file, so i will include the whole code.

Code:

---

<?php

session_start();
//File which connects to database and selects table
include 'db_connect.php';

//Since my session is based on persons username... This code selects the user which is doing this proccess... (i think you already know that)
$user = $_SESSION['username'];
$query = mysql_query("SELECT * FROM yourtable WHERE username='$user'");
$numrows = mysql_num_rows($query);

// Inserting offline state
mysql_query("
UPDATE yourtable SET online='0' WHERE username='$user'"
) or die(mysql_error());
session_destroy();
echo "You have been logged out.";

?>

---

Users_online.php script
Checks how many rows of "online" column is set to 1 and gives the answer. Answer is the count of online (logged-in) users. :)

Code:

---

<?php

session_start();
include 'db_connect.php';

$query = mysql_query("SELECT * FROM yourtable WHERE online='1'");
$numrows = mysql_num_rows($query);
echo $numrows;

?>

---

Sorry for my bad english, if you think that it is THAT bad ;DD

And hope this will save some time for newbies at PHP. :)

And after member login to your site and presses [X] it will leave online column to 1

dang, u rather should work with an INT column that contains the timestamp and check the online users of the last 5 minutes. on every site action the user must update the timestamp with the current timestamp in the database to tell it he/she is online (active) again. on logout the users timestamp simply can be set to 0, so it wont be tracked in any case.

your online user php script simply counts all entries with a timestamp BIGGER than the current timestamp decreases by 300 to get all active users within the last 5 minutes.

cheers.

It's unnecessary to use the Sql database for this, alternatively you can use the PHP file IO function instead - store the data on a txt file.

Spread the workload, in this instance use PHP only, reserve the SQL priority to other database related tasks.

Quote:

---

Originally Posted by VibeTribe

dang, u rather should work with an INT column that contains the timestamp and check the online users of the last 5 minutes. on every site action the user must update the timestamp with the current timestamp in the database to tell it he/she is online (active) again. on logout the users timestamp simply can be set to 0, so it wont be tracked in any case.

your online user php script simply counts all entries with a timestamp BIGGER than the current timestamp decreases by 360 to get all active users within the last 5 minutes.

cheers.

---

I did like that. I didn't even knew how other people does it before I tried it, but it works.

For thread.
It can be done with $currTime = time(); and $Time = time()+300;
$Time is posted to database and site checks:

PHP Code:

---

$q = mysql_query("SELECT * FROM users WHERE online <= '$currTime'");
echo mysql_num_rows($q); 


---

Well,
PHP User online tutorial
Is actually a good solution to this :P...

Quote:

---

Originally Posted by Justei

Well,
PHP User online tutorial
Is actually a good solution to this :P...

---

Yeps. Not bad at all, but I think it's much easier to explain what it need to do in theory instead of reading full already made script.
(IMO)

Quote:

---

Originally Posted by getty

It's unnecessary to use the Sql database for this, alternatively you can use the PHP file IO function instead - store the data on a txt file.

Spread the workload, in this instance use PHP only, reserve the SQL priority to other database related tasks.

---

Are you sure? File I/O isn't very fast... Besides, a connection to a SQL database is likely already established if the application is using a user database in the first place.

share link mysql?

Do you drive a monster truck? because you're a gravedigger.

Quote:

---

$query = mysql_query("SELECT * FROM yourtable WHERE online='1'");

---

Just a tip. When you do querys where you don't pass any variables inside of them, you should not use "", use ' instead. It's all about performance. In cases of big applications this things can do major inpact on the over-all performance.

Quote:

---

Originally Posted by Macbeth

Just a tip. When you do querys where you don't pass any variables inside of them, you should not use "", use ' instead. It's all about performance. In cases of big applications this things can do major inpact on the over-all performance.

---

there is no matter what to choose, since " or ' is the same result. tho an integer variable shouldnt have any ' in the statement + this topic is outdated, why u bump it up with such unnecessary things?

Quote:

---

Originally Posted by Macbeth

Just a tip. When you do querys where you don't pass any variables inside of them, you should not use "", use ' instead. It's all about performance. In cases of big applications this things can do major inpact on the over-all performance.

---

Some professionals believe that premature optimization is the root of all evil. The problem with "s is they are inconsistent. Given the choice, you should ALWAYS use single quotes because of the inconsistencies found in "s.

Quote:

---

Originally Posted by VibeTribe

there is no matter what to choose, since " or ' is the same result. tho an integer variable shouldnt have any ' in the statement + this topic is outdated, why u bump it up with such unnecessary things?

---

" and ' is not the same result in PHP. Macbeth correctly notes that you can pass variables inside of double quotes, and not single quotes. Double quotes may result in silent unexpected results, making them dangerous to use. In fact, I find most features in PHP are dangerous to use due to inconsistencies, so I try very hard not to use the language.

Mk, now my turn to post.

I don't see what makes this Realtime... Clients are needed to display up-to-date content on-the-fly, you just supplied server-side code..

Quote:

---

Originally Posted by VibeTribe

there is no matter what to choose, since " or ' is the same result. tho an integer variable shouldnt have any ' in the statement + this topic is outdated, why u bump it up with such unnecessary things?

---

It doesn't matter if it's outdated or not, if someone has got some valuable content to post to the thread then why shouldn't it be posted. Just like now, you learned something new with these "'s which you obviously didn't know before.

Regarding if there's any matter or not to the use of the "'s I refer you to s-p-n's quote as there is no need to re-write it.

I wrote in the rules that this Tutorial's section in particular (of Coder's Paradise) is never outdated. If you find something useful to add, add it! This section is not for the thread starter, it's for passer-by's searching via google or coming here to find a specific tutorial. Most threads will go out-dated in two weeks, tutorials are always useful- but do get outdated. If a tutorial gets outdated, we should reply to improve it.

Couldn't agree more.

This isn't a good approach because a SELECT COUNT(*) against this table will lock it, and any login/logout action will want to lock a row to write to it, so it'll have to wait for any SELECT to finish. So if you have 100 logins/logouts per second and 50 of those are log-ins, with each request taking 8ms due to lock contention, you'll have 100*8 = 800ms + 50*8ms = 1200ms of work to do every second, but you only have 1000ms of time to do it. So this will fail at that low level of activity. This is also assuming that the SELECT COUNT(*) isn't slow, and it will be if this table has tens of thousands of rows, much slower than 8ms and the locking issue will actually be much worse.

A better approach is to use an atomic key/value store to keep track of how many users are online. This can also be used to track exactly which users are online, and their usernames so you don't have to perform the same SELECT against the users table for each user, and instead simply write whatever's in the cache. As others have pointed out, a simple on/off doesn't work in an online application where a user is "online" if they recently did something (because they aren't required to touch your logout page before leaving). In this case, you can either back an activity-based timestamp in a user table with a K/V query with expiration on write, which is the straightforward and easy solution, or you can do something a bit more complex with a means to fire triggers on expiration. Memcached and Redis don't currently support this so it'd have to be custom (it could be done pretty trivially in Node.js), but it would scale much better at the cost of complexity. In the simple solution, a good solution would be to have an activity table that keeps track of the last time each user performed an action (mutable actions, not GETs as these may be cached). A simple tuple table with an fkey to the user table on id (thus an index on id) and a timestamp field will have very little write-lock contention and a simple use of Memcache or Redis to cache a user count where activityTime > some timestamp can be stored for 5-30 seconds and replaced each time it expires, meaning the amortization of the read lock will be trivial, especially since it can be done against a slave.

I should point out that if you just want the number of records in a query, you should use COUNT(*). The code presented here will return a cursor, which uses resources, and requires an implicit ordering, which makes the operation much slower.

Quote:

---

Originally Posted by jMerliN

-snip-

---

This is a great post... but I'm worried not very many people will be able to.. understand it.

Let's see if I can trivialize it; please do correct any mistakes I may make jMerliN.

WhiteWizz: Your script will cause the MySQL table to lock up - even with a very low amount of activity. This is bad. Really bad. No one should use your script because of this.

jMerliN offers two alternative approaches.
The simpler one is as follows: Have an activity table that links users to their last active time. Whenever the user does something on your website that signifies they are online, record the current timestamp in that activity table. Then you can just run a query every 5-30 seconds(or whatever interval you want it to update at) to get the count of rows that were active recently.

The more complex one: I don't fully understand this, partially because jMerliN didn't appear to go into much detail, but I'll do my best to explain it. You fire a trigger when the user's active session expires, which would, I assume, update the current active users list. Due to limitations in key/value stores, jMerliN recommended building this in Node.js.

Do note that the more complex solution is more scalable than the simpler solution.

Obviously, jMerliN goes into more technical detail about both of the above solution in his original post. He justifiably recommended the use of a key/value storage such as Memcache or Redis rather than using a relational database such as MySQL. jMerliN, I'm not sure if you were recommending or not the use of MySQL for either solution in your post, so if you were, please do correct me.

As an example, I've made the following schema:

Code:

---

CREATE  TABLE `ragezone_test`.`users` (
  `id` INT NOT NULL ,
  `name` VARCHAR(45) NOT NULL ,
  `active` TINYINT NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`id`) )
COMMENT = 'Your run-of-the-mill user table, not normalized, as an example.';

---

I filled this with 10000 fake users, with names of 'bob0000' to 'bob9999'. As a stress-test, I wrote this Node.JS script.

It will attempt to perform 400 log-ins and 400 log-outs and 400 SELECT COUNT's to simulate a count after a successful login to show the user how many people are logged-in. Once all requests are done, it determines how long this entire process took.

After a few runs, I find that the average time taken for this (1200 requests total), is roughly 2400ms. That means each request is taking roughly 2ms, and the maximum throughput we can handle here is 500 requests/second. Not terrible. Now I'll try a few things:

1. I'll change it from a COUNT(*) to a SELECT *
2. I'll remove the COUNT(*) to get an idea of the cost of the writes alone

In the first case, I find the cost is now 17000ms, which is SEVEN times the cost in time, it uses 4x as much CPU, and 2x as much memory. Needless to say, use COUNT(*) if you just want a count. In the second case, it takes just around 850ms, which is a little more than 1ms per request (800 vs 1200, since we cut out the COUNTs). Twice as fast, and we can easily perform over 800 requests a second, pretty sexy improvement.

So now we have some baseline ideas on how fast this is using a purely-MySQL driven approach. I'll go ahead and implement the simple alternative I gave for a reference. In the first suggestion, I'm creating a table called 'active_users'. Here's the table creation:

Code:

---

CREATE  TABLE `ragezone_test`.`active_users` (
  `user_id` INT NOT NULL ,
  `active_time` TIMESTAMP NOT NULL DEFAULT 0 ,
  PRIMARY KEY (`user_id`) ,
  INDEX `active_users_idx` (`user_id` ASC) ,
  CONSTRAINT `active_users`
    FOREIGN KEY (`user_id` )
    REFERENCES `ragezone_test`.`users` (`id` )
    ON DELETE CASCADE
    ON UPDATE CASCADE);

---

Then I fill this table with 10000 entries, one for each user, with the default value of 0. I'll run my original example on this, with a modified query to COUNT(*) where active_time is greater than a timestamp (new code). The results are as follows:

1. Identical to the previous but using `active_users` and a modified COUNT(*): 2400ms
2. Now using no COUNT(*): 850ms

So similar numbers. Now I'm going to re-write this slightly to do a continuous stress test. It'll try to push as many requests as possible and count how many requests we've seen, and every 1000ms displaying the current throughput to stdout. Same code, just continuous, and instead of dumping everything in a for loop, I'll issue a repeat request in the callback of each do_logout, do_login, and do_usercount. Each will increment a global that will keep track of how many requests we've handled.

Sample output (using full COUNT):

Code:

---

Throughput: 515 r/s
Throughput: 522 r/s
Throughput: 492 r/s
Throughput: 522 r/s
Throughput: 509 r/s
Throughput: 516 r/s

---

Now, I'm going to mimick the use of a K/V store. Because I don't want to use Memcache or Redis here, I'm going to implement a very naive method of expiring keys. I will only use this to expire the actual count, and on each expiry I will re-compute the count. This is doable via PHP with Memcache by simply checking if the count key exists, if not, perform the COUNT query, otherwise, just use the existing value.

Output using expiring count, recomputed every 2 seconds:

Code:

---

Throughput: 902 r/s, currently active: 4905
Throughput: 956 r/s, currently active: 4890
Throughput: 958 r/s, currently active: 4890
Throughput: 933 r/s, currently active: 4931
Throughput: 972 r/s, currently active: 4931
Throughput: 972 r/s, currently active: 4956
Throughput: 986 r/s, currently active: 4956
Throughput: 959 r/s, currently active: 4960
Throughput: 919 r/s, currently active: 4960
Throughput: 960 r/s, currently active: 4963

---

It should be pointed out this solution uses almost zero CPU compared with the COUNT version. This also scales very well. Here's the same test run with 1 million rows (and a 5 second expiry, which is far more real-time than most sites need, a 5-minute update would be sufficient for most):

Code:

---

Throughput: 990 r/s, currently active: 13269
Throughput: 949 r/s, currently active: 13269
Throughput: 964 r/s, currently active: 13269
Throughput: 655 r/s, currently active: 13258
Throughput: 962 r/s, currently active: 13258
Throughput: 960 r/s, currently active: 13258
Throughput: 1013 r/s, currently active: 13258
Throughput: 946 r/s, currently active: 13258
Throughput: 661 r/s, currently active: 13283

---

But it's pretty clear what happens every 5 seconds here when the COUNT is updated. The disparity between the 'currently active' is because of how I'm selecting records to retire. Since the probability space is so much larger, the log-in code runs pretty much unchecked until it stabilizes at the maximum number of active users (~450 * 30, since I'm expiring sessions after 30 seconds). And with COUNT:

Code:

---

Throughput: 9 r/s
Throughput: 9 r/s
Throughput: 9 r/s
Throughput: 9 r/s
Throughput: 9 r/s
Throughput: 9 r/s
Throughput: 9 r/s

---

I won't write an example using no COUNTs because it's quite a bit more complicated, and might be best solved using a fibonacci heap or something similar. You basically need a priority queue with very high performance for removing smallest nodes (expired) and very good performance for re-balancing the heap after an update (increment). That's more work than I'm willing to put into an example, for now. I'll probably end up writing this in one of my projects, in which case I'll post the code here. The benefit of this solution, while complicated, is that it doesn't need a backing table, which means it can achieve thousands to tens of thousands of requests per second, and scales extremely well. Not really necessary for most websites, I would point out.

Also, final code.

Edit: Added the below quick implementation of the second idea as an example.

Here's a crude implementation using a HashMap with 10000 simulated users. No backing table, but it doesn't scale as well as it could because of thrashing in the backing array for the hash map and because the cleaning code is O(n^2) or O(n^3) (because it's obviously O(n) but it also does deletes), where n is the number of active users. Better structures could eliminate almost all of these overheads.

Code:

---

Throughput: 114298 r/s, currently active: 5024
Throughput: 113380 r/s, currently active: 5035
Throughput: 113776 r/s, currently active: 5022
Throughput: 114394 r/s, currently active: 5006
Throughput: 113686 r/s, currently active: 4983
Throughput: 113830 r/s, currently active: 4969
Throughput: 116638 r/s, currently active: 5048
Throughput: 113740 r/s, currently active: 5000
Throughput: 113572 r/s, currently active: 4982

---

The currently active figure isn't even useful here. With how fast this operates, this model isn't even capable of representing real usage. For one, logouts would be very rare, but they would also only occur to a person who's active. For two, activity isn't registered only as a log-in, and while "login" is a misnomer here, the approximation is close to correct, but it's unlikely a completely random selection of users would be performing action, rather it would be some fraction of users repeating lots of activity (thus massive updates to already existing timestamps), and some other fraction with a pattern of action followed by no action until the timeout occurs (insert then delete before re-insert). There's also some fraction of users who don't ever really log-in, so with 10000 registered users, a site might only have 500 active users. This is capable of such a high throughput in a good case that this simulation immediately balances at half of the user base logged-in and generally has a very full users map, which makes this implementation scale particularly horrendously under this synthetic test. In a realistic case, it would be significantly better as the expected number of active users is low, and this scales poorly with the number of active users (but excellent with the number of total users in the system). A realistic model would leave almost all of the 'delete' operations to the cleanup and have few active users, so the thrashing that occurs in this model due to the logout wouldn't exist, and the throughput should be probably an order of magnitude better than it is in this case. The most important thing when implementing a high-performance anything is that it fit the scenario well, and this doesn't fit my test well at all.

Indeed, if the logout is disabled, the throughput skyrockets to over 500K r/s at 10000 users due to a lack of thrashing (I'm somewhat limited by the implementation of the hash map in Javascript, and some other data structure would likely be significantly better even at my test). With 100K users, it gets 450K r/s, and it can't even function at 1 million users because the gc sweep is too expensive.

I usually do it like this if it helps anyone

PHP Code:

---


$online = time() - 180; // 3 mins since actively doing something on site
$usersonline = mysql_result($this->DB->query("SELECT COUNT(*) FROM `users_sys` WHERE `active` = '1' AND `lastactivetime`> $online"),0); 


---

you can see i just use a unix timestamp on the field `lastactivetime` but only check active players as my banned players get `active` 0 and `status` 3 so i only need check active cause new account wont account either just easier this way for me
then i just echo this on the page where online statistics

Optimizing access to cross-request variables can be challenging with a stateless language like PHP. It doesn't support atomicity or anything similarly without an external database of some sort. I'd say a typical SQL database for keeping track of the activity times, then storing the number of active users in memcached for a set period of time is probably the best approach.

Honestly, for a pserver, I would go with node.js as jMerliN suggested unless you have good prior experience in other languages. For an enterprise, such as an official MMORPG publisher, I'd say node.js is too new and it would be difficult to find developers experienced with it. Although, I could see more lightweight languages like Javascript replacing PHP/ASP.NET in some years.

You should really try out PDO or MySQLi, faster and much more safer. MySQL is going to be deprecated soon in PHP6.

I was trying to figure out a way to do this without a database. You can just count the active sessions. The only problem is that the folder where the sessions are stored is protected. This can be bypassed by using a custom folder to save the sessions in.

I came across this solution on Stackoverflow.

PHP Code:

---

<?php

// you must set your own accessible session path atop every page.
session_save_path("/home/some/other/location/"); # SECURITY VIOLATION!!!
session_start();

function session_count() {
  $filter = "sess_";
  $files = scandir(session_save_path());
  $count = 0;
  foreach ($files as $file) {
    if(strpos($file,$filter)===0) {
      $count += 1;
    }
  }
  return $count;
}

echo session_count();
?>

---

The database way is probably better but I figured I would toss this in here anyways.

Quote:

---

Originally Posted by leenster

I was trying to figure out a way to do this without a database. You can just count the active sessions. The only problem is that the folder where the sessions are stored is protected. This can be bypassed by using a custom folder to save the sessions in.

I came across this solution on Stackoverflow.

PHP Code:

---

<?php

// you must set your own accessible session path atop every page.
session_save_path("/home/some/other/location/"); # SECURITY VIOLATION!!!
session_start();

function session_count() {
  $filter = "sess_";
  $files = scandir(session_save_path());
  $count = 0;
  foreach ($files as $file) {
    if(strpos($file,$filter)===0) {
      $count += 1;
    }
  }
  return $count;
}

echo session_count();
?>

---

The database way is probably better but I figured I would toss this in here anyways.

---

I imagine this would scale horrendously.

Quote:

---

Originally Posted by mootie

Although, I could see more lightweight languages like Javascript replacing PHP/ASP.NET in some years.

---

could u explain me, how u can believe javascript is able to replace asp.net. i agree, php is easy to pwn due the lack of securiy etc, but asp.net provides a strong oop model with a huge delievered .net lib collection. also asp.net is very fast after compiling and it allows huge projects without much programming time to spend on. basics are might harder, but complex stuff getting easier, i.e. look on the entity framework. now compare this high language model with javascript. doest provide real oop. server sided like node.js it wont rule just cuz it will be slow.

Quote:

---

Originally Posted by VibeTribe

could u explain me, how u can believe javascript is able to replace asp.net. i agree, php is easy to pwn due the lack of securiy etc, but asp.net provides a strong oop model with a huge delievered .net lib collection. also asp.net is very fast after compiling and it allows huge projects without much programming time to spend on. basics are might harder, but complex stuff getting easier, i.e. look on the entity framework. now compare this high language model with javascript. doest provide real oop. server sided like node.js it wont rule just cuz it will be slow.

---

Node.js is simple, and performance is becoming less and less important especially for smaller websites. Any website getting less than 1000 requests per second could run easily on node.js(in almost all cases) from what I've seen. The other issue with ASP.NET is scalability, considering how easy it is to scale a node.js application in comparison. Also note that LinkedIn serves over a billion requests per month with node.js, which is a pretty strong number.

Quote:

---

Originally Posted by xLethal

Node.js is simple, and performance is becoming less and less important especially for smaller websites. Any website getting less than 1000 requests per second could run easily on node.js(in almost all cases) from what I've seen. The other issue with ASP.NET is scalability, considering how easy it is to scale a node.js application in comparison. Also note that LinkedIn serves over a billion requests per month with node.js, which is a pretty strong number.

---

i agree node.js is pretty simple, such as php, node.js is even safer than php which is a good point. for smaller websites node.js could really become a leading tool within next years. im not facing smaller websites at all, however. to say node.js could replace asp.net u drop into a clanger. asp.net has no issue with scalability nor performance, its all about how u go for it.

believe me im familar with the asp.net system and its optimal for web development. sure are some cons when im looking on the freedom to edit html and bring in variables or reading out them from controls. however, this is just a hidden lie, cuz asp.net provides own possibilities to do anything (its completly different and hard to compare with other languages, its a high system not a script system). many ppl call it the wrong way microsoft goes, cuz its a system which is build by itself and i felt like i was in a programming jail when i skipped from php to asp. now when i understand the system, life cycle, behaviors, web control inflection to html controls, c# at all, etc. i see the high pros of this system. to build something small on a correct way takes longer, but to expand the "small" (no matter how big it really is) never takes insane long if u do it right. without three tier logic u wont success, but with it u will overview your code so easily and maintain it faster than any language can provide you.