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 read existing comments below or you can post a comment yourself on this article .


Consider making a donation
Please consider making a donation using PayPal. Your donation helps me to pay the bills so I can keep running Imar.Spaanjaars.Com, providing fresh content as often as possible.



Feedback by Other Visitors of Imar.Spaanjaars.Com

On Tuesday, September 21, 2004 12:19:21 PM Mardon said:
"Thanks!" for the Hit Counter mentioned in QuickDoc=165. I've implemented it on my ISP-hosted website but had to change the DB conection to use "Server.MapPath".  I replaced the 2 lines that make up the connection string in your code with 3 lines as follows:
ConnectionString = "DBQ=" & Server.MapPath("fpdb/Website.mdb") & ";"
ConnectionString = ConnectionString & "Driver={Microsoft Access Driver (*.mdb)};"
ConnectionString = ConnectionString & "DriverId=25;FIL=MS Access;"
I'm a real novice at this ASP stuff, so I figure that I was lucky to get it to work.  I was suspicious that your approach of using the Admin account on an ISP's server, would not work, so when the counter failed on my first attempt, that was the clue I needed to revise the code.  Perhaps you might want to mention this in your article, since most people who are apt to want to use your counter are unlikely to be running their own server.  In any event, I would not have ever got this working without your extrememly clear and helpful tutorial.  Thanks again!  Mardon
On Tuesday, September 21, 2004 1:18:24 PM Imar Spaanjaars said:
Thanks for your feedback.
The Admin account is not really used in this case. I'd say the following Connection String would work equally well:

ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  "Data Source=" & Server.MapPath("/Database/MyDatabase.mdb") & _
  ";User Id=admin;Password=;"

This "translates" the path at run-time so it matches the physical location of the database with the virtual location of /Database/MyDatabase.mdb.
On Monday, February 14, 2005 12:24:38 PM Simon Morgan said:
Hi Imar - great article, very well written and almost exactly what I'm looking for, however, is it possible to use a similar chunk of code to count the total visitors / current visitors to a particular folder (not sub-web) of an Application?  Many thanks, Simon
On Monday, February 14, 2005 7:00:43 PM Imar Spaanjaars said:
Hi Simon,

Thanks for your feedback.
What you want *is* possible albeit with some limitations. You cannot directly use the code I presented in this article. Part of your post lies at the root of the problem:

"a particular folder (not sub-web)"

A particular folder that is not a sub-web or application does not have its own session and application state but instead uses the same state as its parent. This means it doesn't have its own Session_OnStart.

However, you can use the requested URL in Session_OnStart using this code:

Dim requestedUrl
requestedUrl = Request.ServerVariables("SCRIPT_NAME")

Then you can parse the requestedUrl for the folders you want to track, and update the counter accordingly. This will only work if the first hit is on one of the folders you want to track.

Alternatively, you can ditch the global.asa and include a HitCounter.asp page in all the pages you want to track. A function in this file can do pretty much the same as what I demonstrated for the Session_OnStart except that you call it only for hits on the tracked folders.

Since Session_OnEnd is not tied to a particular folder, you cannot use this for a "currently online" counter because there is no way to decrement the counter for the specific folders; all you can use this for is a general hit counter.

A final alternative is converting the folders to sub webs, so they can have their own global.asa.

HtH,

Imar
On Friday, April 15, 2005 8:06:18 PM Josh said:
Hi Imar, I think I struck gold here in what I'm looking for.  I was looking on attaching a hit counter to our website, but then I saw a link indicating that you could do this in ASP with a database.  Wohoo!!  Now I'm still a newbie in this ASP/Dynamic area.  I'm taking classes in it in a couple weeks.

Reading over what you have here, interests me.  I'm thinking what I might do is on every page place a counter.  This way indicating where our heavy hit pages are.
Being that this is in a database, ever thought of creating some sort of reporting tool for it?  I mean, I guess it would be fairly easy to create a report that shows each page and it's total hits?
I guess as an afterthought, is it still easy to place all the different pages hit totals into a single database?

Sorry for such a newbie question,
Josh
On Sunday, April 17, 2005 10:19:10 AM Imar Spaanjaars said:
Hi Josh,

Yeah, that would be fairly simple to implement. Here's what I would do:

1. Create a HitCounter table that has two columns:
  a) The PageName
  b) The Counter

2. Create a generic function in a helper page that accepts a page name as a string. Inside that function, update the HitCounter table for the requested page. E.g. something like this:

  "UPDATE HitCounter SET Counter = Counter + 1 WHERE PageName = " + pageName

