Finding top ranked articles
Hi guys, Late last night in a matter of moments I found myself creating a table called tbl_020articleTopRanked . The idea was to do something similar to news stories where they display their 5 top-viewed articles (like at SMH: http://www.smh.com.au - at the bottom end of the page). So in my creation I created this table and put 4 fields there, topRankedID (PK - incrementing), articleID (ID of the article which has just been viewed), dateViewed (when it was viewed) and lastly ipAddress (client's IP address). Each time the page gets viewed a check is made to see whether this IP has already viewed the page in the last few hours. If they have then an insert doesn't happen but if they haven't then a record is inserted. It all works fine and I'm very happy, but i'm just trying to work out the best way to work out what are the top viewed pages in the last 12 hours. I also will create a script to delete, say, all inserts over 14 hours old so that i'm not using too much MS-SQL space (this i'll do as a CFSCHEDULE or get the hosting company to set up a schedule to run that script. Any advice or help would be greatly appreciated. Thanks, Saturday ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223005 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Finding top ranked articles
Why does it matter if someone already viewed the article? If you are going to remove historical data anyway, after 14 hours, what does it matter if the person visited 2 hours later or 15 hours later. What about people using proxy servers, you may be blocking out unique views. I think I would axe that, add a column to the article table called views. Create a stored proc or query that selects the article and then does an update. Update article_table set views = views + 1. Or something like that. Then no need to worry about space and scheduling a delete. -Original Message- From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED] Sent: Thursday, November 03, 2005 8:48 AM To: CF-Talk Subject: Finding top ranked articles Hi guys, Late last night in a matter of moments I found myself creating a table called tbl_020articleTopRanked . The idea was to do something similar to news stories where they display their 5 top-viewed articles (like at SMH: http://www.smh.com.au - at the bottom end of the page). So in my creation I created this table and put 4 fields there, topRankedID (PK - incrementing), articleID (ID of the article which has just been viewed), dateViewed (when it was viewed) and lastly ipAddress (client's IP address). Each time the page gets viewed a check is made to see whether this IP has already viewed the page in the last few hours. If they have then an insert doesn't happen but if they haven't then a record is inserted. It all works fine and I'm very happy, but i'm just trying to work out the best way to work out what are the top viewed pages in the last 12 hours. I also will create a script to delete, say, all inserts over 14 hours old so that i'm not using too much MS-SQL space (this i'll do as a CFSCHEDULE or get the hosting company to set up a schedule to run that script. Any advice or help would be greatly appreciated. Thanks, Saturday ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223012 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Finding top ranked articles
This should do the trick cfquery name=qMyQueryName datasource=#MyDSN# maxrows=5 SELECT articleID, COUNT(ID) AS MyHits FROM tbl_020articleTopRanked WHERE DateCreated BETWEEN '#CreateODBCDateTime(Now())#' AND '#CreateODBCDateTime(DateAdd(h, -14, Now()))#' GROUP BY articleID ORDER BY COUNT(ID) DESC /cfquery hth -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223014 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Finding top ranked articles
Sorry, it's early, not enough caffeine and I didn't test the code I forgot to remove the ticks from around the date/time entries in the query... cfquery name=qMyQueryName datasource=#MyDSN# maxrows=5 SELECT articleID, COUNT(ID) AS MyHits FROM tbl_020articleTopRanked WHERE DateCreated BETWEEN #CreateODBCDateTime(Now())# AND #CreateODBCDateTime(DateAdd(h, -14, Now()))# GROUP BY articleID ORDER BY COUNT(ID) DESC /cfquery -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223016 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Finding top ranked articles
Brian, The main problem with that however is that i have no way of knowing when somebody visited the site so I can pick out the top ranked articles from the previous 12 hours. Thanks, Saturday On 3 Nov 2005, at 14:39, Brian Peddle wrote: Why does it matter if someone already viewed the article? If you are going to remove historical data anyway, after 14 hours, what does it matter if the person visited 2 hours later or 15 hours later. What about people using proxy servers, you may be blocking out unique views. I think I would axe that, add a column to the article table called views. Create a stored proc or query that selects the article and then does an update. Update article_table set views = views + 1. Or something like that. Then no need to worry about space and scheduling a delete. -Original Message- From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED] Sent: Thursday, November 03, 2005 8:48 AM To: CF-Talk Subject: Finding top ranked articles Hi guys, Late last night in a matter of moments I found myself creating a table called tbl_020articleTopRanked . The idea was to do something similar to news stories where they display their 5 top-viewed articles (like at SMH: http://www.smh.com.au - at the bottom end of the page). So in my creation I created this table and put 4 fields there, topRankedID (PK - incrementing), articleID (ID of the article which has just been viewed), dateViewed (when it was viewed) and lastly ipAddress (client's IP address). Each time the page gets viewed a check is made to see whether this IP has already viewed the page in the last few hours. If they have then an insert doesn't happen but if they haven't then a record is inserted. It all works fine and I'm very happy, but i'm just trying to work out the best way to work out what are the top viewed pages in the last 12 hours. I also will create a script to delete, say, all inserts over 14 hours old so that i'm not using too much MS-SQL space (this i'll do as a CFSCHEDULE or get the hosting company to set up a schedule to run that script. Any advice or help would be greatly appreciated. Thanks, Saturday ~| Find out how CFTicket can increase your company's customer support efficiency by 100% http://www.houseoffusion.com/banners/view.cfm?bannerid=49 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223019 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Finding top ranked articles
Yep, that worked a treat. I did a little changing to take in to account for time as my site is hosted in the US and i'm in the UK: cfquery name=MyQueryName datasource=user020 maxrows=5 SELECT articleID, COUNT(topRankedID) AS MyHits FROM tbl_020articleTopRanked WHERE DateViewed BETWEEN #CreateODBCDateTime(DateAdd(h, -17, now())) # AND #CreateODBCDateTime(DateAdd(h, 5, now()))# GROUP BY articleID ORDER BY COUNT(articleID) DESC /cfquery Thanks for your help. On 3 Nov 2005, at 14:51, Alan Rother wrote: Sorry, it's early, not enough caffeine and I didn't test the code I forgot to remove the ticks from around the date/time entries in the query... cfquery name=qMyQueryName datasource=#MyDSN# maxrows=5 SELECT articleID, COUNT(ID) AS MyHits FROM tbl_020articleTopRanked WHERE DateCreated BETWEEN #CreateODBCDateTime(Now())# AND #CreateODBCDateTime(DateAdd(h, -14, Now()))# GROUP BY articleID ORDER BY COUNT(ID) DESC /cfquery -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223039 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
Re: Finding top ranked articles
Glad to help =] -- Alan Rother Macromedia Certified Advanced ColdFusion MX 7 Developer ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223042 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Finding top ranked articles
Why does it matter if someone already viewed the article? I actually think that tracking page views and having time limits on counters is a good approach if you have the time. I run a link database that tracks clicks on the links. If someone clicks the same link more than once within six hours, it only counts once. In my situation links are marked as popular of they are within the top 5% by clicks, so this helps prevent people from clicking, hitting back, and clicking again multiple times to inflate their rankings. Clicks are tracked by IP, but I don't worry much about proxy servers for this purpose. Here's the code if the original poster can use it. Watch for line wrapping. -Justin !--- Delete any clicks more than six hours old. --- cfquery name=clickdelete datasource=#request.dsn# DELETE FROM click WHERE cliDateStamp = DATEADD(hh, -6, getdate()) /cfquery !--- Check to see if they've hit this link in the last six hours. --- cfquery name=clickcheck datasource=#request.dsn# SELECT cliID FROM click WHERE linID = cfqueryparam cfsqltype=CF_SQL_INTEGER value=#links.linID# / AND cliIP = cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#cgi.remote_addr# / /cfquery !--- If not found, update the count. --- cfif not clickcheck.recordcount cfquery name=updatecounters datasource=#request.dsn# UPDATE link SET linHitCountCurrent = linHitCountCurrent + 1, linHitCountTotal = linHitCountTotal + 1 WHERE linID = cfqueryparam cfsqltype=CF_SQL_INTEGER value=#links.linID# / /cfquery !--- Add this to the click table. --- cfquery name=clickinsert datasource=#request.dsn# INSERT INTO click VALUES ( cfqueryparam cfsqltype=CF_SQL_VARCHAR value=#cgi.remote_addr# /, getdate(), cfqueryparam cfsqltype=CF_SQL_INTEGER value=#links.linID# / ) /cfquery /cfif !--- not clickcheck.recordcount --- ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223079 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
RE: Finding top ranked articles
Then Alans will work. -Original Message- From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED] Sent: Thursday, November 03, 2005 10:02 AM To: CF-Talk Subject: Re: Finding top ranked articles Brian, The main problem with that however is that i have no way of knowing when somebody visited the site so I can pick out the top ranked articles from the previous 12 hours. Thanks, Saturday On 3 Nov 2005, at 14:39, Brian Peddle wrote: Why does it matter if someone already viewed the article? If you are going to remove historical data anyway, after 14 hours, what does it matter if the person visited 2 hours later or 15 hours later. What about people using proxy servers, you may be blocking out unique views. I think I would axe that, add a column to the article table called views. Create a stored proc or query that selects the article and then does an update. Update article_table set views = views + 1. Or something like that. Then no need to worry about space and scheduling a delete. -Original Message- From: Saturday (Stuart Kidd) [mailto:[EMAIL PROTECTED] Sent: Thursday, November 03, 2005 8:48 AM To: CF-Talk Subject: Finding top ranked articles Hi guys, Late last night in a matter of moments I found myself creating a table called tbl_020articleTopRanked . The idea was to do something similar to news stories where they display their 5 top-viewed articles (like at SMH: http://www.smh.com.au - at the bottom end of the page). So in my creation I created this table and put 4 fields there, topRankedID (PK - incrementing), articleID (ID of the article which has just been viewed), dateViewed (when it was viewed) and lastly ipAddress (client's IP address). Each time the page gets viewed a check is made to see whether this IP has already viewed the page in the last few hours. If they have then an insert doesn't happen but if they haven't then a record is inserted. It all works fine and I'm very happy, but i'm just trying to work out the best way to work out what are the top viewed pages in the last 12 hours. I also will create a script to delete, say, all inserts over 14 hours old so that i'm not using too much MS-SQL space (this i'll do as a CFSCHEDULE or get the hosting company to set up a schedule to run that script. Any advice or help would be greatly appreciated. Thanks, Saturday ~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:223080 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4 Donations Support: http://www.houseoffusion.com/tiny.cfm/54
