Howto Create a Hit Counter Using a Database

If you have a live Web site on the World Wide Web, you may be interested in how many people are visiting your site. You can of course analyze the log files of your Web server but that information is usually difficult to read. The log files contain information for each and every request a visitor has made to your site, including resources like images, Flash movies and so on. This makes it near impossible to extract information about individual users. It would be a lot easier if you could count the number of individual users that have visited you since you started your site. It would also be useful if you could see the number of users that are currently browsing your site.

This article will show you how to accomplish these two tasks by storing the hit counters in Application variables and in a database using code in the global.asa file. The counters in the Application variables are used to display them on a page in your Web site; either as a counter so your visitors can see it as well, or somewhere on a page in your Admin section, so only you have access to them. By writing the counter to a database you can maintain its value even when you restart the Web server, while you still have a fast and scalable solution.

This article extends the ideas from two previous articles where the values of the counters were just stored in Application variables and in a text file.

There is also an ASP.NET version of this article available.

Prerequisites

The code in this article uses Sessions in ASP, so you'll need to have them enabled on your server. See the References section at the end of this article for more information. You'll also need to have access to a file called global.asa in the root of your site. If you run your own Web server, this is not a problem; you can simply create the file yourself. If you are using an ISP, you'll need to check with them if they support the use of the global.asa file as, unfortunately, not all ISPs allow this.

You'll also need to be able to write to and read from a database from within the global.asa file. This means that the account your Web site is running under (usually, IUSR_MachineName where MachineName is the name of your computer) needs sufficient permissions to write to the database file and the folder it resides in.

Counting Users

Just as in the articles where the counters were stored in just the Application object and in a text file, you can make use of the Session_OnStart event, defined in the global.asa file to keep track of your users. This event is fired whenever a user requests the first page in your Web site. This way, you have the ability to count each unique visitor only once during their visit. As long as the Session remains active on the server, the user won't be counted again. After the Session has timed out (it will automatically time out after a certain interval when no new pages have been requested) or has been explicitly ended, a request to a page will create a new Session, and the user will be counted again.

To keep track of the total number of users that have visited your site since you started the Web server, you can increase a counter for each request a user makes. Let's call this counter TotalNumberOfUsers. You can store that counter in a variable in Application state, so you can retrieve and display it on other pages in your site. You can also create a second counter, called CurrentNumberOfUsers for example, that counts the number of active Sessions on your server. Just as with TotalNumberOfUsers, you increase the value of this counter whenever a new Session is started. However, you should decrease its value again when the Session ends. so you can keep track of the number of users that are currently visiting your site.

Besides storing these values in the Application object, I'll show you how to save them in a database, so the value for the counter is preserved, even when the Web server is restarted. This example uses a Microsoft Access database that is called Website.mdb. The database has just one single table called Counters. This table has two columns called CounterType and NumberOfHits. The CounterType column defines the type of counter you want to store in the database. The table looks as follows:

Tabel Counters that can store various types of Hit Counters
Figure 1: The Counters table

In this example, the type of counter will be UserCounter, to indicate you are counting users. You can expand on this example and count individual pages, for example. In that case, you could store the page name in the CounterType column to indicate which page you are tracking.
The NumberOfHits column will hold the number of hits for the counter. In this example, it stores the number of visitors to your site.

The code assumes that you have saved this database (which is available in the code download for this article) in a folder called C:\Databases. If you decide to store your database at a different location, make sure you adjust the path in the code that references the database. You'll also need to make sure that the IUSR_MachineName account has sufficient permissions to both the database and the folder Databases.

Once you have set up the database, create a file called global.asa (note that the extension is different from ordinary ASP pages) and save it in the root of your Web site. Open the file in Dreamweaver, Notepad or in your favorite HTML / ASP editor, and make sure it's completely empty (it shouldn't contain any ASP or HTML tags). Add the following ASP code to the file:

<script language="vbscript" runat="server">
Sub Session_OnStart

End Sub

Sub Session_OnEnd

