MSSQL Last 10 Posts

I should mention that the original code came from Praveen...  I only made a few modifications to get it to work in MSSQL.  Thanks Praveen for the original effort!

Ok, all done!

I've removed a lot of redundant variable declarations too. The result is thus:

<tr class="tableSubLedger">     
<td colspan="2">Latest Forum Posts</td>
</tr>
<tr class="tableRow">     
<td> </td>    
<td> <table cellspacing="1" cellpadding="3" class="tableBorder" align="center">      
    <tr class="tableSubLedger">     
          <td width="38%" align="center" >Topic</td>     
    <td width="19%" align="center" >Forum</td>       
       <td width="15%" align="center" >Author</td>       
       <td width="21%" align="center" >Date</td>          
     <td width="7%" align="center" >Views</td></tr>     
<%

Dim LastLatestPostsDateTime
Dim LastLatestPostsDate
Dim LastLatestPostsTime

strSql = "SELECT TOP 10 T.Subject, T.Topic_ID, Fo.Forum_ID, Fo.Forum_name, T.No_of_views, Th.Message_date, Au.Username, Th.Thread_ID " & _
        "FROM " & strDbTable & "Forum Fo " & _
        "INNER JOIN " & strDbTable & "Topic T ON Fo.Forum_ID = T.Forum_ID " & _
        "INNER JOIN " & strDbTable & "Thread Th ON T.Last_Thread_ID = Th.Thread_ID " & _
        "INNER JOIN " & strDbTable & "Author Au ON Th.Author_ID = Au.Author_ID " & _
        "WHERE T.Hide = 0 " & _
        "ORDER BY Th.Message_date DESC"

rsCommon.Open strSQL, adoCon

If rsCommon.EOF Then
    Response.Write "<tr class=""tableRow""><td colspan=5>No recent posts. Please check back later.</td></tr>"
