Microsoft.AspNetCore.Mvc.Localization.LocalizedHtmlStringMOD request : Different read stats.

Author : pitstraight
Subject : MOD request : Different read stats.
Posted : 31 January 2008, 15:29
Message :

At the moment the 'read' counter on any thread just starts from 1 and continually rises. I would like to see a MOD that allows you to find how many reads in the last X days (like the active topics dropdown).
I see something like the Active Topics page, but the dropdown has 1, 7, 14, 30 , 60, 90 days etc ..... and lists the topics sorted by reads (in that timeframe) in desc order.
So, a thread with a HUGE number of reads, but no one has read it today will NOT be shown in the 1-day list, but the thread that was posted and read 3 times WILL appear in the 1-day list. It's kinda hard to desctibe, but I hope you get the idea<

Reply Author: AnonJr
Replied on: 31 January 2008, 16:16
Message:

Hate to sound like a broken record, but I've got the same answer here as for your other question:

To the best of my knowledge, there is no existing MOD to do this. While it is technically possible, and I agree that it would be a good thing from a conceptual standpoint, I'm not sure I would actually implement something like this unless I've got a lot of data storage to burn.

WROX had a statistics package up that I played with for a while, but its in need of some updating and for the short time I needed/used it it generated a sizable chunk of data.

You could look at it for some ideas on how to go about programming what you want. It is worth mentioning that it also got slower and slower as time went on, so you might not want to follow too closely.

http://www.2enetworx.com/dev/projects/statcountex.asp<

Reply Author: pitstraight
Replied on: 31 January 2008, 20:44
Message:

Hmmm ok, I don't know enough about the coding and data storage side or I would do it myself, but could you just store the topic number and date and then add the hits when you want to ?
Possibly you could remove the data more than 12 months old if the storage is getting too big.<

Reply Author: phy1729
Replied on: 31 January 2008, 22:47
Message:

I think the way to approach it would be to add 8 columns to the topics table say Day0 through Day6 or 1-7 depending on how the day is returned by ASP and Last_Day and each day store the day view count in that column each time the day changes which you could tell if Last_Day <> today then clear the column and to get the read count of the prev 7 days just sum the values. Thoughts? I can't code this now but I may later when I have time if you choose to use this method.<

Reply Author: pitstraight
Replied on: 31 January 2008, 23:41
Message:

But I don't want just 8 days, I need a good list going back maybe 2-3 months.
<

Reply Author: AnonJr
Replied on: 01 February 2008, 07:03
Message:

Then your original thought would be a better way to go - have a table that records the topic number and date/time.<

Reply Author: phy1729
Replied on: 01 February 2008, 07:12
Message:

well then you could expand it to be say Day0_0 ..0_1 ... and do a modulo date or probably better go with something else.<

Reply Author: AnonJr
Replied on: 01 February 2008, 08:34
Message:

Just out of curiosity, does your hosting package have any kind of statistical analysis you could use?<

Reply Author: pitstraight
Replied on: 01 February 2008, 14:54
Message:

Yes, I have a stats package I can use but I think it just records topic.asp and not all the stuff on the end like topic id

I'm scratching my head wondering why this is so hard, I guess there's something I'm missing. At the moment there is a counter to record the total number of reads on a specific topic. I just want that added in different ways, not a straight total.
The thing is I don't know enough about the coding to actually make it work, which is why I posted here.<

Reply Author: AnonJr
Replied on: 01 February 2008, 15:07
Message:

Most stat packages I've seen do keep the query string variables when they record the hits, which would allow you to extract that information.
As far as building it into Snitz, its not so much that its hard, its just inefficient. Why do you need this information? (I saw about the drop-down, be more specific and include the rational as to why - it helps when building a program.

) There may be a better way to get what you want...<

Reply Author: pitstraight
Replied on: 01 February 2008, 15:23
Message:

