Creating a Quick and Dirty Hit Counter

by Michael Smith, TeraTech
http://www.teratech.com

Overview

Nowadays, having a hit counter on your website is essential -- to find out how many visitors come to your site, who they are, and if possible where they came from. Sometimes you want a hit counter on your page that you can control the format of (unlike the prebuilt free graphics hit counters that are out there). With ColdFusion, it is easy to create a simple hit counter, using a database to store the current count. Our first example only tracks how many times a page has been visited or refreshed. While this first counter won't give you detailed statistics, you can easily modify your table to include columns for tracking items such as the visitor's IP address, browser type and referring page. We do this at the end of this article.

Creating tables in ColdFusion

To get started, you will need a table in your database to store the number of hits. Just for fun, I will show you how to create a table using pure ColdFusion and SQL code. (Note: This table can be created even when you don't have direct access to your database. If you have direct access to your ColdFusion data source, then of course you can just create the table in your database by hand.)

The table in question, "hit_counter" (see Listing 1 below), stores the number of hits a web page has received in an integer field called hit_count, thus tracking the number of visits to your web page.

FTP the create table example below to your web server and run it by typing the name of the file into your browser. In this case, the file is called "createcounter.cfm". When the CFQUERY successfully creates your table, you will see the message "Table Created!" in your browser window.

Listing 1:
Syntax - createcounter.cfm

<html>

<head>

        <title>My Counter</title>

</head>

<body>

        <cfquery datasource="YOUR_DATASOURCE" name="create">

               create table hit_counter (hit_count int  null)

        </cfquery>

        Table Created!

</body>

</html>

Note that you can also delete tables using the DROP TABLE command in SQL.

Listing 1.5
Syntax - DropCounter.cfm

<html>

<head>

        <title>My Counter</title>

</head>

<body>

        <cfquery datasource="YOUR_DATASOURCE" name="create">

               drop table hit_counter

        </cfquery>

        Table Dropped!

</body>

</html>

Counting hits

Listing 2 below shows how to update and display the results of your counter in your specified web page. In line A, we are checking to see how many "hits" are in our database. This is followed by a few CFIF queries which check the number and update the hit count in your database each time your web page is visited (or refreshed).

In line B, we are checking to see if a number exists in your database (i.e. at least "1"). When we created the table (in Listing 1) above, we did not insert a number into the database. The code below is required to allow for the case of there being no records in the counter. If we didn't do this, the hit_count variable would be null rather than 0, which would cause an error when we tried to use it in lines C and D. Using the query "CreateHit" below, your hit counter will start at "1" if it is brand new.

Line C displays the actual number of hits on the web page. And Line D will increment the current number of hits in our database to +1 for each new visitor to the web page.

Listing 2
To display the counter: Paste the following code into your web page:

A.	<cfquery name="GetHits" datasource="YOUR_DATASOURCE"> SELECT hit_count FROM hit_counter </cfquery>
B.	<CFIF GetHits.recordcount is 0> <cfquery name="CreateHit" datasource="YOUR_DATASOURCE"> INSERT INTO hit_counter( hit_count) VALUES (1) </cfquery> Hits: 0
C.	<cfelse> Hits: <CFOUTPUT query="GetHits">#hit_count#</cfoutput> </cfif>
D.	<cfquery name="updateHits" datasource="YOUR_DATASOURCE"> UPDATE hit_counter SET hit_count = hit_count + 1 </cfquery>

Using this simple hit counter will give you a good starting point, but I encourage you to modify the example code and add more information about your visitors to your database. Doing so will allow you to reap the rewards of this simple tracking device.

Complex counting and user tracking

Here is the code for a more complex counter. In this case, we want to save both every page hit and track individual customers. This code is typically placed in your application.cfm file that is run at the front of every page or in a header.cfm include file.

A.
Identify user by cookie:

<CFIF isdefined("Cookie.user_id")>

        <CFSET user_id=trim(Cookie.user_id)>

<CFELSE>

        <CFSET user_id="">

</CFIF>

B.
Get page referrer information:

<CFSET #address# = "#cgi.remote_addr#">

<CFSET #host# = "#cgi.remote_host#">

<CFSET #referer# = "#cgi.http_referer#">

<CFSET #agent# = "#cgi.http_user_agent#">

<CFSET #page# = "#cgi.script_name#">

<CFIF #host# is "">

<CFSET #host# = "unknown">

</CFIF>

<CFIF #referer# is "">

        <CFSET #referer# = "unknown">

</CFIF>

To track repeat visitors, we use a cookie called user_id. The code in section A checks to see if the cookie exists and copies the ID into a page variable called user_id. Otherwise, we default user_id to "", so that we won't get undefined variable errors later in the code.

Next, in code B, we set page variables for the user's IP address, machine name, etc., using the standard ColdFusion CGI variables. If you want to see a complete list of these variables, turn on debug on your CF server and look at the bottom of the page.

CGI variable	Meaning	Example
Remote_addr	IP of user	123.23.45.78
Remote_host	Machine name of user	user147.mycomp.com
http_referer	Link	this page came fromhttp://www.teratech.com/press.cfm
Remote_addr	IP of user	123.23.45.78
http_user_agent	User's browser type	Mozilla/4.7 [en] (WinNT; U)
Script_name	File name of CFM file being run	/press/CFConfAndByteBack.CFM

In addition to the user id, we give many of our users a variable called "custno" that may be passed on URL from a CF generated email or via a session variable. In the latter case, we default the page variable "custno" to the session variable in part C.

C.
Track any customer by session:

<CFIF not isdefined("custno")>

        <CFIF isdefined("session.custno")>

               <cfset custno = session.custno>

        </cfif>

</cfif>

D.
If user has not been here before, create ID and cookie for them:

<CFIF #user_id# is "">

        <CFQUERY NAME="CreateUser" DATASOURCE="webserver">

               insert into ClientInfo (RemoteAddress, RemoteHost, Referer,UserAgent, page )

               values ('#address#', '#host#', '#referer#', '#agent#', '#page#')

        </CFQUERY>

        <CFQUERY NAME="MakeCookie" DATASOURCE="webserver">

               SELECT max(id) as high from clientinfo

        </CFQUERY>

        <CFOUTPUT QUERY="MakeCookie">

               <CFSET #cook# = "#high#">

               <CFSET #user_id# = "#high#">

        </CFOUTPUT>

        <CFCOOKIE NAME="user_id" VALUE="#cook#" EXPIRES="never">

<CFELSE>

Then, in D, we start creating the tracking records. For new users we create a ClientInfo record and a cookie based on the ID using the <CFCOOKIE> tag. For existing users we add a new record to the ClientTrack table for their userid (Section E). This way we can track every page a particular user visits.

E.
Add tracking record for existing user:

<CFQUERY NAME="TrackUser" DATASOURCE="webserver">

        insert into ClientTrack (Id, RemoteAddress, RemoteHost,         Referer,UserAgent, page, custno)

        values ('#user_id#', '#address#', '#host#',       '#referer#', '#agent#', '#page#',

<CFIF isdefined("custno")>

        '#custno#'

<cfelse>

''

</cfif>)

</CFQUERY>

</CFIF>

Finally, in sections F, G and H, we set the "custno" session variable and update the ClientInfo database with the "custno", if it was passed in.

Listing 3

F.
If custno variable passed in on URL (from email for example), save in user database:

<CFIF isdefined("custno")>

        <CFQUERY NAME="UpdateUser" DATASOURCE="webserver">

               update ClientInfo

<CFIF isdefined("custno")>

        set custno ='#custno#'

<cfelse>

        set custno = custno

</cfif>

        where ID = #val(user_id)#

</CFQUERY>

<CFIF isdefined("custno")>

        <cfset session.custno = custno>

</cfif>

<cfelse>

G.
Otherwise, try to set custno number from previous visit:

<CFQUERY NAME="GetUser" DATASOURCE="webserver">

        SELECT custno, id

               FROM ClientInfo

               where ID = #val(user_id)#

</CFQUERY>

H.
Set session variable for use by other pages:

<CFIF GetUser.custno is not "">

        <cfset session.custno = GetUser.custno>

</cfif>

</cfif>

Summary

We have covered how to code a simple hit counter and how to track more complete information about users. We mentioned dynamic table creation, the CGI variables, cookies and session variables. Good luck with tracking your users and have fun coding!

Other ColdFusion Resources for Beginners

Allaire maintains an extensive knowledge base and tech support forums on their website.
CPCUG and TeraTech ColdFusion Conference Page
CF Advisor
ColdCuts, a page of ColdFusion code cuttings. This page also has links to about a dozen ColdFusion white papers in the CF Info Center.

Michael Smith is president of TeraTech, a ten-year-old Rockville Maryland based consulting company that specializes in ColdFusion, Database and Visual Basic development. You can reach Michael at [email protected] or 301-424-3903.