Get Top 10 posts and topics for MySql

Message Topic Search Topic Options Post Reply Create New Topic Printable Version Translate Topic

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%>