Overview

Creating Tables in ColdFusion

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

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

Listing 1.5
Syntax - DropCounter.cfm

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:

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

Listing 3

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.

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

Listing 3 (continued)

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.

Listing 3 (continued)

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 (continued)