quote:

---

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.

---

        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

        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

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"