Users online script

[spyderco]

Hi everyone, have a quick question on a USERS ONLINE script I am writing.

The script just tells you the number of people who have accessed your web site in the past 5 minutes. It also gives you a number of total unique visitors in the past 24 hours (NOT days, but 24 hours ago).

The users online works perfectly but the total users online during the past 24 hours does not. It never clears itself meaning the number isn't accurate. Can anyone see what the bug is to get the total users online working?

Below is the complete source code for this portion of the script. The other script involves the MySQL table creation.

#!/usr/bin/perl

use warnings;
use strict;
use CGI::Carp qw(fatalsToBrowser);
use CGI qw/:standard/;
use CGI::Cookie;
use DBI;

####################
# CONFIG
####################

my $dbase = "spyders_usersonline";
my $mysql_user = "spyders_admin";
my $mysql_pass = "pass";
  # The above three variables are your database name, username and password.

my $minutes = "5";
####################
# Do NOT edit below this line
####################



######
# Connecting to our database
######
my $dbh = DBI->connect("DBI:mysql:$dbase", $mysql_user, $mysql_pass) or
print DBI=>"errstr";


my $userip = $ENV{REMOTE_ADDR};



##########
# Selecting our database columns
#########
my $data = qq(SELECT id, ip, lasttime FROM now_time WHERE ip="$userip");
my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;

print header;
 
  if ($sth->rows < 1)  # IP is new within the past X minutes
  {

     my $sth = $dbh->prepare( qq{
          INSERT INTO now_time (id, ip, lasttime) VALUES (?,?,?)
      });
      $sth->execute("id","$userip", time()) or die $dbh->errstr;

     my $sth = $dbh->prepare( qq{
          INSERT INTO day_time (id, ip, lasttime) VALUES (?,?,?)
      });
      $sth->execute("id","$userip", time()) or die $dbh->errstr;

  }
  else
  {
      my $time = time();
      my $data = qq(UPDATE now_time SET lasttime=$time WHERE ip="$userip");
      my $sth = $dbh->prepare($data);
      $sth->execute() or die $dbh->errstr;

      my $data = qq(UPDATE day_time SET lasttime=$time WHERE ip="$userip");
      my $sth = $dbh->prepare($data);
      $sth->execute() or die $dbh->errstr;
  }

################################
# Remove expired users from now_time and day_time tables
################################
my $timenow = time();
$minutes = $minutes * 60;
my $timemin = $timenow - $minutes;

my $data = qq(DELETE FROM now_time WHERE lasttime < "$timemin");
my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;

my $day = 60 * 60 * 24;
my $timeday = $timenow - $day;

my $data = qq(DELETE FROM day_time WHERE lasttime < "$timeday");
my $sth = $dbh->prepare($data);
$sth->execute() or die $dbh->errstr;


################################
# Printout number of current users
################################
my $sql = "SELECT ip FROM now_time";
my $rows = $dbh->do($sql);
print "<b>$rows</b> users online";

my $sql = "SELECT ip FROM day_time";
my $rows = $dbh->do($sql);
print "<br><b>$rows</b> users seen today<br>";
print "<font size=\"1.5\">^ beta testing ^</font>";

A sample from my day_time column

      id   ip            lasttime 
      2020 216.32.90.218 1126281068 
      1455 216.32.90.218 1126281068 
      2017 216.32.90.218 1126281068 
      1916 216.32.90.218 1126281068

Any assistance in getting this working properly would be very much appreciated.

 

[duncdude]

this might be of some interest to you - or maybe not!?

[b]#!/usr/bin/perl[/b]

$mins = 5;

for ($x=1; $x<=100; $x++) {

  $id = int(rand(10)+2000);
  $ip = "216.32.90.2" . int(rand(10));
  $lt = time() - int(rand(($mins*2) * 60));
  
  print "$id\t$ip\t$lt\t";
  
  if ((time() - $lt) <= ($mins * 60)) {
    print "S T O R E D\n";
    $seen{$ip}++;
  } else {
    print "I G N O R E D\n";
  }
}

print "\n";

while (($key, $value) = each %seen) {
  print "$key\t$value\n";
}

generates:-

...

2000	216.32.90.24	1126291277	S T O R E D
2008	216.32.90.23	1126291070	I G N O R E D
2002	216.32.90.25	1126291409	S T O R E D
2002	216.32.90.23	1126291065	I G N O R E D
2001	216.32.90.25	1126291284	S T O R E D
2000	216.32.90.25	1126291322	S T O R E D
2006	216.32.90.22	1126291229	I G N O R E D
2009	216.32.90.29	1126291478	S T O R E D
2009	216.32.90.20	1126291060	I G N O R E D
2000	216.32.90.22	1126291094	I G N O R E D
2004	216.32.90.25	1126291166	I G N O R E D
2006	216.32.90.25	1126291165	I G N O R E D
2006	216.32.90.27	1126291472	S T O R E D
2001	216.32.90.29	1126291523	S T O R E D
2000	216.32.90.28	1126291049	I G N O R E D
2005	216.32.90.24	1126291006	I G N O R E D
2006	216.32.90.20	1126291315	S T O R E D

216.32.90.20	5
216.32.90.29	6
216.32.90.24	2
216.32.90.28	5
216.32.90.25	10
216.32.90.21	4
216.32.90.22	5
216.32.90.27	6
216.32.90.26	2
216.32.90.23	4

Kind Regards
Duncan

 

[duncdude]

The script above generates random id's, random IP's, random time of visit (up to 10 mins ago - twice the duration of interest), and then analyses the result to see if it is within the last 5 minutes. If it is, the script stores the IP in a hash and increments this hash so as to show you how many times, within the last 5 minutes, each individual IP has visited your site

Most of the code is for the generation of the random data - since i didn't have any to work with. However it does demonstrate how quickly you can summarise what you are looking for

... I hope!?


Kind Regards
Duncan

 

[spyderco]

Hi.

Thanks for your help.

I looked through your code and can understand what you did, however the 5 minute portion of the script is already working. I can get all that information stored into my MySQL tables nicely (or at least I think it's nice, I'm not too well versed with MySQL yet).

I still can't figure out what's wrong with the 24 hour time. The math calculations look fine to me but yet it continues to track ALL visits from the start of the script instead of clearing the older ones.

Thank you.