[Fix]Topics Module and MySQL 5

gotcha · Regular Joined: Mar 14, 2005 Posts: 88

So after switching servers I go to my topics module and all i see is this poorly written message

Quote:

...There aren't any news yet for this topic...

But really there "are news". So what it boiled down to is changing the query to work in this newer version of MySQL. Apparently "reads" is a reserved word now??

Old, Broken Version:

Code:

$sql = "SELECT t.topicid, t.topicimage, t.topictext, count(s.sid) AS stories, SUM(s.counter) AS reads FROM ".$prefix."_topics t LEFT JOIN ".$prefix."_stories s ON (s.topic = t.topicid) GROUP BY t.topicid, t.topicimage, t.topictext ORDER BY t.topictext";

I found by simply changing "reads" to something else fixed the problem.

Changed the above to:

Code:

$sql = "SELECT t.topicid, t.topicimage, t.topictext, count(s.sid) AS stories, SUM(s.counter) AS views FROM ".$prefix."_topics t LEFT JOIN ".$prefix."_stories s ON (s.topic = t.topicid) GROUP BY t.topicid, t.topicimage, t.topictext ORDER BY t.topictext";

Changed:

Code:

$output .= "<big>·</big> "._TOTREADS.": ".(isset($row['reads']) ? $row['reads'] : 0)."";

To:

Code:

$output .= "<big>·</big> "._TOTREADS.": ".(isset($row['views']) ? $row['views'] : 0)."";

And the topics module was back to normal.

montego · Posted: Wed Feb 14, 2007 8:38 pm

gotcha, thanks for this. Just so the RavenNuke crowd knows, this is NOT an issue with that release because RavenNuke uses an enhanced Topics module. It does not have that issue.

Gremmie · Posted: Sun Sep 02, 2007 10:17 am

I know this is old but in case anyone searches for this problem...

I think a simpler fix would be to put back ticks around reads in the SQL. I think that will tell MySQL not to confuse it with the new reserved word called READS.

For example:

Code:

$sql = "SELECT t.topicid, t.topicimage, t.topictext, count(s.sid) AS stories, SUM(s.counter) AS `reads` FROM ".$prefix."_topics t LEFT JOIN ".$prefix."_stories s ON (s.topic = t.topicid) GROUP BY t.topicid, t.topicimage, t.topictext ORDER BY t.topictext";