link counting script

[derwent]

WE are linking to various external websites and we want to keep track of how many people click each link.

I guess I need a database (we have mssql), but I am having trouble getting my head around the logic required.

1. Is it best to log each time a link is clicked as a seperate row then use a count on the DB for certain entries such as url =

http://www.tek-tips.com

2. or is it best to create one row per link and increment a number each time it's clicked?

If I go for 1 I can put a new link on the website pointing to my file (links.asp?url=

http://www.tek-tips.com)

and not have to update the DB, but if I go for option 2 I would have to add the row to the DB before the link is made live.


What would you do folks?

 

[Tarwn]

I would probably go for number 1 or a combination of 1 and 2.

Number 1 allows you to create some statistics or trends because you have all of the individual click data. So you could very easily pull that data out and generate a grph of all the clicks for a certain site, over the course of a period of time.
Downside is it takes up mor database space.

Another option is to only store number of clicks per day. Since your using MS SQL I would suggest a stored procedure, as the database will handle SELECT vs UPDATE faster than ASP. Basically you would have a table that looks like:
HitCount
hit_id - auto-incrementing id
link_id - int - Foreign Key to Site/Link Table
hit_count - int
hit_date - DateTime

Link
link_id - auto-incrementing int
link_name - varchar - shortname
link_url - varchar - full URL

and a Stored Proc like so:

Create Procedure dbo.AddHit
   @LinkUrl varchar(80)
AS

DECLARE @LinkId int
SELECT @LinkId = link_id FROM Link WHERE link_url = @LinkUrl

DECLARE @HitId
SELECT @HitId = hit_id FROM HitCount WHERE link_id = @LinkId AND DateDiff(day,hit_date,getdate()) = 0

IF @HitId IS NULL
   INSERT INTO HitCount(link_id, hit_count, hit_date) VALUES(@LinkId, 1, CAST(DATEDIFF(day, 0, getdate()) AS DATETIME))
ELSE
   UPDATE HitCount SET hit_count = hit_count + 1 WHERE hit_id = @HitId
END

While there are probably some syntactical erros in there, the basic concept should be sound.

Form your code, now, all you have to do is executeths stored procedure with the URL, it should handle the rest of the work. If you later decide you want to keep all of the data then you could modify the database structure and change the stored proc and that would be all you would need, you would never have to touch your web page code.

 

[emozley]

I would have each link click as it's own record as you will also be able to record other information in each row such as date/time, the referring page etc...etc..

You can calculate your totals on the fly using SQL.

 

[ChrisHirst]

Make sure that you use a 301 redirect when "jumping" from your links.asp to the external URI.
DO NOT simply use response.redirect(url) as this will redirect with a 302 response.



Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.

 

[derwent]

I had just built it then was testing it worked when you posted this message. I had just used a response.redirect.

A quick google of 301 redirect says I should be doing this

Response.Status="301 Moved Permanently" Response.AddHeader "Location", "

http://www.new-url.com"

is that correct?

 

[ChrisHirst]

perfectly


Chris.

Indifference will be the downfall of mankind, but who cares?
Woo Hoo! the cobblers kids get new shoes.
People Counting Systems

So long, and thanks for all the fish.