It's for a project that I have to do to measure how popular topics are over a certain timeframe. The dropdown is the key, it limits the days that the read counter is totalled over. So the dropdown basically says "please rank the topics in descending order based on their read count in the last X days" where X can be 1,2,7,14,30, whatever but probably like the ActiveTopics page there is a sensible list of values to choose from.<

Reply Author: phy1729
Replied on: 01 February 2008, 17:28
Message:

Is this for normal users or just for you?<

Reply Author: muzishun
Replied on: 01 February 2008, 17:32
Message:

If you want to go that route, here's how I would do it: create a new table, TOPIC_VIEWS, and store all of the information there. For fields, I'd have something like VIEW_ID (just so there is a unique key), TOPIC_ID, and VIEW_TIME.
Then you could query the table as you wanted to get results for different time frames. It adds some overhead to the forum, but I suppose the tradeoff depends on how busy your forum is or how beefy your server. As AnonJr mentioned, this table will bloat your DB in a hurry, since every time someone reads a topic (thousands of times a day on forums like this one), there's an associated DB entry.<

Reply Author: pitstraight
Replied on: 01 February 2008, 21:08
Message:

I'm thinking just for me at the moment, does that affect the space it will use ?
<

Reply Author: phy1729
Replied on: 01 February 2008, 21:37
Message:

Well if it's just for you you may want to consider adding Google analytics to your forum to do the job for you.<

Reply Author: pitstraight
Replied on: 01 February 2008, 22:16
Message:

My site is behind a firewall, so I can't use it

I have Google Analytics on another site, but that tracks the hits on each page separately ... I've just looked :(

<

Reply Author: pitstraight
Replied on: 03 February 2008, 19:10
Message:

Anyone ?<

Reply Author: muzishun
Replied on: 04 February 2008, 01:31
Message:

See my suggestion above. It wouldn't make a difference whether the feature was only available to you or everybody on your forum, as the bloat is on the database side of the equation rather than the user side. However, the concept is certainly plausible, and the method I described above should work to do what you're wanting.<

Reply Author: cripto9t
Replied on: 04 February 2008, 08:57
Message:

one of these could probably be tweeked to do what you want. Kal Corp
http://forum.snitz.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=33102&SearchTerms=who+viewed

and Nikkols
http://forum.snitz.com/forum/topic.asp?ARCHIVE=true&TOPIC_ID=43287&SearchTerms=who+viewed

Kal Corps mod, minus the memberid row, has the db table you need.

I've never looked at Nikkols mod.
I'll look at modifying Kals later on today.
<

Reply Author: AnonJr
Replied on: 04 February 2008, 09:03
Message:

Originally posted by muzishun
If you want to go that route, here's how I would do it: create a new table, TOPIC_VIEWS, and store all of the information there. For fields, I'd have something like VIEW_ID (just so there is a unique key), TOPIC_ID, and VIEW_TIME.
Then you could query the table as you wanted to get results for different time frames. It adds some overhead to the forum, but I suppose the tradeoff depends on how busy your forum is or how beefy your server. As AnonJr mentioned, this table will bloat your DB in a hurry, since every time someone reads a topic (thousands of times a day on forums like this one), there's an associated DB entry.

I'll ditto that. Its pretty much the same thing I was thinking of. I had intended on putting something quick together this weekend, but things got busy...

<

Reply Author: pitstraight
Replied on: 05 February 2008, 22:46
Message:

cripto9t, how are you going with it ? I've had a quick look at those links that you posted but they don't make a lot of sense to me. I'm not a code monkey by any stretch.<

Reply Author: muzishun
Replied on: 06 February 2008, 02:06
Message:

I'll make the same tentative offer as the other thread. If I wind up having tomorrow off due to the snowstorm, I will try and write something up for this.<

Reply Author: cripto9t
Replied on: 06 February 2008, 10:17
Message:

I've got the basics working. I decided to update the records by the day. It will cut down on the entries a bit.
This is the db setup I have so far

