Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
 All Forums
 Snitz Forums 2000 DEV-Group
 DEV Discussions (General)
 "Recent Topics" improvements

Note: You must be registered in order to post a reply.
To register, click here. Registration is FREE!
Before posting, make sure you have read this topic!

Screensize:
UserName:
Password:
Format Mode:
Format: BoldItalicizedUnderlineStrikethrough Align LeftCenteredAlign Right Horizontal Rule Insert HyperlinkInsert EmailInsert Image Insert CodeInsert QuoteInsert List
   
Message:

* HTML is OFF
* Forum Code is ON
Smilies
Smile [:)] Big Smile [:D] Cool [8D] Blush [:I]
Tongue [:P] Evil [):] Wink [;)] Clown [:o)]
Black Eye [B)] Eight Ball [8] Frown [:(] Shy [8)]
Shocked [:0] Angry [:(!] Dead [xx(] Sleepy [|)]
Kisses [:X] Approve [^] Disapprove [V] Question [?]

 
   

T O P I C    R E V I E W
Shaggy Posted - 14 August 2007 : 05:11:06
Was just helping someone out with tweaking the "Recent Topics" section in pop_profile.asp and noticed there was a little bit of room for improvment. At the moment, this is the SQL being used to pull the topics:
'## Forum_SQL - Find all records for the member
strsql = "SELECT F.FORUM_ID"
strSql = strSql & ", T.TOPIC_ID"
strSql = strSql & ", T.T_SUBJECT"
strSql = strSql & ", T.T_STATUS"
strSql = strSql & ", T.T_LAST_POST"
strSql = strSql & ", T.T_REPLIES "
strSql = strSql & " FROM ((" & strTablePrefix & "FORUM F LEFT JOIN " & strTablePrefix & "TOPICS T"
strSql = strSql & " ON F.FORUM_ID = T.FORUM_ID) LEFT JOIN " & strTablePrefix & "REPLY R"
strSql = strSql & " ON T.TOPIC_ID = R.TOPIC_ID) "
strSql = strSql & " WHERE (T_DATE > '" & strStartDate & "') "
strSql = strSql & " AND (T.T_AUTHOR = " & ppMember_ID
strSql = strSql & " OR R.R_AUTHOR = " & ppMember_ID & ")"
strSql = strSql & " AND (T_STATUS < 2 OR R_STATUS < 2)"
strSql = strSql & " AND F.F_TYPE = 0"
strSql = strSql & " ORDER BY T.T_LAST_POST DESC, T.TOPIC_ID DESC"
set rs2 = my_Conn.Execute(strsql)
As you can see, and as the comment states, this pulls all topics the member has posted in which isn't necessary as there will only ever be a maximum of 10 displayed. For a heavy poster, this will return an unnecessarily large recordset so I propose the following changes:

01) When opening the recordset above (line 389), add the code in green:
set rs2 = my_Conn.Execute(TopSQL(strsql, 10))
02) We can then remove the topic count used when looping through the records by deleting lines 400:
TopicCount = 0
And 441:
TopicCount = TopicCount + 1
And removing the check on topic count from the do loop on line 404:
do until rs2.EOF or (TopicCount = 10)
I know we've made some changes to how "recent topics" are processed here but can't find the topic we discussed it in right now, although I seem to remember they were specific to the database in use here.

<
6   L A T E S T    R E P L I E S    (Newest First)
ruirib Posted - 14 August 2007 : 07:04:53
Just did the test. If int the ORDER By clause of the SQL statement, you remove the alias, the code works on MySQL 4.x and it should work on SQL Server and Access as well.

So, the code looks like this:


strsql = "SELECT F.FORUM_ID"
					strSql = strSql & ", T.TOPIC_ID"
					strSql = strSql & ", T.T_SUBJECT"
					strSql = strSql & ", T.T_STATUS"
					strSql = strSql & ", T.T_LAST_POST"
					strSql = strSql & ", T.T_REPLIES "
					strSql = strSql & " FROM (" & strTablePrefix & "FORUM F INNER JOIN " & strTablePrefix & "TOPICS T"
					strSql = strSql & " ON F.FORUM_ID = T.FORUM_ID)"
					strSql = strSql & " WHERE (T_DATE > '" & strStartDate & "') "
					strSql = strSql & " AND (T.T_AUTHOR = " & ppMember_ID & ")"
					strSql = strSql & " AND (T_STATUS < 2)"
					strSql = strSql & " AND (T_REPLIES < 1) AND F.F_TYPE = 0"
					strSql = strSql & " UNION"
					strsql = strsql & " SELECT F.FORUM_ID"
					strSql = strSql & ", T.TOPIC_ID"
					strSql = strSql & ", T.T_SUBJECT"
					strSql = strSql & ", T.T_STATUS"
					strSql = strSql & ", T.T_LAST_POST"
					strSql = strSql & ", T.T_REPLIES "
					strSql = strSql & " FROM ((" & strTablePrefix & "FORUM F INNER JOIN " & strTablePrefix & "TOPICS T"
					strSql = strSql & " ON F.FORUM_ID = T.FORUM_ID) INNER JOIN " & strTablePrefix & "REPLY R"
					strSql = strSql & " ON T.TOPIC_ID = R.TOPIC_ID) "
					strSql = strSql & " WHERE (T_DATE > '" & strStartDate & "') "
					strSql = strSql & " AND (R.R_AUTHOR = " & ppMember_ID & ")"
					strSql = strSql & " AND (T_STATUS < 2 OR R_STATUS < 2)"
					strSql = strSql & " AND F.F_TYPE = 0"
					strSql = strSql & " ORDER BY T_LAST_POST DESC, TOPIC_ID DESC"
<
ruirib Posted - 14 August 2007 : 06:56:38
I haven't tested it, but it should work with MySQL 4.x out of the box. Anyway, the second part of the code whould work with MySQL, you should not need to use the first part.

Let me see if I get some time to test it on one of my local MysQL DBs.<
Shaggy Posted - 14 August 2007 : 06:09:59
Did you ever get it working with MySQL?

<
ruirib Posted - 14 August 2007 : 05:22:43
Got it. Here ya go: http://forum.snitz.com/forum/topic.asp?TOPIC_ID=59576&whichpage=1&SearchTerms=getRows<
Shaggy Posted - 14 August 2007 : 05:17:17
Ah, a much better solution, so; I was just going the quick & dirty route

<
ruirib Posted - 14 August 2007 : 05:16:15
Actually, if I remember well, it basically involved using getRows to process the resulting records.<

Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000 Version 3.4.07