End Sub
</script>
This code defines the skeleton for the two methods that fire when a new Session is started, and when a Session ends. You can add the code for the counters to these two Subs. To keep track of the total number of users, and the current number of users, you'll need to add the following shaded lines of code that create two Application variables. Once the variables have been increased and stored in the Application object, the value for the total number of users is then saved in the database as well.

<script language="vbscript" runat="server">
Sub Session_OnStart
  Application.Lock
  Application("TotalNumberOfUsers") = _
      Application("TotalNumberOfUsers") + 1
  Application("CurrentNumberOfUsers") = _
      Application("CurrentNumberOfUsers") + 1
  Application.Unlock

  ' Values have been increased. Now write them to the database as well
  Dim ConnectionString
  Dim SQLStatement
  Dim aConnection
  ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=C:\Databases\Website.mdb;User Id=admin;Password=;"
  SQLStatement = "UPDATE Counters SET NumberOfHits = " & _
        Application("TotalNumberOfUsers") & _
        " WHERE CounterType = 'UserCounter'"
  Set aConnection = Server.CreateObject("ADODB.Connection")
  aConnection.Open(ConnectionString)
  aConnection.Execute(SQLStatement)
  aConnection.Close()
  Set aConnection = Nothing
End Sub
When a user starts a new Session by requesting a page in your site, the code in the Session_OnStart event will fire. This code will lock the Application object temporarily, to prevent two users from writing to it at the same time. Then the Application variables TotalNumberOfUsers and CurrentNumberOfUsers are increased by one. Once they are increased, the Application is unlocked again, so it's available to other users.

The second block of code in the Session_OnStart event writes this value to the database. First three variables are declared: one to hold the Connection string to the database, one for the SQL statement that needs to be passed to the database and one to hold the ADODB.Connection object that is used to connect to the database. The SQL statement is a simple UPDATE statement that will update the value for the NumberOfHits column. You need to make sure that just the UserCounter record is updated, and the WHERE clause in the SQL statement takes care of that. This WHERE clause will make sure that only the right record is modified.

Once all the variables are set up correctly, the Open method will open the connection to the database. Then the Execute method of the connection is used to send the SQL statement to the database and finally the connection is closed again using the Close method. At the end, the connection object is cleaned up by setting it to Nothing.

Notice that just the value of the TotalNumberOfUsers counter is saved. There is no need to store the current number of users; after all, when the Web server is restarted, there are no current users yet.

To decrease the counter for the CurrentNumberOfUsers, you'll need to add some code to the Session_OnEnd event, that will fire when a Session times out, or when it is explicitly ended. You should just decrease the value for CurrentNumberOfUsers, and leave TotalNumberOfUsers untouched. Add the following shading lines of code to the Session_OnEnd event in your global.asa file:

    Application.Unlock
End Sub

Sub Session_OnEnd
    Application.Lock
    Application("CurrentNumberOfUsers") = _
         Application("CurrentNumberOfUsers") - 1
    Application.Unlock
End Sub
</script>
Whenever a Session is ended, the value of CurrentNumberOfUsers is decreased by one. This way, its value will always reflect the number of users that are browsing your site right now.

Reading the Counter When the Web Server Starts

As long as the Web server keeps running, this code will run fine. For each new Session that is created, the counters are increased by one and the value of TotalNumberOfUsers is written to the file. If, however, the Web server stops unexpectedly, or your restart or reboot the Web server yourself, the values for TotalNumberOfUsers and CurrentNumberOfUsers are lost. To read in the value from the database, you'll need to add some code to the Application_OnStart event that is also defined in the global.asa file. Add the following code to the end of the global.asa file, right before the closing </script> tag:

End Sub

Sub Application_OnStart
  ' Web server is started, so read the value for the counter
  Dim ConnectionString
  Dim SQLStatement
  Dim aConnection
  Dim aRecordset

  ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
      "Data Source=C:\Databases\Website.mdb;User Id=admin;Password=;"
  SQLStatement = "SELECT NumberOfHits FROM Counters " & _
      " WHERE CounterType = 'UserCounter'"
  Set aConnection = Server.CreateObject("ADODB.Connection")
  aConnection.Open(ConnectionString)
  Set aRecordset = aConnection.Execute(SQLStatement)
  If Not aRecordset.EOF Then
    Application("TotalNumberOfUsers") = _
        CInt(aRecordset.Fields("NumberOfHits").Value)
  Else
    Application("TotalNumberOfUsers") = 0
  End If
  aRecordset.Close()
  aConnection.Close()
  Set aRecordset = Nothing
  Set aConnection = Nothing