Code:

Code:
        case "mysql"

               strSql = "CREATE TABLE " & strTablePrefix & "TOPIC_VIEWS ( "
	        strSql = strSql & "TOPICVIEW_ID int NOT NULL auto_increment, "
	        strSql = strSql & "TV_TOPIC_ID INT DEFAULT '1' NOT NULL, "
                strSql = strSql & "TV_VIEWS INT DEFAULT '0' NOT NULL, "
	        strSql = strSql & "TV_DATE varchar (14) DEFAULT '' NOT NULL, "
                strSql = strSql & "PRIMARY KEY (TV_TOPIC_ID), "
                strSql = strSql & "KEY " & strTablePrefix & "TOPIC_VIEWS_TOPICVIEW_ID(TOPICVIEW_ID))"

	        my_Conn.Execute (strSql),,adCmdText + adExecuteNoRecords

I might drop the identity column because, right now, I don't see no need for it. I also plan to knock the date down to 8 characters to store just (year,month,day), because the time is irrelevent.
Heres the code in topic.asp to update the counts.

Code:

Code:
        iDate = Left(DateToStr(strForumTimeAdjust),8)

	strSql = "SELECT TV_TOPIC_ID FROM " & strTablePrefix & "TOPIC_VIEWS " &_
		 " WHERE TV_TOPIC_ID = " & Topic_ID & " AND LEFT(TV_DATE,8) = " & iDate

	Set rs = Server.CreateObject("ADODB.Recordset")
	rs.open strSql, my_Conn

	if (rs.EOF or rs.BOF) then 
		my_conn.execute ("INSERT INTO " & strTablePrefix & "TOPIC_VIEWS(TV_TOPIC_ID, TV_VIEWS, TV_DATE) VALUES (" & Topic_ID & ", 1, " & DateToStr(strForumTimeAdjust) & ")")
	else
		my_conn.execute ("UPDATE " & strTablePrefix & "TOPIC_VIEWS SET TV_VIEWS = (TV_VIEWS + 1) WHERE TV_TOPIC_ID=" & Topic_ID & " AND LEFT(TV_DATE,8) = " & iDate )
	end if
	rs.close
	set rs = nothing

And this is what I've come up with to pull the records

Code:

Code:
strSql = "SELECT TV.TOTAL_VIEWS, T.TOPIC_ID, T.T_SUBJECT, T.T_DATE, F.FORUM_ID, F.F_SUBJECT, C.CAT_ID, C.CAT_NAME " & _ 
         "FROM " & strTablePrefix & "TOPICS T " & _ 
         "INNER JOIN (SELECT SUM(TV_VIEWS) AS TOTAL_VIEWS, TV_TOPIC_ID " & _
                     "FROM " & strTablePrefix & "TOPIC_VIEWS " & _
                     "GROUP BY TV_TOPIC_ID) " & _
         "AS TV " & _
         "ON TV.TV_TOPIC_ID = T.TOPIC_ID " & _
         "INNER JOIN " & strTablePrefix & "FORUM F " & _
         "ON T.FORUM_ID = F.FORUM_ID " & _
         "INNER JOIN " & strTablePrefix & "CATEGORY C " & _
         "ON T.CAT_ID = C.CAT_ID " & _ 
         "ORDER BY TV.TOTAL_VIEWS DESC"

Let me say that query will not work in older versions of mysql and I have no idea if it will work with access. Anyway it's testing fine with mysql 5 and ms sql server express.<

Reply Author: muzishun
Replied on: 06 February 2008, 12:12
Message:

Cripto, it looks like we're trading back and forth on the same stuff. I'm also planning on tackling this, now that I have the day off work. I think we're approaching it from slightly different angles, so it shouldn't conflict - just add options.<

Reply Author: cripto9t
Replied on: 06 February 2008, 15:58
Message:

