| Author |
 Topic  |
|
|
modifichicci
Average Member
Italy
787 Posts |
 Posted - 30 January 2005 : 17:33:06
|
Hi!
I have tryed Top POster mod in Mysql, but I get an error with the ruirib SQL statement:
'##### below magic SQL statement original by ruirib.
strSql = "SELECT R_AUTHOR, MEMBER_ID, M_NAME, M_DATE, M_POSTS, M_TITLE, M_LEVEL, M_LASTPOSTDATE, M_AVATAR_URL, COUNT(R_AUTHOR) AS T_POSTS FROM "
strSql = strSql & "(SELECT R_AUTHOR, MEMBER_ID, M_NAME, M_DATE, M_POSTS, M_TITLE, M_LEVEL, M_LASTPOSTDATE, M_AVATAR_URL, R_DATE FROM (" & strMemberTablePrefix &"MEMBERS INNER JOIN " & strTablePrefix & "REPLY ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "REPLY.R_AUTHOR) WHERE R_DATE " & sqlperiod & " AND M_NAME <> '" & strAnonyName & "'" & vbCrLf
strSql = strSql & " UNION " & VbCrLf
strSql = strSql & "SELECT T_AUTHOR, MEMBER_ID, M_NAME, M_DATE, M_POSTS, M_TITLE, M_LEVEL, M_LASTPOSTDATE, M_AVATAR_URL, T_DATE FROM (" & strMemberTablePrefix & "MEMBERS INNER JOIN " & strTablePrefix & "TOPICS ON "
strSql = strSql & strMemberTablePrefix & "MEMBERS.MEMBER_ID="& strTablePrefix & "TOPICS.T_AUTHOR) WHERE T_DATE " & sqlperiod & " AND M_NAME <> '" & strAnonyName & "') As MyView "
strSql = strSql & "GROUP BY R_AUTHOR, MEMBER_ID, M_NAME, M_DATE, M_POSTS, M_TITLE, M_LEVEL, M_LASTPOSTDATE, M_AVATAR_URL ORDER BY Count(R_AUTHOR) DESC, MEMBER_ID ASC;"
I got this error
Microsoft OLE DB Provider for ODBC Drivers error '80040e21'
ODBC driver does not support the requested properties.
/forumlang/test/members_top_poster.asp, line 150
I think, could be it a property of MySql 4? How can I change the string?
TY |
Ernia e Laparocele
Forum di Ernia e Laparocele
Acces - MySql Migration Tutorial
Adamantine forum |
Edited by - Davio on 30 January 2005 22:29:38 |
|
|
Shaggy
Support Moderator
Ireland
6780 Posts |
Posted - 31 January 2005 : 07:57:09
|
As I found out meself a few weeks back, SQL subqueries will not work with a MySQL database.
|
 Search is your friend
“I was having a mildly paranoid day, mostly due to the
fact that the mad priest lady from over the river had
taken to nailing weasels to my front door again.” |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 01 February 2005 : 17:06:04
|
As Shaggy wrote, MySQL does not support neither UNIONS nor subqueries. Using SQL I don't see how you could change that. You'd need to do it using VbScript code.
I think recent MySQL versions were supposed to support one or both features, but I really can't tell whether they do or not. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 02 February 2005 : 06:18:53
|
It's not a single query, it's a union of two queries.
The first query just gets the data from the author of the replies, including name, number of posts, etc. This data is retrieved from the replies table.
The second query does the same, but the data is retrieved from the topics table. However, because of the UNION, the data from both queries is "unified", so that only a single recordset is retrieved, and that recordset is ordered according to the decreasing count of posts made by each poster.
As I said, there is no way for you to this in MySQL. The only thing you could do would be to write both queries separately and then use VbScript to join the results. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
modifichicci
Average Member
Italy
787 Posts |
|
|
modifichicci
Average Member
Italy
787 Posts |
|
| |
Topic |
|
|
|
Topic Locked
