Web Wiz Forums Modifications - MSSQL Last 10 Posts

Print Page | Close Window

MSSQL Last 10 Posts

Printed From: Web Wiz Forums
Category: Web Wiz Web App Support Forums
Forum Name: Web Wiz Forums Modifications
Forum Description: Mod's and Add-on's for Web Wiz Forums.
URL: https://forums.webwiz.net/forum_posts.asp?TID=22375
Printed Date: 30 March 2026 at 1:50pm
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: MSSQL Last 10 Posts

Posted By: markomeara
Subject: MSSQL Last 10 Posts
Date Posted: 10 January 2007 at 10:24am

First of all, find the first closedatabase() command and comment it out... ie

'call losedatabase()

The Access version needs a change to work under MSSQL: 'topic.hide = false" gave me an error... change it to topic.hide = 0 for the MSSQL version...

If you want to display more or less posts, change the number in the for 1 to 10 loop - the 10 determines how many posts to display.

You can see the results at http://www.canadastudentdebt.ca - http://www.canadastudentdebt.ca

This is the code I used ... I put it right before birthdays...

    <tr class="tableSubLedger">
          <td width="38%" align="center" style="color:#FFA1C1">Topic</td>
    <td width="19%" align="center" style="color:#FFA1C1">Forum</td>
       <td width="15%" align="center" style="color:#FFA1C1">Author</td>
       <td width="21%" align="center" style="color:#FFA1C1">Date</td>
     <td width="7%" align="center" style="color:#FFA1C1">Views</td></tr>
<%
Dim intSay
Dim rsLatestPosts2
Dim sql2
Dim rsLatestPosts
Dim strTopicSubject
Dim strTopicSubjectOrj
Dim NumberOfPosts
Dim strFirstPostMsg
Dim LastLatestPostsDateTime
Dim LastLatestPostsDate
Dim LastLatestPostsTime

Set rsLatestPosts = Server.CreateObject("ADODB.Recordset")

strSql = "Select " & strDbTable & "Topic.Subject, " & strDbTable & "Topic.Topic_ID, " & strDbTable & "Forum.Forum_ID, " & strDbTable & "Forum.Forum_name, " & strDbTable & "Topic.Last_Thread_ID, " & strDbTable & "Topic.No_of_views "
strSql = strSql & "From " & strDbTable & "Topic, " & strDbTable & "Forum Where " & strDbTable & "Topic.Hide = 0 And " & strDbTable & "Topic.Forum_ID = " & strDbTable & "Forum.Forum_ID Order By " & strDbTable & "Topic.Last_Thread_ID desc"
rsLatestPosts.Open strSQL, adoCon

If rsLatestPosts.EOF Then
Response.Write "<tr class=""tableRow""><td colspan=5>No recent posts. Please check back later.</td></tr>"
Else

For intSay = 1 to 10
if rsLatestPosts.Eof Then Exit For

Set rsLatestPosts2 = Server.CreateObject("ADODB.Recordset")
sql2 = "SELECT tblThread.Thread_ID, tblThread.Topic_ID, tblThread.Message_date, tblAuthor.Username, tblAuthor.Author_ID FROM (tblAuthor INNER JOIN tblThread ON tblAuthor.Author_ID = tblThread.Author_ID) WHERE tblThread.Hide = 0 And Topic_ID= " & rsLatestPosts("Topic_ID") & " order by Message_date desc"
rsLatestPosts2.Open sql2, adoCon