Like I said in the other post, I don't have the time to work on these right now. A storm blew through here last night and took out 2 of my trees. There are 3 down in my grandmothers yard. So I'll be busy the next few days cutting next winters firewood.<

Reply Author: pitstraight
Replied on: 06 February 2008, 23:08
Message:

Ah yes, "by day" sounds good as well.
Thanks guys, all help appreciated ..... and cripto9t, good luck with the wood cutting

<

Reply Author: pitstraight
Replied on: 12 February 2008, 19:45
Message:

Any progress on this one ?<

Reply Author: muzishun
Replied on: 13 February 2008, 01:11
Message:

I haven't made any progress yet. The Post History mod took up more time than I expected it to. I may be able to work on this over the next week or so. Something tells me it will be a little more complex than it seems on the surface, but we shall see.<

Reply Author: pitstraight
Replied on: 27 February 2008, 02:44
Message:

* bump *<

Reply Author: cripto9t
Replied on: 27 February 2008, 08:22
Message:

pitstraight, I haven't had much time to put into this lately, but I'm almost finished. Just a few more issues to work out. Hopefully I'll have it ready by this weekend.

Also like I stated before, I have no idea if the db query will work with an access db and I know it won't work with older versions of mySql. I'm testing with mySql 5 and MSSql7. Just to let you know

. <

Reply Author: AnonJr
Replied on: 27 February 2008, 08:39
Message:

Last I checked, the bigger issues usually run into are things like TOP vs. LIMIT - but if you use the TopSQL() function Snitz has built in you can neatly avoid that problem.
The only other issues I've seen are using reserved words as field names.
Just my 2 cents (USD).

<

Reply Author: muzishun
Replied on: 27 February 2008, 09:16
Message:

cripto, I'm glad you are making progress on this. I, too, have been busy beyond belief the past few weeks. I haven't even been able to work on a couple of my other things that need updating.
I look forward to seeing what you have.<

Reply Author: pitstraight
Replied on: 27 February 2008, 19:21
Message:

Thanks all, I have MS SQL so I guess it will work out of the box.<

Reply Author: muzishun
Replied on: 28 February 2008, 11:49
Message:

I am willing to test on MS Access, assuming I can find the time. Shouldn't be hard to make sure it all works as it should.<

Reply Author: cripto9t
Replied on: 07 March 2008, 14:04
Message:

pitstraight, I still haven't found the time to finish this. I still need to write a file to clean up the old data.

But it does accomplish the task at hand and it looks decient

. I'll email the code to you if you want to try it out. It will take a few weeks on a live forum to load up the read table anyway. Also gives you a chance to look it over and see what I missed. By then I may have the admin file ready.
If anybody else wants to try it out, just ask and I'll send you a copy. muzishun, still wanna test?<

Reply Author: pitstraight
Replied on: 07 March 2008, 16:37
Message:

I would really like to test it

Please send to <my user name>@gmail.com I don't want to write my email here but I think you know what I mean.<

Reply Author: pitstraight
Replied on: 11 March 2008, 15:38
Message:

* bump *

I posted here and sent you an email, but I haven't seen anything arrive in my inbox yet ... would really love to test this

<

Reply Author: AnonJr
Replied on: 11 March 2008, 16:34
Message:

He may be just like me... haven't had a chance to check the ole Inbox in the last week or so...

<

Reply Author: cripto9t
Replied on: 11 March 2008, 17:30
Message:

sorry about that pitstraight. I'll get it to you in the next hour or so.<

Reply Author: pitstraight
Replied on: 12 March 2008, 04:44
Message:

No probs, hope I wasn't pushing to much.<

Reply Author: cripto9t
Replied on: 12 March 2008, 08:56
Message:

Originally posted by pitstraight
No probs, hope I wasn't pushing to much.

Nope, I needed reminding

.
I finally got it off, so check your mail.<

MOD request : Different read stats.

Topic

Replies ...