| T O P I C R E V I E W |
| kyodai |
Posted - 29 June 2009 : 18:26:54
Hi and sorry for disturbing... I am already dangerous enough for basic SQL statements, but now i had the idea to just display like the 5 people that have the highest post count like "Top five posters:".
How would the appropriate SQL staement look like? Is there something like "GET * from users.postcount WHERE 5 highest" or so? I am sure there is some way. Bonus points if you can tell me then how to get the 5 highest post counts of today :) |
| 8 L A T E S T R E P L I E S (Newest First) |
| kyodai |
Posted - 30 June 2009 : 16:18:38
OK at first thanks to all of you. I really thought recordset was an array, but okay now its obvious.
Carefree i used your code, works like a charm. Now i am trying to do the "Top 5 today" and Sisls code looked promising (last 24 hours does make sense), so i tried to implement it like this:
strSql="SELECT TOP 5 M_NAME, (SELECT COUNT(1) AS Count1 FROM FORUM_TOPICS T WHERE T.T_AUTHOR = M_NAME AND T.T_STATUS = 1 AND T.T_DATE > '" & DateToStr(Now-1) & "') AS Topic_Count, (SELECT COUNT(1) AS Count2 FROM FORUM_REPLY R WHERE R.R_AUTHOR = M_NAME AND R.R_STATUS = 1 AND R.R_DATE > '" & DateToStr(Now-1) & "') AS Reply_Count FROM FORUM_MEMBERS ORDER BY Topic_Count DESC"
set rsTop5=my_Conn.Execute(strSql)
Response.Write "<br>Today's most active Members: "
if not rsTop5.EOF then
xx=0
rsTop5.MoveFirst
do until rsTop5.EOF
Response.Write rsTop5("M_NAME")
xx=xx+1
if xx<5 then
Response.Write ", "
else
Response.Write "."
end if
rsTop5.MoveNext
Loop
rsTop5.Close
end if
set rsTop5=Nothing
Okay apart from changing M.M_NAME to just M_NAME (it didnt like the M. somehow) i get an error saying "Conversion failed when converting the nvarchar value 'fronzel' to data type int. " Well i understand so much that it is trying to convert my username (fronzel) to an integer. I think it happens here: "AS Reply_Count" i think i can not get the result as Reply_Count as its no number but a string. How do i need to define it?
Edit: Okay i think the Reply_Count is not the problem as i replaced it with nvarchar and still get the error. So somewhere i seem to convert my username into an integer but i cant understand where. Any ideas?
Edit 2: Also tried the solution by Etymon - but it always gets me back "Incorrect syntax near the keyword 'FROM'. " - although i really cant see anything wrong here. Checked my tables and its definitely FORUM_MEMBERS, dunno what it wants from me. |
| Carefree |
Posted - 30 June 2009 : 15:13:09
quote:
|
| SiSL |
Posted - 30 June 2009 : 15:09:10
GetRows(x), takes X number of records and returns an array, not a result...
if you say:
top5users = rstop.GetRows(5)
Then :
top5user1 = top5users(0,0)
top5user2 = top5users(0,1)
top5user3 = top5users(0,2)
top5user4 = top5users(0,3)
top5user5 = top5users(0,5)
|
| kyodai |
Posted - 30 June 2009 : 12:57:53
Thanks a lot both of you. But now i have a problem to get the names out of the record set. I tried the following pathetic try, but it obviously doesnt work like this:
'## Forum_SQL to grab top 5 posters
strSql = "SELECT TOP 5 M_NAME,M_POSTS FROM FORUM_MEMBERS ORDER BY M_POSTS DESC;"
Set rstop = Server.CreateObject("ADODB.Recordset")
rstop.open strSql, my_Conn, adOpenForwardOnly, adLockReadOnly, adCmdText
if rstop.EOF then
'top5user not available
else
top5user1 = rstop.GetRows(0)
top5user2 = rstop.GetRows(1)
top5user3 = rstop.GetRows(2)
top5user4 = rstop.GetRows(3)
top5user5 = rstop.GetRows(4)
response.write "<br>Top 5 Posters: " & top5user1 & "," & top5user2 & "," & top5user3 & "," & top5user4 & "," & top5user5 & "."
end if
rstop.close
set rstop = nothing
|
| SiSL |
Posted - 30 June 2009 : 07:07:11
Will do Etymon :) Where is FORUM_TOPICS link in that SQL? :p For last 24 hours, I'd use DateToStr(Now-1)
SELECT TOP 5 M.M_NAME, (SELECT COUNT(1) AS Count1 FROM FORUM_TOPICS T WHERE T.T_AUTHOR = M.M_NAME AND T.T_STATUS = 1 AND T.T_DATE > '" & DateToStr(Now-1) & "') AS Topic_Count, (SELECT COUNT(1) AS Count2 FROM FORUM_REPLY R WHERE R.R_AUTHOR = M.M_NAME AND R.R_STATUS = 1 AND R.R_DATE > '" & DateToStr(Now-1) & "') AS Reply_Count FROM FORUM_MEMBERS ORDER BY Topic_Count DESC
Can be for instance :) |
| Etymon |
Posted - 29 June 2009 : 19:42:10
quote:
Originally posted by kyodai
Ah thanks, you're the best. I guess the "5 top posters of today" would be more complicated? Or could i just add like "FROM POSTS WHERE POSTS.POSTDATE = TODAY" ? well or similar to that. Oh and thanks a lot again! Really made my day. :)
That would be:
"SELECT TOP 5 M_NAME,M_POSTS WHERE T_DATE = " & DateToStr(strForumTimeAdjust) & " AND T_STATUS = 1 FROM FORUM_MEMBERS ORDER BY M_POSTS DESC"
In the above, you are checking for posts that are not held (thus hidden) by moderation by looking for the T_STATUS = 1 values. You are checking for posts that are relative to right now while also including your hour offset that may have determined in your admin section under Server Date/Time Configuration. You are checking against strForumTimeAdjust since my today and your today could be two different days.
Remember that if your strTablePrefix variable has a value set to something other than FORUM_ then FORUM_MEMBERS will be different. FORUM_ is the default out-of-the-box value in a fresh Snitz install.
I am pretty sure all of that is correct. Someone will correct me if not. |
| kyodai |
Posted - 29 June 2009 : 18:34:50
Ah thanks, you're the best. I guess the "5 top posters of today" would be more complicated? Or could i just add like "FROM POSTS WHERE POSTS.POSTDATE = TODAY" ? well or similar to that. Oh and thanks a lot again! Really made my day. :) |
| HuwR |
Posted - 29 June 2009 : 18:30:24
depends on the database type you are using as they hvae slightly different syntax, but for Microsoft SQL it would be
something like SELECT TOP 5 M_NAME,M_POSTS FROM FORUM_MEMBERS ORDER BY M_POSTS DESC |
