| Author |
 Topic  |
|
|
dayve
Forum Moderator
USA
5820 Posts |
 Posted - 09 March 2003 : 14:20:58
|
I was browsing through the ASP.NET forums and saw a Top Poster feature (see: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=115769 ) that I thought would be cool to make a mod out of. I have successfully incorporated the mod, but before I package it up I would like some ideas on how to make this streamlined and more efficient. Here is a screenshot first:
currently the sql statement and code I used is:
strSql = "SELECT TOP 25 M.MEMBER_ID "
strSql = strSql & "FROM FORUM_MEMBERS M "
strSql = strSql & "WHERE M.M_NAME <> 'n/a' "
strSql = strSql & "ORDER BY M.M_POSTS DESC"
tPosition = ""
tCount = 1
set rsPost = my_Conn.Execute(strSql)
do while not (rsPost.EOF or rsPost.BOF)
if rsPost("MEMBER_ID") = Reply_MemberID and tCount <=5 then tPosition = "Top 5 Poster ("&tCount&")"
if rsPost("MEMBER_ID") = Reply_MemberID and tCount >5 and tCount <=10 then tPosition = "Top 10 Poster ("&tCount&")"
if rsPost("MEMBER_ID") = Reply_MemberID and tCount >10 and tCount <=25 then tPosition = "Top 25 Poster ("&tCount&")"
tCount = tCount + 1
rsPost.MoveNext
loop
rsPost.close
set rsPost = nothing
and then I place the variable tPosition wherever I want the results to show which I have under the members post count. the number in parenthesis signifies their position in the top 25. I know I could make this more dynamic with an administrative control panel which I am currently working on, but for effeciency on database hits, what can I do to improve it? I have not noticed any major performace degradations right now but I'd gladly accept some input on this.
thanks in advance.
|
 |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 09 March 2003 : 14:23:56
|
well, one thing I just realized was I should probably edit the conditional statement to this:
if rsPost("MEMBER_ID") = Reply_MemberID then
if tCount <=5 then tPosition = "Top 5 Poster ("&tCount&")"
if tCount >5 and tCount <=10 then tPosition = "Top 10 Poster ("&tCount&")"
if tCount >10 and tCount <=25 then tPosition = "Top 25 Poster ("&tCount&")"
end if
|
|
 |
|
|
pweighill
Junior Member
United Kingdom
453 Posts |
Posted - 09 March 2003 : 15:32:43
|
How often do you call this query per page?
You could use the GetRows function to store the data in an array and then just use that in the rest of the page.
set rsPost = my_Conn.Execute(strSql)
arrTopPosters = rsPost.GetRows(25)
rsPost.close
set rsPost = nothing
tPosition = ""
for i= 1 to 25
if arrTopPosters(0,i-1) = Reply_MemberID then
if i <=5 then
tPosition = "Top 5 Poster ("&tCount&")"
elseif i <=10 then
tPosition = "Top 10 Poster ("&tCount&")"
else
tPosition = "Top 25 Poster ("&tCount&")"
end if
end if
next |
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 09 March 2003 : 15:59:20
|
pweighill's suggestion will be a better approach, just need little correction, highlighted in red
set rsPost = my_Conn.Execute(strSql)
arrTopPosters = rsPost.GetRows(25)
rsPost.close
set rsPost = nothing
tPosition = ""
for i= 1 to 25
if arrTopPosters(0,i-1) = Reply_MemberID then
if i <=5 then
tPosition = "Top 5 Poster ("& i &")"
elseif i <=10 then
tPosition = "Top 10 Poster ("& i &")"
else
tPosition = "Top 25 Poster ("& i &")"
end if
end if
next |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 09 March 2003 : 15:59:51
|
| how would I get the individual member id's without nesting this routine in an existing loop? I understand the logic, but not sure how it could be done in topic.asp in order to hit the db once. |
|
|
|
|
GauravBhabu
Advanced Member
4288 Posts |
Posted - 09 March 2003 : 16:02:26
|
| You need to get the top poster ID's and store them in an array and then compare the individual Member ID (Topic Author or Reply Poster) with the Id's in array. |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 09 March 2003 : 16:03:32
|
| yeah, I just saw how that was done in topic.asp for the replies... I think I understand what I need to do now. thanks. |
|
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 09 March 2003 : 16:21:17
|
| Got it working... thanks guys. |
|
|
|
|
PeeWee.Inc
Senior Member
United Kingdom
1893 Posts |
Posted - 09 March 2003 : 16:30:58
|
Dayve - i really do love you man  |
De Priofundus Calmo Ad Te Damine |
|
|
|
DJBBIZ
Junior Member
214 Posts |
Posted - 12 March 2003 : 01:23:54
|
Mod works great...thanks...I wanted to eliminate Admins and Mods from the top posters so only members were included by adding to the WHERE clause:
strSql = strSql & "WHERE M.M_NAME <> 'n/a' AND M.M_LEVEL < 3 "
But this returns this eror:
Subscript out of range: '[number: 24]'
Does the fact that it finds 3 members that fit this profile cause the incrementing to get messed up? |
"The difference between good ideas and good results is performance"
the management institute | tmiFinance | tmiCreative | ProfileOnDemand |
Edited by - DJBBIZ on 12 March 2003 01:25:59 |
|
|
|
OneWayMule
Dev. Team Member & Support Moderator
Austria
4969 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
Posted - 12 March 2003 : 10:21:59
|
quote:
Originally posted by DJBBIZ
Does the fact that it finds 3 members that fit this profile cause the incrementing to get messed up?
yeah, I didn't put in an error trap. let me give that some thought. either that or get more members quickly!!! j/k  |
|
|
|
|
xstream
Junior Member
242 Posts |
Posted - 12 March 2003 : 14:33:30
|
Is there a version with all the changes?
This is awesome.
Xstream |
|
|
|
DJBBIZ
Junior Member
214 Posts |
Posted - 13 March 2003 : 00:37:53
|
quote:
Originally posted by dayve
quote:
Originally posted by DJBBIZ
Does the fact that it finds 3 members that fit this profile cause the incrementing to get messed up?
yeah, I didn't put in an error trap. let me give that some thought. either that or get more members quickly!!! j/k
To clarify the error further, the forum is new but for University students we teach, and registration is not required so there is not a lot of members but there are more than 25...Many of the students, don't register or register and don't post, they just come there to get there resources or links...and leave. Just like the class room, they try not to make "Forum eye contact" and hope not to get called on! As such could the problem be the requirement that you have at least 25 actual posters? It works fine without the mLev=, and there are posters besides the admin and mods. |
"The difference between good ideas and good results is performance"
the management institute | tmiFinance | tmiCreative | ProfileOnDemand |
|
|
|
terryp
Junior Member
United Kingdom
174 Posts |
Posted - 13 March 2003 : 07:20:53
|
| Ready to package this complete yet Dayve? Me wants it. |
  |
|
|
| |
Topic |
|
Topic Locked
