Snitz Forums 2000
Snitz Forums 2000
Home | Profile | Register | Active Topics | Members | Search | FAQ
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 Help Groups for Snitz Forums 2000 Users
 Help: Database: MySql
 MySQL questions		  New Topic  Topic Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

campinmom03
Junior Member

USA
190 Posts
Posted - 24 March 2008 :  07:22:36  Show Profile
Good morning. I have had my db converted from Access to MySQL. At the moment, I have found that my topics and replies are not appearing where they were prior to this conversion. No errors, just not seeing my topics and replies where they should be.

I did some searching and read this:
quote:
One mySql does not support "SELECT TOP" they support "LIMIT xx". Second, one of the "AND" conditions did not have a space before it so it was contatinating WHere blahblah=1andblahblah=2 instead of where blahblah=1 and blahblah=2"


This maybe the culprit to my problem? I found a piece of code that I believe is preventing my topics and replies from being displayed. Would someone mind taking a look at this?

This is the code I have that I feel might be the cause of my problem:

'## Forum_SQL - Get all active topics from last visit
if flag_maxtopics = 0 then
	strSql = "SELECT "
else
	strSql = "SELECT TOP " & flag_maxtopics & " "
end if
' remove this crap

strSql = strSql & strTablePrefix & "FORUM.F_SUBJECT, " & strTablePrefix & "TOPICS.T_STATUS, "
strSql = strSql & strTablePrefix & "TOPICS.T_VIEW_COUNT, " & strTablePrefix & "TOPICS.FORUM_ID, "
strSql = strSql & strTablePrefix & "TOPICS.TOPIC_ID, " & strTablePrefix & "TOPICS.CAT_ID, "
strSql = strSql & strTablePrefix & "TOPICS.T_SUBJECT, "
strSql = strSql & strTablePrefix & "TOPICS.T_AUTHOR, " & strTablePrefix & "TOPICS.T_REPLIES, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_NAME, " & strTablePrefix & "TOPICS.T_LAST_POST_AUTHOR, "
strSql = strSql & strTablePrefix & "TOPICS.T_LAST_POST, " & strMemberTablePrefix & "MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME "
strSql = strSql & "FROM " & strMemberTablePrefix & "MEMBERS, " & strTablePrefix & "FORUM, "
strSql = strSql & strTablePrefix & "TOPICS, " & strMemberTablePrefix & "MEMBERS AS " & strMemberTablePrefix & "MEMBERS_1 "
strSql = strSql & "WHERE " & strTablePrefix & "TOPICS.T_LAST_POST_AUTHOR = " & strMemberTablePrefix & "MEMBERS_1.MEMBER_ID  "
strSql = strSql & "AND " & strTablePrefix & "FORUM.FORUM_ID = " & strTablePrefix & "TOPICS.FORUM_ID "
strSql = strSql & "AND " & strTablePrefix & "FORUM.CAT_ID = " & strTablePrefix & "TOPICS.CAT_ID "
strSql = strSql & "AND " & strMemberTablePrefix & "MEMBERS.MEMBER_ID=" & id
strSql = strSql & "AND " & strTablePrefix & "TOPICS.T_AUTHOR=" & id
strSql = strSql & " ORDER BY " & strTablePrefix & "TOPICS.T_LAST_POST DESC;"


And this is the code I found to replace it with to make it work, but its not working:

'## Forum_SQL - Get all active topics from last visit
if flag_maxtopics = 0 then
	strSql = "SELECT "
else
	If strDBType = "mysql" then
		strSQL = "SELECT "
	else
		strSql = "SELECT TOP " & flag_maxtopics & " "
	end if
end if
' remove this crap

strSql = strSql & strTablePrefix & "FORUM.F_SUBJECT, " & strTablePrefix & "TOPICS.T_STATUS, "
strSql = strSql & strTablePrefix & "TOPICS.T_VIEW_COUNT, " & strTablePrefix & "TOPICS.FORUM_ID, "
strSql = strSql & strTablePrefix & "TOPICS.TOPIC_ID, " & strTablePrefix & "TOPICS.CAT_ID, "
strSql = strSql & strTablePrefix & "TOPICS.T_SUBJECT, "
strSql = strSql & strTablePrefix & "TOPICS.T_AUTHOR, " & strTablePrefix & "TOPICS.T_REPLIES, "
strSql = strSql & strMemberTablePrefix & "MEMBERS.M_NAME, " & strTablePrefix & "TOPICS.T_LAST_POST_AUTHOR, "
strSql = strSql & strTablePrefix & "TOPICS.T_LAST_POST, " & strMemberTablePrefix & "MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME "
strSql = strSql & "FROM " & strMemberTablePrefix & "MEMBERS, " & strTablePrefix & "FORUM, "
strSql = strSql & strTablePrefix & "TOPICS, " & strMemberTablePrefix & "MEMBERS AS " & strMemberTablePrefix & "MEMBERS_1 "
strSql = strSql & "WHERE " & strTablePrefix & "TOPICS.T_LAST_POST_AUTHOR = " & strMemberTablePrefix & "MEMBERS_1.MEMBER_ID  "
strSql = strSql & " AND " & strTablePrefix & "FORUM.FORUM_ID = " & strTablePrefix & "TOPICS.FORUM_ID "
strSql = strSql & " AND " & strTablePrefix & "FORUM.CAT_ID = " & strTablePrefix & "TOPICS.CAT_ID "
strSql = strSql & " AND " & strMemberTablePrefix & "MEMBERS.MEMBER_ID=" & id
strSql = strSql & " AND " & strTablePrefix & "TOPICS.T_AUTHOR=" & id
strSql = strSql & " ORDER BY " & strTablePrefix & "TOPICS.T_LAST_POST DESC "

if flag_maxtopics <> 0 and strDBType = "mysql" then
	strSql = strSQL & "LIMIT " & flag_maxtopics & ";"
else
	strSql = strSQL & ";"
end if


Any help is greatly appreciated. Thank you.
Have a great day!
Terry

balexandre
Junior Member

Denmark
418 Posts
Posted - 24 March 2008 :  08:24:21  Show Profile  Visit balexandre's Homepage  Send balexandre an ICQ Message
this will help you

http://forum.snitz.com/mod_doc/

and for the Top / Limit issue, why don't you use TopSQL() function?
Bruno Alexandre
(Strøby, DANMARK)

"a Portuguese in Danmark"
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Topic Locked
 Printer Friendly
Jump To:
Snitz Forums 2000 © 2000-2021 Snitz™ Communications Go To Top Of Page
This page was generated in 0.16 seconds. Powered By: Snitz Forums 2000 Version 3.4.07