LastLatestPostsDateTime = rsLatestPosts2("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=" & rsLatestPosts("Topic_ID") & "&get=last#" & rsLatestPosts("Last_Thread_ID") & """ title="""">" & rsLatestPosts("Subject") & "</a></td><td><a class=""smLink"" href=""forum_topics.asp?FID=" & rsLatestPosts("Forum_ID") & """>" & rsLatestPosts("Forum_name") & "</a></td><td align=""center""><span class=""smText"">"&rsLatestPosts2("Username") &"</span></td><td align=""center""><span class=""smText"">" & LastLatestPostsDate & "-" & LastLatestPostsTime & "</span></td><td align=""center""><span class=""smText"">" & rsLatestPosts("No_of_views") & "</span></td></tr>"

rsLatestPosts.MoveNext
Next
end if

'Clean up
Call closeDatabase()

%>
</table></td>

Replies:

Posted By: markomeara
Date Posted: 10 January 2007 at 7:02pm

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!

Posted By: MrMellie
Date Posted: 11 January 2007 at 9:56am

At first glances, it doesn't look to be too efficient. It seems that you're pulling back every single topic in the DB then just displaying the top 10 using the loop, but hitting the database with a query for every one. Works ok on a test system with minimal threads, but wouldn't like to hit my main forum with it!

It'd be far easier just to pull back the top 10 from the DB in the first place. SQL has a TOP function that will do this for you e.g. SELECT TOP 10 * FROM tablename
This will also work in Access, though I don't know about MySQL as I've never used that.

As this is something I'll be requiring for my site's front page, I'll have a look at optimising the code. One of the reasons I chose WWF was because the schema is well constructed and allows this sort of function to be done easily.

Posted By: MrMellie
Date Posted: 11 January 2007 at 2:06pm

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 " & _

Posted By: EvoIX_mr2006
Date Posted: 21 January 2007 at 7:40am

Any way to get this to appear on something other than Default, such as a website homepage?

Posted By: MrMellie
Date Posted: 21 January 2007 at 3:20pm

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.

Posted By: EvoIX_mr2006
Date Posted: 22 January 2007 at 5:37am

Any way you could copy and paste whats needed once you get back to work? I'm having a hell of a time.

Posted By: MrMellie
Date Posted: 22 January 2007 at 3:12pm

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:




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:



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.

Posted By: EvoIX_mr2006
Date Posted: 22 January 2007 at 9:36pm

Ok, everything works except link creation. I have a sub domain for my forums, forums.cicenet.net, so the link tries to go to http://www.cicenet.net/forums.cicenet.net/posthere - www.cicenet.net/forums.cicenet.net/posthere . Any suggestions?

Posted By: MrMellie
Date Posted: 22 January 2007 at 10:30pm

You just need to fully qualify the link:

Response.Write "<a class=""smLink"" href=""http://forums.cicenet.net/forum_posts.asp?TID=" & CLng(saryTop10(fld_Topic_ID,i)) & "&get=last#" & saryTop10(fld_Thread_ID,i) & """ title="""">" & saryTop10(fld_Subject,i) & "</a></td>"

Posted By: Pfeff
Date Posted: 27 January 2007 at 4:23pm

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.1.21-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '10 T.Subject, T.Topic_ID, T.No_of_views, Th.Message_date, Au.Username, Th.Thread' at line 1

/forums/forum/last_posts_ext.asp, line 38

Any ideas?

Posted By: MrMellie
Date Posted: 27 January 2007 at 4:43pm

Yes, I found the bug yesterday after installing MySQL.

I've updated the original post to reflect the changes.

The two lines that read

if strDatabaseType <> "MYSQL"

should read

if strDatabaseType <> "mySQL"

This should then build the correct query string for MySQL.

Posted By: MrMellie
Date Posted: 27 January 2007 at 4:55pm

EvoIX_mr2006 wrote:

Ok, everything works except link creation.� I have a sub domain for my forums, forums.cicenet.net, so the link tries to go to http://www.cicenet.net/forums.cicenet.net/posthere - www.cicenet.net/forums.cicenet.net/posthere .� Any suggestions?

In your forum configuration page, what have you got for the item "Web address path to forum"? If that's correctly configured, I can't see why the original code doesn't work...

Posted By: Pfeff
Date Posted: 27 January 2007 at 5:19pm

It works! You are the goods MrMellie. I can not thank you enough!

Posted By: MrMellie
Date Posted: 27 January 2007 at 5:56pm

Pleasure to be of service.

Posted By: Kezzal
Date Posted: 28 January 2007 at 11:55am

Great Mod - worked first time and easy to adjust further - thanks for putting it together for us!

Posted By: bitbyter
Date Posted: 04 February 2007 at 4:32pm

o.k it's a great mod MR Mellie. thanks for it!

i'd like to see in which (sub)forum the message is posted instead of seeing the beginning of the message . is there an easy way to change this?

istead of this

Topic

Message

Author

Date

Views

this

Topic

Forum

Author

Date

Views

Posted By: PrivateEye
Date Posted: 05 February 2007 at 8:48am

MR Mellie, I tried your code to get top 10 forum names only (not posts/topics in forums). But if users post 10 posts in one forum, this one forum is displayed 10 times. I am not good in SQL grouping, can you please help me and write the query to get true top 10 forum names. I will be thankful to you. I modified the SQL as

Select tblTopic.Subject, tblTopic.Topic_ID, tblForum.Forum_ID, tblForum.Forum_name, tblTopic.Last_Thread_ID, tblTopic.No_of_views
From tblTopic, tblForum
Where
tblTopic.Hide = 0 And tblTopic.Forum_ID = tblForum.Forum_ID
Order By tblTopic.Last_Thread_ID desc

-------------
The Judgement Day