| Author |
 Topic  |
|
DoraMoon
Average Member
Taiwan
661 Posts |
 Posted - 30 May 2002 : 22:56:05
|
the idea take from a MOD call Syndication, it can list the newest or most popular topics recently.
and i want to get these Top xx information together on a page....
i try the top 5 new coming members, the top 5 posts members... and so on.
(i know u just can get it by changing the members page's order, but i still want to put these together..)
but as u know, the only thing i can do about ASP is Copy and Paste... 
so i just "EXTRACT" the top 5 members list from members.asp...
and unexpectedly it got to work !!! 
i think it just need a simple change at SQL string... just like this..
strsql = "SELECT TOP 5 * FROM " & strMemberTablePrefix & "MEMBERS"
strSql = strSql & " ORDER BY " & strMemberTablePrefix & "MEMBERS.M_POSTS DESC, " & strMemberTablePrefix & "MEMBERS.M_NAME ASC"
but i found it really not what i want lately, about the Top 5 posts member...
for example, this forum, Richard 10527posts!!, HuwR 7403posts, Nathan 5992posts .....
i am wonder who can exceed them in a short time..
so actually i need is the Top 5 posts member IN A GIVING PERIOD...
e.g. who posts most in 7 days or 30 days nearly.
of course, i do'nt know how to add a time period to above SQL string...
(even though i think it just need .. strSql = strSql & WHERE in 30 days ..... !?? )
so can someone help me how to solve this SQL puzzle ?
thanks for help. hope this not a stupid question .. 
~~ ¡¹ ¡¸ ¡¸¡¹ ¡¸ ¡¹ ~~ |
|
|
DoraMoon
Average Member
Taiwan
661 Posts |
Posted - 04 June 2002 : 03:02:13
|
someone can tell me is it possible to do it ??
-- Top 5 members of most posts in a time period --
thx ~~
~~ ¡¹ ¡¸ ¡¸¡¹ ¡¸ ¡¹ ~~ |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 04 June 2002 : 06:31:59
|
It is possible to know what you want to know, but you are not doing it the right way. You'll never find out the top posters in a period by looking at the M_POSTS field, because this field counts all posts since the member registered at the forum.
To know what you want to know, you'll need to count the number of topics and replies done by each member over a period and select the top 5 members with more topics and replies posted over that period. Since we are dealing with topics and replies this means you need to use a UNION query to do it. Here is a sample script to do it. You can pick from here:
<!--#INCLUDE FILE="config.asp" -->
<!--#INCLUDE FILE="inc_functions.asp" -->
<%
Dim strSql, strConn, conn
strMydate = DateToStr(dateadd("d",-30,Now()))
strSql = " SELECT TOP 5 R_AUTHOR, M_NAME, COUNT(R_AUTHOR) AS T_POSTS FROM "
strSql = strSql & "(SELECT R_AUTHOR, M_NAME, R_DATE FROM (" & strMemberTablePrefix &"MEMBERS INNER JOIN " & strTablePrefix & "REPLY ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "REPLY.R_AUTHOR) WHERE R_DATE > '" & strMyDate & "'" & vbCrLf
strSql = strSql & " UNION " & VbCrLf
strSql = strSql & "SELECT T_AUTHOR, M_NAME, T_DATE FROM (" & strMemberTablePrefix & "MEMBERS INNER JOIN " & strTablePrefix & "TOPICS ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "TOPICS.T_AUTHOR) WHERE T_DATE > '" & strMyDate & "') As MyView "
strSql = strSql & "GROUP BY R_AUTHOR,M_NAME ORDER BY Count(R_AUTHOR) DESC;"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open strSql, strConnString
While Not Rs.EOF
Response.Write rs("M_NAME") & " " & rs("T_POSTS") & "<br>"
rs.MoveNext
Wend
rs.Close
%>
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs
Edited by - ruirib on 04 June 2002 07:01:20 |
|
|
|
DoraMoon
Average Member
Taiwan
661 Posts |
Posted - 04 June 2002 : 07:55:50
|
Thanks !!! ruirib
it's great ! work fine and just what i needed ! 
now the only thing to remain just add it into my HTML web page.. YA!!
i think the SQL query should be more complicated and profound than i though.
i'm hardly to understand what this query string meaning.. (so wonderful UNION !! )
so i will try to study this later, and learning more about SQL...
deeply appreciated your help and teach me a valuable lesson ! 
~~ ¡¹ ¡¸ ¡¸¡¹ ¡¸ ¡¹ ~~ |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
DoraMoon
Average Member
Taiwan
661 Posts |
Posted - 16 June 2002 : 20:05:33
|
Hi~~ (ruirib, hope you can see this...  )
here is my top 5 poster file inc_topposter.txt
(i have a little change to get more information from database... and i really not sure it's correct or not, i just use the most stupid method - keep trying.. )
i include it to my default page, and it seem work very good until....
something strange... it does'nt get any problem on my PWS+Win98, but when i upload to host server, i got this error message:
Microsoft JET Database Engine Error '80040e21'
can't on SELECT sentence of UNION query using Memo¡BOLE or hyperlink object field 'M_LNEWS'¡C
/othello/forum/inc_topposter.asp, line 15
(sorry, i just translate this message from Chinese to my poor English)
and i search here about this error, there are many posts talking about blob text fields problem.... then i try to change the order of "M_LNEWS", but it seem not to work.
it's ok for me to remove the "M_LNEWS" query or just change the field property from Memo to Text. but i still want to know...
is it just the server problem ?? or something mistake in my including file ??
thanks if someone can teach me more about these SQL knowledge.
~~ ¡¹ ¡¸ ¡¸¡¹ ¡¸ ¡¹ ~~
Edited by - DoraMoon on 17 June 2002 02:11:48 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
DoraMoon
Average Member
Taiwan
661 Posts |
Posted - 16 June 2002 : 20:51:21
|
Thank ruirib for quick reply ...
i just want to "call out" more data information by this query,
so it not just show the M_NAME & T_POSTS only.
and in fact, now the only thing i can do, just call more field i needed from the MEMBERS table. i have no idea how to get data from another tables by using UNION query...
i do'nt know is it right or wrong, but it really take effect on my PWS computer. just get error when i put it on hosting server.
~~ ¡¹ ¡¸ ¡¸¡¹ ¡¸ ¡¹ ~~ |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 16 June 2002 : 21:47:37
|
You can call more fields from the members table, but you need to call the same fields in the same order in both sides of the UNION keyword, so to speak...
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 17 June 2002 : 14:34:28
|
quote:
i just want to "call out" more data information by this query,
so it not just show the M_NAME & T_POSTS only.
and in fact, now the only thing i can do, just call more field i needed from the MEMBERS table. i have no idea how to get data from another tables by using UNION query...
Dora,
If you look at the SQL statement attentively you'll notice that what you really have is two different queries, unioned together by the UNION keyword. The "requirement" to unite two different queries is that the queries have the same number of returned columns and that you choose "equivalent" (the word is bad, but that's what I can think of now) fields to be returned in the same position in both queries.
In the current query you have : R_AUTHOR->T_AUTHOR, M_NAME->M_NAME, R_DATE->T_DATE. All these are "equivalent".
You can change both queries to include additional fields from the same tables or joined from addtional tables, as long as they respect these rules.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
dayve
Forum Moderator
USA
5820 Posts |
|
|
HarshReality
Junior Member
USA
128 Posts |
Posted - 23 June 2002 : 23:55:21
|
little things maybe, have member name goto the profile. Have the post count be a link to the members last post. I am full of silly ideas like that.....
Anti Social Personality |
|
|
|
Topic |
|
Topic Locked
.