WhizBase: Creating live visitors counter (who is online) in WhizBase

Ever wondered how to display how many visitors you have online. In this tutorial I will show you an easy but effective way to display the number of online visitors in WhizBase.
In this article I assume you have read my previous articles and know some WhizBase basics, as how to query, insert or update a DB.

We will need one table in the DB where we will keep the data of the recent visitors. The principle goes like this:

1- the visitor enters the site
2- we check the IP address of visitor and check if he is in the DB
3- if the IP exists, we update its record with current time
4- if not, we insert a new record
5- To include to visitor in our counting he must check-in in last 5 seconds

The database table used by the tutorial

I will not go through the SQL or the Access file creation, I will just describe what we need in the DB. We will need one table named "visitor". It will have the following fields:

Id as integer(9) autoincrement primary key
IP as char(100) unique and not null
Lastvisit as DateTime

I will create it in access DB and name the file visitor.mdb, and the table as visitor.
The id is auto increment field, so we will not need it in our code, it is just for table indexing and maintenance. The IP is the IP address of the visitor, and it must be unique and not null. Lastvisit will be a date and time of the last visit.

If exists include update, else include add

As we stated in the introduction, we will need to check if the visitor is online or not, and then include the file which makes the needed changes on the DB.

[FormFields]
WB_BaseName=visitor.mdb
WB_RcdSet=visitor
WB_Query=IP=$WBE{REMOTE_ADDR}
WB_Command=Q

$WBIF[$WBP[RC]>0|
$WBGETURL[update.wbsp?wbf_id=$WBF[id]]
|
$WBGETURL[add.wbsp?wbf_ip=$WBE[REMOTE_ADDR]]
]
<html>
<head>Number of visitors online</head>
<body>
$wbsr[show.sr]
</body>
</html>

In this code we have started WhizBase file and queried the DB «visitor.mdb», the table «visitor» where the IP address is gained by «$wbe[remote_addr]». «$wbe» is the function for reading operating system environment variable, in this case variable remote_addr that exist in every web server. I have named this file as «onvisitors.wbsp».
Now using «$wbif» we check if count of records is greater than zero by «$wbp[rc]>0», and if true we call the file «update.wbsp» and pass the parameter wbf_id with the id of current record. If false («$wbp[rc]=0») we call the file «add.wbsp» and pass the WBF_IP value.
Finally we call a sub-report file which will give us the result of how many users are online using «$wbsr» function.

I have visited your site before, update me

This is a description of the file that will be used to update the DB and record the time of last visit.

[FormFields]
WB_BaseName=visitor.mdb
WB_RcdSet=visitor
WB_Command=U
WB_UID=ID
WB_FORCED=wbf_lastvisit=$WBFN{FDT(yyyy-mm-dd hh:mm:ss)}

This is a simple update script, we connect to «visitor.mdb», open table «visitor» and make an update to the unique field id and force one more parameter named «wbf_lastvisit» with the current date and time in the suitable format.
In previous paragraph we've explained why we call this file and give it wbf_id as a parameter, and you know WhizBase needs a unique field to update the record by. So we defined field "id" as the unique one. Then we needed the current time, so we have forced one more parameter (same as if we have sent it by form, but for security reasons we've used this method). The parameter is wbf_lastvisit which has the value of current date and time formatted in full year-month-day hour:minutes:seconds format.
Since we need no feedback from this file, it will do the update and return an empty string (a blank page).

This is my first visit to your site, insert me

We use this simple code to call the file «insert.wbsp» and pass the parameter with the IP of the visitor.

[FormFields]
WB_BaseName=visitor.mdb
WB_RcdSet=visitor
WB_Command=A
WB_FORCED=wbf_lastvisit=$WBFN{FDT(yyyy-mm-dd hh:mm:ss)}

There is no need to describe anything in this code, it is the same as previous one but for addition (wb_command=A).

So how many visitors are on my site now?

Finally, we will look into the file «show.sr» which we include as a sub-report in our WBSP file.

[FormFields]
WB_BaseName=visitor.mdb
WB_RcdSet=visitor
WB_Query=lastvisit>$WBDCALC{$WBFN{FDT(yyyy-mm-dd hh:nn:ss)}|10|s|-}
WB_Command=Q

<html>
<head>
<title>Number of users</title>
</head>
<body>
There is $WBP[RC] users now online!
</body>
</html>

In our sub-report we make a query to the DB «visitor.mdb» on table «visitor» and get all records where the last visit is greater than current time minus ten seconds. This will return all IPs that where recorded for the last ten seconds.
$wbdcalc makes arithmetic operations on dates, so we pass the current date and time and number of intervals to be added to or subtracted from it. We use «s» for interval (as seconds) and defined 10 of them, and finally we defined subtraction using the minus operator.
We will show just the number of visitors that are currently online, but not their IPs, so we need only «$wbp[rc]» which we have described before in this article.

What's next

That is how we record/count/show the number of visitors in the last 10 seconds using the WhizBase. Now, you can call «onvisitors.wbsp» with AJAX every 5-8 seconds from any page on your site and you'll have 100% real data and will not depend on the refresh of the page.
For more information email me at: NurAzije [at] Gmail [dot] com Or visit WhizBase official site at www.whizbase.com

Mar 21, 2011

Creating live visitors counter (who is online) in WhizBase