End Sub
</script>
This code is more or less the same as the code that was used to write the value to the database. What's different here is that a SELECT statement is used to retrieve the value for the hit counter from the database. The same WHERE clause is used to make sure you get the value for the right counter.

What's also different is the use of a Recordset to retrieve the record you get back from the database. If the Recordset is not empty (it's not EOF or End Of File), it means that the requested counter has been found. In that case, the value from the NumberOfHits column is stored in the Application variable called TotalNumberOfUsers, after it has been converted to a number using the CInt function. If the counter is not found, the Application variable is reset to 0. (Note that it is unlikely that the record is not found, as it is defined in the database. If you ever delete this record, the UPDATE statement that sets the counter in the Session_OnStart will not run correctly, because it expects the record to be present).

At the end, the Recordset and the Connection are closed and the objects are cleaned up again, just as in the code for the Session_OnStart event.

Testing it Out

To test out your hit counter, create a new ASP file and call it Counter.asp. You can save it anywhere in your site. Add the following ASP code to that page:

Total number of visitors: <%=Application("CurrentNumberOfUsers")%><br>
Current visitors: <%=Application("TotalNumberOfUsers")%>
These two lines of code write out the total number of visitors and the current number of visitors. Open the page in your browser and you'll see there is one current user. The total number of users is 1. Open another browser (don't use Ctrl+N, but start a fresh instance or use an entirely different brand of browser) and open the counter page. You'll see there are two current users and two users in total.
Next, restart your Web server. If you are using IIS, you can choose Restart IIS... from the All Tasks context menu for your Web server in the IIS Management Console.
Open Counter.asp again. You'll see that currently you're the only user browsing the site, but the total number of users has maintained its value.

Summary

This article demonstrated how to create a hit counter that keeps track of the current and total number of users to your site. It stores these variables in Application state so they are available in each page in your Web site. It also stores the value for the total number of users in a database so its value won't be lost when the Web server restarts, either unexpectedly, or on purpose.

By using a database, you have created a solution that can easily be scaled to lots of users. If you have a real busy Web site, you can change the code from this article so it uses a real Database Management System, like Microsoft SQL Server or Oracle. These kind of work beasts can easily serve thousands and thousands of users a day allowing for lots of concurrent users.

Related Articles

References

Download Files


Where to Next?

Wonder where to go next? You can post a comment on this article.

Doc ID 165
Full URL https://imar.spaanjaars.com/165/howto-create-a-hit-counter-using-a-database
Short cut https://imar.spaanjaars.com/165/
Written by Imar Spaanjaars
Date Posted 10/04/2003 17:27
Date Last Updated 02/19/2005 12:05
Date Last Reviewed 12/08/2006 14:40

Comments

Talk Back! Comment on Imar.Spaanjaars.Com

I am interested in what you have to say about this article. Feel free to post any comments, remarks or questions you may have about this article. The Talk Back feature is not meant for technical questions that are not directly related to this article. So, a post like "Hey, can you tell me how I can upload files to a MySQL database in PHP?" is likely to be removed. Also spam and unrealistic job offers will be deleted immediately.

When you post a comment, you have to provide your name and the comment. Your e-mail address is optional and you only need to provide it if you want me to contact you. It will not be displayed along with your comment. I got sick and tired of the comment spam I was receiving, so I have protected this page with a simple calculation exercise. This means that if you want to leave a comment, you'll need to complete the calculation before you hit the Post Comment button.

If you want to object to a comment made by another visitor, be sure to contact me and I'll look into it ASAP. Don't forget to mention the page link, or the Doc ID of the document.

(Plain text only; no HTML or code that looks like HTML or XML. In other words, don't use < and >. Also no links allowed.