Else
   do while rsCommon.Eof = false
        LastLatestPostsDateTime = rsCommon("Message_date")
        LastLatestPostsDate = DateFormat(LastLatestPostsDateTime)
        LastLatestPostsTime = TimeFormat(LastLatestPostsDateTime)   
        intForumColourNumber = intForumColourNumber + 1   
        Response.Write(vbCrLf & " <tr ")   
        If (intForumColourNumber MOD 2 = 0 ) Then Response.Write("class=""evenTableRow"">") Else Response.Write("class=""oddTableRow"">")    
        Response.Write("<td>")
        Response.Write "<a class=""smLink"" href=""forum_posts.asp?TID=" & rsCommon("Topic_ID") & "&get=last#" & rsCommon("Thread_ID") & """ title="""">" & rsCommon("Subject") & "</a></td>" & _
        "<td><a class=""smLink"" href=""forum_topics.asp?FID=" & rsCommon("Forum_ID") & """>" & rsCommon("Forum_name") & "</a></td>" & _
        "<td align=""center""><span class=""smText"">" & rsCommon("Username") &"</span></td>" & _
        "<td align=""center""><span class=""smText"">" & LastLatestPostsDate & "-" & LastLatestPostsTime & "</span></td>" & _
        "<td align=""center""><span class=""smText"">" & rsCommon("No_of_views") & "</span></td></tr>"
        rsCommon.MoveNext
    loop
end if
'Clean up
rsCommon.Close
Call closeDatabase()
   
Response.Write "</table></td></tr>"



This doesn't give a true "last 10 posts" though, it gives the last 10 topics that have been posted to. A bit too much like Active Topics for my liking. So, if you want a true last 10 posts, simply replace the SQL definition with this:

strSql = "SELECT TOP 10 T.Subject, T.Topic_ID, Fo.Forum_ID, Fo.Forum_name, T.No_of_views, Th.Message_date, Au.Username, Th.Thread_ID " & _
        "FROM " & strDbTable & "Forum Fo " & _
        "INNER JOIN " & strDbTable & "Topic T ON Fo.Forum_ID = T.Forum_ID " & _
        "INNER JOIN " & strDbTable & "Thread Th ON T.Topic_ID = Th.Topic_ID " & _
        "INNER JOIN " & strDbTable & "Author Au ON Th.Author_ID = Au.Author_ID " & _
        "WHERE T.Hide = 0 " & _
        "ORDER By Th.Message_date DESC"



This could result in the same topic appearing in the list more than once, but for different posts within that thread. This is correct behaviour if any of the last 10 posts made on the forum are within the same topic.

To change the amount of posts returned simply change the TOP 10 part of the SQL query to what ever you want to pull back, e.g. TOP 5 or TOP 20 etc.

The syntax of the SQL query needs changing for MS Access. I've not got an Access forum with data in to try it on but the query ran without errors on a blank WWF Access database.

strSql = "SELECT TOP 10 Subject, " & strDbTable & "Topic.Topic_ID, " & strDbTable & "Forum.Forum_ID, Forum_name, No_of_views, Message_date, Username, Thread_ID " & _
        "FROM " & strDbTable & "Forum Fo " & _
        "INNER JOIN (tblTopic INNER JOIN (" & strDbTable & "Thread INNER JOIN " & strDbTable & "Author " & _
        "ON " & strDbTable & "Thread.Author_ID = " & strDbTable & "Author.Author_ID) " & _
        "ON " & strDbTable & "Topic.Last_Thread_ID = " & strDbTable & "Thread.Thread_ID) " & _
        "ON " & strDbTable & "Forum.Forum_ID = " & strDbTable & "Topic.Forum_ID" & _
        "WHERE " & strDbTable & "Topic.Hide = 0 " & _
        "ORDER By Message_date DESC"



I beleive that the MySQL query would something like the following. It would need testing however as I don't have MySQL to test it on. Note the use of LIMIT, as MySQL doesn't support the TOP command.

strSql = "SELECT T.Subject, T.Topic_ID, Fo.Forum_ID, Fo.Forum_name, T.No_of_views, Th.Message_date, Au.Username, Th.Thread_ID " & _
        "FROM " & strDbTable & "Forum Fo " & _
        "INNER JOIN " & strDbTable & "Topic T ON Fo.Forum_ID = T.Forum_ID " & _
       

Yes, just put the code into an asp file and include it from which ever page you want to show it on. There's a couple of tweaks needed for this though. The file I've done is on my work PC so I can't get to it till I'm back in next week.

Ok, here's the contents of a file I called last_posts_ext.asp


<%

Dim LastLatestPostsDateTime
Dim LastLatestPostsDate
Dim LastLatestPostsTime
Dim intStyle
Dim strShtMess
Dim saryTop10
Dim intRows
Dim fld_Subject
Dim fld_Topic_ID
Dim fld_Views
Dim fld_Date
Dim fld_Author
Dim fld_Thread_Id
Dim fld_Message

'### Set intStyle to give the required output
'### 1 = Top 10 latest posts, regardless of which Topic they appear in (gives a true Top 10 posts, but some might share the same Topic)
'### 2 = Top l0 latest Topics that have been posted to (gives unique Topics).
intStyle=1

strSql = "SELECT "
if strDatabaseType <> "mySQL" then strSql = strSql & "TOP 10 "
strSql = strSql & "T.Subject, T.Topic_ID, T.No_of_views, Th.Message_date, Au.Username, Th.Thread_ID, Th.message " & _
        "FROM " & strDbTable & "Topic T" & strDBNoLock & " " & _
        "INNER JOIN (" & strDbTable & "Thread Th" & strDBNoLock & " INNER JOIN " & strDbTable & "Author Au" & strDBNoLock & " " & _
        "ON Th.Author_ID = Au.Author_ID) "
if intStyle=1 then       
    strSql = strSql & "ON T.Topic_ID = Th.Topic_ID "
else
    strSql = strSql & "ON T.Last_Thread_ID = Th.Thread_ID "
end if       
strSql = strSql & "WHERE T.Hide = 0 " & _
        "ORDER By Th.Message_date DESC "
if strDatabaseType = "mySQL" then strSql = strSql & "LIMIT 10"

rsCommon.Open strSQL, adoCon
saryTop10 = rsCommon.GetRows()

'Clean up
Call closeDatabase()

intRows = UBound(saryTop10, 2)

fld_Subject = 0
fld_Topic_ID = 1
fld_Views = 2
fld_Date = 3
fld_Author = 4
fld_Thread_Id = 5
fld_Message = 6


Response.Write "<table cellspacing=""1"" cellpadding=""3"" class=""tableBorder"" align=""center"">" & vbNewline & _
"<tr class=""tableLedger"">" & vbNewline & _
" <td colspan=""5"">Latest Forum Posts</td>" & vbNewline & _
" </tr>" & vbNewline & _
"      <tr class=""tableSubLedger"">" & vbNewline & _
"        <td width=""25%"" align=""center"">Topic</td>" & vbNewline & _
"        <td width=""33%"" align=""center"">Message</td>" & vbNewline & _
"        <td width=""15%"" align=""center"">Author</td>" & vbNewline & _
"        <td width=""20%"" align=""center"">Date</td>" & vbNewline & _
"        <td width=""7%"" align=""center"">Views</td></tr>" & vbNewline

If intRows=0 Then
    Response.Write "<tr class=""tableRow""><td colspan=5>No recent posts. Please check back later.</td></tr>" & vbNewline
Else
   for i = 0 to intRows
        LastLatestPostsDateTime = cDate(saryTop10(fld_Date,i))
        LastLatestPostsDate = DateFormat(LastLatestPostsDateTime)
        LastLatestPostsTime = TimeFormat(LastLatestPostsDateTime)
        strShtMess = left(saryTop10(fld_Message,i),40) & "..."
        Response.Write vbCrLf & " <tr "
        If (i MOD 2 = 0 ) Then Response.Write("class=""evenTableRow"">") Else Response.Write("class=""oddTableRow"">")    
        Response.Write("<td>")
        Response.Write "<a class=""smLink"" href=""" & strForumPath & "/forum_posts.asp?TID=" & CLng(saryTop10(fld_Topic_ID,i)) & "&get=last#" & saryTop10(fld_Thread_ID,i) & """ title="""">" & saryTop10(fld_Subject,i) & "</a></td>" & _
        "<td><span class=""smText"">" & strShtMess & "</span></td>" & _
        "<td align=""center""><span class=""smText"">" & saryTop10(fld_Author,i) &"</span></td>" & _
        "<td align=""center""><span class=""smText"">" & LastLatestPostsDate & "-" & LastLatestPostsTime & "</span></td>" & _
        "<td align=""center""><span class=""smText"">" & saryTop10(fld_Views,i) & "</span></td></tr>"
    next
end if

Response.Write "</table>"
%>


There is a constant in the file, intStyle, that you can set to toggle between two different query types, true latest 10 posts, or latest 10 topics posted to.

You will also need to put the following in your default.asp home page (not the forum default, but the main one for your site). At the top of the file:

<!--#include file="forum/common.asp" -->
<!--#include file="forum/functions/functions_date_time_format.asp" -->

Change the directory name (here I've used "forum") to suit where your forum sits within your site.

Then add:

Response.Write "<link href=""forum/" & strCSSfile & "default_style.css"" rel=""stylesheet"" type=""text/css"" />"& vbNewline

somewhere in your html header statement. You may not need to use the response.write part of the statement depending how you prefer to code your pages. Don't forget to change the directory name for your forum again.

Lastly add:

<!--#include file="forum/last_posts_ext.asp"-->

at the point you require the Latest posts to appear. I've checked it works on MS SQL and the query also works with MS Access, but it's not tested on MySQL. I *think* the SQL format should be ok for that though.

Edited by MrMellie - 27 January 2007 at 4:45pm