Hi Guys,
I'm using CFML and SQL Server 2008.
I have an app that offers url shortening. I'm trying to build some stats about the hits and the referers they come from. While compiling a list is not a big deal, I'm struggling to figure out the best way to sort some of the data. I want to sort the referers by host. This means I'm going to have to take a look at each referer and parse out the host.
ex.
http://forums.anandtech.com/showthread.php?t=2096389
would be parsed to
forums.anandtech.com
A good example of this is found on bit.ly and how they display referrers.
http://bit.ly/cGXNq3+ and click "referrers".. you can see that the primary list is sorting by host, but you can click each host and see each unique referrer from each host.
I'm thinking that I'd start by querying the database for all hits to a particular redirect. That would give me a result set like the following (which is a crude markup).
ID Referrer datetime
1 http://forums.anandtech.com/showthread.php?t=2096389 june 19, 2010
2 http://forums.anandtech.com/showthread.php?t=45578334 june 22, 2010
3 http://www.mikeportnoy.com/forum/m2566811.aspx June 23, 2010
4 http://www.mikeportnoy.com/forum/m2566811.aspx June 24, 2010
5 http://forums.anandtech.com/showthread.php?t=2096389 june 25, 2010
6 http://forums.anandtech.com/showthread.php?t=2096389 june 26, 2010
7 http://mikeportnoy.com June 27, 2010
8 http://forums.devshed.com/index.php June 28, 2010
9 http://www.mikeportnoy.com/forum/m2566811.aspx June 29, 2010
I guess then, I'd need to loop through that record set, and run each referrer through a function that would parse out the host. From there, rebuild the query with the new host values and sort that set. Then I could just count how many records match where hosts match.
Or could I just loop through the initial query, and build a list of hostnames. Then use that list to loop through looking for "where XXX like YYY".. that way if I look for referrers that are like
forums.anandtech.com
it would return stuff like the following as a match..
http://forums.anandtech.com/showthread.php?t=2096389
Any advice?
I'm using CFML and SQL Server 2008.
I have an app that offers url shortening. I'm trying to build some stats about the hits and the referers they come from. While compiling a list is not a big deal, I'm struggling to figure out the best way to sort some of the data. I want to sort the referers by host. This means I'm going to have to take a look at each referer and parse out the host.
ex.
http://forums.anandtech.com/showthread.php?t=2096389
would be parsed to
forums.anandtech.com
A good example of this is found on bit.ly and how they display referrers.
http://bit.ly/cGXNq3+ and click "referrers".. you can see that the primary list is sorting by host, but you can click each host and see each unique referrer from each host.
I'm thinking that I'd start by querying the database for all hits to a particular redirect. That would give me a result set like the following (which is a crude markup).
ID Referrer datetime
1 http://forums.anandtech.com/showthread.php?t=2096389 june 19, 2010
2 http://forums.anandtech.com/showthread.php?t=45578334 june 22, 2010
3 http://www.mikeportnoy.com/forum/m2566811.aspx June 23, 2010
4 http://www.mikeportnoy.com/forum/m2566811.aspx June 24, 2010
5 http://forums.anandtech.com/showthread.php?t=2096389 june 25, 2010
6 http://forums.anandtech.com/showthread.php?t=2096389 june 26, 2010
7 http://mikeportnoy.com June 27, 2010
8 http://forums.devshed.com/index.php June 28, 2010
9 http://www.mikeportnoy.com/forum/m2566811.aspx June 29, 2010
I guess then, I'd need to loop through that record set, and run each referrer through a function that would parse out the host. From there, rebuild the query with the new host values and sort that set. Then I could just count how many records match where hosts match.
Or could I just loop through the initial query, and build a list of hostnames. Then use that list to loop through looking for "where XXX like YYY".. that way if I look for referrers that are like
forums.anandtech.com
it would return stuff like the following as a match..
http://forums.anandtech.com/showthread.php?t=2096389
Any advice?
Facebook
Twitter