Have Geek Brain

Find out how many users are online in PHP?

Each visit to my website updates the individual user counter and updates the column for time()

based on their IP address and ID stored in the cookie. So when you post the output, what's a more efficient way of my following code with fewer database queries, as it is essentially a copy itself:

<?
$last1Min = time()-60;
$last5Mins = time()-300;
$last1Hr = time()-6000;
$last1Dy = time()-144000;
$last1Wk = time()-1008000;
$last1Mnth = time()-30240000;

//last1Min
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last1Min";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last minute: " . $rows['COUNT(*)'] . "<br />\n";
}

//last5Mins
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last5Mins";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last 5 minutes: " . $rows['COUNT(*)'] . "<br />\n";
}

//last1Hr
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last1Hr";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last hour: " . $rows['COUNT(*)'] . "<br />\n";
}

//last1Dy
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last1Dy";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last day: " . $rows['COUNT(*)'] . "<br />\n";
}

//last1Wk
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last1Wk";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last week: " . $rows['COUNT(*)'] . "<br />\n";
}

//last1Mnth
$sql = "SELECT COUNT(*) FROM usersonline WHERE lastOnline > $last1Mnth";
while($rows = mysql_fetch_array(mysql_query($sql))) {
    echo "Users online in the last month: " . $rows['COUNT(*)'] . "<br /><br />\n";
}

If there is a more efficient way of presenting this data, I want to expand it to show not only the number of users for each of these metrics that are online across my site, but also the recording and output of data for every page on my site.

+1


a source to share


4 answers 


SELECT 
  SUM(lastOnline <= 60) AS one_minute,
  SUM(lastOnline <= 300) AS five_minutes,
  ...
  SUM(lastOnline <= 30240000) AS one_month
FROM usersonline

Using this method, you can get everything you need in one query with a single table scan; it doesn't get much more efficient than that. As others have pointed out, you should cache the result as it is relatively expensive (even in this optimized form). There is no point in calculating this on every page load, especially if you see several hits per second (which is very likely if, say, you land on the first page of digg)



lastOnline <= 60 evaluates to 1 for rows where the condition is true and 0 for rows where the condition is false; SUM () sums these 1s and zeros, giving you the number of rows for which the condition is true.

Learned this technique from user comment in mysql docs a few years ago; there are similar examples elsewhere

+4


a source


Set up a cron job that only calculates the correct values ​​once a minute / 5 minutes / etc. Instead, copy the result and show it. There is really no need to calculate these kinds of statistics X times per second, when they only change once a minute or once every half hour.



+3


a source


Instead of calling the database for each time, you could simply call for everyone for the last month and order them by date. Then in php, you can compare date and time to see how long ago the user was logged in.

0


a source


If you save your sessions to files, you can count the number of files you have accessed over a period of time. Then there will be no access to the database at all.

0


a source






More articles:


All Articles