3. Include this helper page in all the pages you want to track

4. Call the helper function by passing in the page name on each page you want to track:

  UpdateCounter(Request.ServerVariables("SCRIPT_NAME"))


Currently, there is no reporting for this counter. However, creating a simple report isn't that difficult. Just execute a Sql statement like this:

SELECT Counter, PageName FROM HitCounter ORDER BY PageName

and then use a loop to write out the page names and counter values...

Imar
On Tuesday, February 07, 2006 4:20:35 PM Malcolm said:
Imar,

Great article sir, it was very insightful and helpful to me. I do have a question and I wonder if I missed it, but how could I implement an automated reset to the counter. Like at the end of the month I would like the counter on a certain web page to be reported and then reset to start the count again for the next month. I did not develop my counter as database, it just occurs within the page. Is that a requirement. please advise.

Thank you again,
malcolm
On Tuesday, July 18, 2006 2:40:10 PM Laetitia said:
Hi Imar,

Thank you for your article, which I was able to implement with ease.

I am using the function to update hits to a database. I want to expand the function to calculate the duration of page views.

Please help
Laetitia
On Tuesday, July 18, 2006 3:05:34 PM Imar Spaanjaars said:
Hi Laetitia,

This is not an easy undertaking and may lead to a lot of data in your database.

Instead of simply counting pages, you now need to get track of which user generated a page view and at which time. You can save each page view in the database, together with a unique identifier (e.g. a user's name, a session ID, the value from a cookie and so on) and then later write code to count the difference between each page view.

Not a very specific answer, but it's hard to give one to such a broad question... ;-)

Cheers,

Imar
On Wednesday, July 23, 2008 8:59:25 AM dhanya said:
i need the code to find out the page which is repeately called by one person  in my web site
On Wednesday, July 23, 2008 10:27:29 AM Imar Spaanjaars said:
And?

That would be pretty easy to do with similar code; just keep track of the IP address as well.

Cheers,

Imar
On Thursday, July 24, 2008 4:37:17 AM dhanya said:
hai imar,


thank you for your respnce...i need one more help..."SELECT NumberOfHits FROM Counters " & _
      " WHERE CounterType = 'UserCounter'"  which value given for this UserCounter..Is this any pagename
On Thursday, July 24, 2008 6:01:42 AM Imar Spaanjaars said:
Hi dhanya,

I am not sure I understand what you are asking. CounterType is just a counter type, like pages, users, whatever. You can use it for pages but it would be cleaner to introduce a PageName column.

Imar
On Wednesday, August 13, 2008 5:25:55 AM snoopy said:
hi Imar,
i very new on asp.net. i already create my database name visited, and i wan to count the total visitor visit my index page and save at visited. Another visitormy database to save customer IP adress and country.....
but i nowan my index page show the total visitor only wan save at database..so i should start at where??i really blank...i use visual studio.net2003 - asp.net. write in vb language. my index page using htmlkit.
On Thursday, August 21, 2008 8:25:03 AM Imar Spaanjaars said:
Hi snoopy,

I think what you're asking is largely covered in this article and the ones that I am linking to. If that's not enough, may I recommend you get yourself some books like my Beginning ASP.NET 3.5 in C# and VB.NET?

Cheers,

Imar
On Monday, August 25, 2008 8:33:56 PM raj said:
This is the error i am getting when i compiled the application. I am trying to compile the application in visual studio 2008.

The component 'ADODB.Connection' cannot be created.  Apartment threaded components can only be created on pages with an <%@ Page aspcompat=true %> page directive.

The error is occuring in the following line in the asax page.
aConnection = Server.CreateObject("ADODB.Connection")

Can you help me what i need to do to rectify the error.
On Monday, August 25, 2008 9:05:41 PM Imar Spaanjaars said:
Hi raj,

You used the Classic ASP version of the article on .NET 2008. Instead, you should take a look here:

http://imar.spaanjaars.com/QuickDocID.aspx?QUICKDOC=238

Please realize that that article was written against .NET 1.x so it may not work 1 on 1 in VS 2008 directly....

Cheers,

Imar
On Saturday, January 29, 2011 2:23:37 PM chloe said:
hi, i would like to ask how to save the hit counts to sql database and not to the access database.
On Saturday, January 29, 2011 2:27:31 PM Imar Spaanjaars said:
Hi chloe,

The concepts are pretty much the same as presented here, except that you target SQL Server in the connection string. For examples of connection string, take a look at: http://www.connectionstrings.com.

Cheers,

Imar

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 QuickDocId of the document.

For more information about the Talk Back feature, check out this news item.