Web Wiz Forums Modifications - Get Top 10 posts and topics for MySql

Get Top 10 posts and topics for MySql

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=26025
Printed Date: 29 March 2026 at 9:20am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Get Top 10 posts and topics for MySql

Posted By: Mattias Nordin
Subject: Get Top 10 posts and topics for MySql
Date Posted: 28 July 2008 at 3:19pm

I created the following script to show the top7 posts (including topic) for my web wiz forum version 8. I had it running on a access database. It worked fine.

Now i have installed version 9 on a new server and that server has mySql installed. Anyone knows how to change the connection string to work with mySql?

Is all the table names still valid or do i need to change the sql syntax? I have limited expreience in MySql


<%'===== Access odbc =====
Set objCon = Server.CreateObject( "Adodb.Connection" )
objCon.Provider = "Microsoft.Jet.OLEDB.4.0"
'MdbFilePath = Server.MapPath( "/web2005/fotoalbum/fotoalbum.mdb") 
MdbFilePath = "D:\secureWebAccess\webwizForumet.mdb" 
objCon.ConnectionString = "Data Source='" & MdbFilePath & "'"
objCon.open %>

<%
strDBTrue = "True"
strDBFalse = "False"
strSQL = " SELECT TOP 7 "
strSQL = strSQL & "tblForum.Forum_name, tblTopic.Topic_ID, tblTopic.Subject, tblThread.Thread_ID, tblThread.Message_date, tblAuthor.Author_ID, " &_
" tblAuthor.Username, tblThread.Message, tblTopic.No_of_views  " & _
" FROM tblForum, tblTopic, tblAuthor, tblThread, tblPermissions " & _
" WHERE tblForum.Forum_ID = tblTopic.Forum_ID " & _
" AND tblTopic.Topic_ID = tblThread.Topic_ID " & _
" AND tblAuthor.Author_ID = tblThread.Author_ID " & _
" AND tblForum.Forum_ID = tblPermissions.Forum_ID " & _
" AND (tblPermissions.Group_ID = 2 AND tblPermissions.View_Forum = " & strDBTrue & ") "
strSQL = strSQL & " AND (tblForum.Password = '' OR tblForum.Password Is Null) AND (tblTopic.Hide = " & strDBFalse & " AND tblThread.Hide = " & strDBFalse & ") ORDER BY tblThread.Message_date DESC "
'response.write strSQL
set rs = objCon.execute(strSQL) %>

 
<table border=0 cellpadding=6>
<% while not rs.eof 
varLastAuthor = rs("Username")
varSubject = rs("Subject")
varLastEntryDate = rs("Message_date")
varViewsNum = rs("No_of_views")
varId = rs("Topic_ID")
varForumName = rs("Forum_name")%>
 <tr><td width=34 align="center">
  <a href="/web07/tab5/forum_posts.asp?TID=<%=varId%>&PN=1"><img border=0 src="/web07/tab1/top5fromForum/ico_32_senast_forum.gif"></a><br>
  <a href="/web07/tab5/forum_posts.asp?TID=<%=varId%>&PN=1">Läs</a> 
 </td><td>
  <div class=text>
  <font face=tahoma size=2><b><%=varSubject%></b></font><div style="margin-bottom:3px;"></div> 
  <font face=tahoma size=1 color=gray>Från forumet "<%=varForumName%>"</font><br>
  <font size=1 face=tahoma>
  <%=funForumLogicDate(varLastEntryDate)%> av <%=varLastAuthor %>.&nbsp;
  Läst <%=varViewsNum%> ggr.
  </font>
  </div>
 </td></tr>
<% rs.movenext
wend %>
</table>

<% 'Returnerar när en post senast var uppdaterad. Ex Idag 15:45 eller Torsdag 10:00
function funForumLogicDate(varInputDate)
  varDateDiff = dateDiff("d", varInputDate, now) 
  varHour = hour(varInputDate)
  varHour = "00" &varHour
  varHour = right(varHour,2)
  varMinute = minute(varInputDate)
  varMinute = "00" &varMinute
  varMinute = right(varMinute,2)
 if varDateDiff = 0 then
 varDateText = "Today " &varHour &":" &varMinute
 varDateText = "<font color=red>" &varDateText &"</font>"
 elseif varDateDiff < 7 then
 varDateText = WeekdayName(WeekDay(varInputDate)) &" " &varHour &":" &varMinute 
 else
 varDateText = left(varInputDate,10)
 end if
funForumLogicDate = varDateText 
end function%>

Replies:

Posted By: i2Paq
Date Posted: 28 July 2008 at 8:57pm

Did you test that code with a version 9 using acces?

To my knowledge the code should be the same accessing SQL or Access, otherwise there should be separate WebWiz files for use with Access, SQL or MySQL.

Posted By: Mattias Nordin
Date Posted: 30 July 2008 at 8:48pm

When i run the code above i get;

Microsoft JET Database Engine error '80004005'

Disk or network error.
/web08/default.asp, line 89

Line 89 = objCon.open

In this case it's obvious that it's wrong since i try to open a file that does not exist. If i try to move my old access database to the new server it works fine.

To start with, i need a new connection string to MySQL. Can anyone help me with this?

Posted By: Mattias Nordin
Date Posted: 30 July 2008 at 9:03pm

I think i figured out how to create an ASP connection string:

