Snitz Forums 2000 - "Recent Topics" improvements

Snitz Forums 2000

Username:	Password:
Save Password
Forgot your Password?

All Forums

Snitz Forums 2000 DEV-Group

DEV Discussions (General)

"Recent Topics" improvements

New Topic

Reply to Topic

Printer Friendly

Author

Topic

Shaggy
Support Moderator

Ireland
6780 Posts

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.

<

Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.”

ruirib
Snitz Forums Admin

Portugal
26364 Posts

Posted - 14 August 2007 : 05:16:15

Actually, if I remember well, it basically involved using getRows to process the resulting records.<

Snitz 3.4 Readme | Like the support? Support Snitz too

Shaggy
Support Moderator

Ireland
6780 Posts

Posted - 14 August 2007 : 05:17:17

Ah, a much better solution, so; I was just going the quick & dirty route

Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.”

ruirib
Snitz Forums Admin

Portugal
26364 Posts

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<

Snitz 3.4 Readme | Like the support? Support Snitz too

Shaggy
Support Moderator

Ireland
6780 Posts

Posted - 14 August 2007 : 06:09:59

Did you ever get it working with MySQL?

<

Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.”

ruirib
Snitz Forums Admin

Portugal
26364 Posts

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.<

Snitz 3.4 Readme | Like the support? Support Snitz too

ruirib
Snitz Forums Admin

Portugal
26364 Posts

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"

Snitz 3.4 Readme | Like the support? Support Snitz too

Topic

New Topic

Reply to Topic

Printer Friendly

Jump To:

Snitz Forums 2000

This page was generated in 0.11 seconds.