<%
strSQLServerName = "xxServer" 'Holds the name of the SQL Server (This is the name/location or IP address of the SQL Server)
strSQLDBUserName = " mailto:oregru_se@o12153 - xx User" 'Holds the user name (for SQL Server Authentication)
strSQLDBPassword = "xxPass" 'Holds the password (for SQL Server Authentication)
strSQLDBName = "xxDatabaseName"
'Initilise the DB Connection String
strCon = "DRIVER={MySQL ODBC 3.51 Driver}; Server=" & strSQLServerName & ";User ID=" & strSQLDBUserName & ";Password=" & strSQLDBPassword & ";Database=" & strSQLDBName & ";"
Set objCon = Server.CreateObject( "Adodb.Connection" )
objCon.open strCon
%>

Posted By: Scotty32
Date Posted: 30 July 2008 at 9:16pm

Would it not be easier to include the 'common.asp' file?

-------------
S2H.co.uk - http://www.s2h.co.uk/wwf/" rel="nofollow - WebWiz Mods and Skins

For support on my mods + skins, please use http://www.s2h.co.uk/forum/" rel="nofollow - my forum .

Posted By: Mattias Nordin
Date Posted: 30 July 2008 at 9:57pm

Perhaps. But i got the query string working now. I have another problem though.

I have never used mySql before and am not sure about the syntax. I used "limit 10" to get "Top 10" out of my query. But i do get a strange result compared on how it worked in access.


strDBTrue = "True"
strDBFalse = "False"
strSQL = " SELECT tblForum.Forum_name, tblTopic.Topic_ID, tblTopic.Subject, tblThread.Thread_ID, tblThread.Message_date, tblAuthor.Author_ID, " &_
" tblAuthor.Username, tblThread.Message, tblTopic.No_of_views  " & _
" FROM tblForum, tblTopic, tblAuthor, tblThread, tblPermissions " & _
" WHERE tblForum.Forum_ID = tblTopic.Forum_ID " & _
" AND tblTopic.Topic_ID = tblThread.Topic_ID " & _
" AND tblAuthor.Author_ID = tblThread.Author_ID " & _
" AND tblForum.Forum_ID = tblPermissions.Forum_ID " & _
" AND (tblForum.Password = '' OR tblForum.Password Is Null) AND (tblTopic.Hide = " & strDBFalse & " AND tblThread.Hide = " & strDBFalse & ") " &_
" ORDER BY tblThread.Message_date DESC " &_
" LIMIT 10 "
response.write strSQL

The result is found in the right part of this page:

http://www.oregrundspiren.se/web08/ - http://www.oregrundspiren.se/web08/

Note that the first post is looped 7 times before the second post is shown. How come?

Posted By: Mattias Nordin
Date Posted: 30 July 2008 at 11:17pm

I rewrote the sql code and this works fine on MySql :)

This will show a list of the 10 last topics and the name of the forum they were posted in. Also number of views will be shown.


<%
strDBTrue = "True"
strDBFalse = "False"
strSQL = " SELECT tblTopic.subject, tblTopic.Topic_ID, tblTopic.Topic_ID, tblTopic.No_of_views, tblAuthor.Username, tblThread.Message_date, tblForum.Forum_name" &_
" FROM tblTopic " & _
" INNER JOIN tblThread ON (tblTopic.Topic_ID = tblThread.Topic_ID) " &_
" INNER JOIN tblAuthor ON (tblThread.Author_ID = tblAuthor.Author_ID) " &_
" INNER JOIN tblForum ON (tblTopic.Forum_ID = tblForum.Forum_ID) " &_
" WHERE (tblForum.Password = '' OR tblForum.Password Is Null) AND (tblTopic.Hide = " & strDBFalse & " AND tblThread.Hide = " & strDBFalse & ") " &_
" ORDER BY Message_date DESC " &_
" LIMIT 10 "
'response.write(" Query = " &strSQL)
set rs = objCon.execute(strSQL) %>
<table border=0 cellpadding=6>
<% while not rs.eof 
 varLastAuthor = rs("Username")
 varSubject = rs("Subject")
 varLastEntryDate = rs("Message_date")
 varViewsNum = rs("No_of_views")
 varId = rs("Topic_ID")
 varForumName = rs("Forum_name")%>
  <tr><td width=34 align="center">
   <a href="/web08/forum/forum_posts.asp?TID=<%=varId%>&PN=1"><img border=0 src="/web08/_design/forum_icon.gif"></a><br>
   <a href="/web08/forum/forum_posts.asp?TID=<%=varId%>&PN=1">L&auml;s</a> 
  </td><td>
   <div class=text>
   <font face=tahoma size=2><b><%=varSubject%></b></font><div style="margin-bottom:3px;"></div> 
   <font face=tahoma size=1 color=gray>Fr&aring;n forumet "<%=varForumName%>"</font><br>
   <font size=1 face=tahoma>
   <%=funForumLogicDate(varLastEntryDate)%> (<%=varLastEntryDate%>) av <%=varLastAuthor %>.&nbsp;
   L&auml;st <%=varViewsNum%> ggr.
   </font>
   </div>
  </td></tr>
<% rs.movenext
wend %>

This is what it looks like:
http://www.oregrundspiren.se/web08/ - http://www.oregrundspiren.se/web08/

Best regards
Mattias Nordin

Posted By: i2Paq
Date Posted: 31 July 2008 at 1:57pm

That looks great!

I use the Who Visited Today mod made by Scotty32, download it http://www.s2h.co.uk/wwf/mods/who-visited-today/ - here .

Would you be willing to include this on the forum index.php from that download?

I'm using the 9.50 Free (access db) version, just like you.

I'm willing to donate a small amount of $ to your work

Posted By: caravelle
Date Posted: 26 November 2008 at 12:24am

just installed it and it works fine here on 9.54

Posted By: caravelle
Date Posted: 11 May 2010 at 5:30pm

after update for 9.68 then the last topics is gone :(

is there help to get from someone like the last time ? :)