22nd Nov
Thursday, November 22nd, 2007
Spam Karma 2 “get recent comments” bugfix on PHP 5.0 and MySQL 5.0.12
There had not been any talk or bugfixes regarding this bug, so I why not share this.
For those who are running the rather good spam filter “Spam Karma” on your blogs and have not actually upgraded for sometime, there are some incompatibility the existing code have with PHP 5.0 and particularly MySQL 5.0.12 on the admin management page “get recent comments”, sprouting the following error:
Can’t fetch comments.
Query: SELECT `posts_table`.`post_title`, `spam_table`.`karma`, `spam_table`.`id` as `spam_id`,`spam_table`.`karma_cmts`, `comments_table`.* FROM `wp_comments` AS `comments_table`, `wp_posts` AS `posts_table` LEFT JOIN `wp_sk2_spams` AS `spam_table` ON `spam_table`.`comment_ID` = `comments_table`.`comment_ID` WHERE `comment_approved` != ‘1′ AND `posts_table`.`ID` = `comments_table`.`comment_post_ID` ORDER BY `comments_table`.`comment_date_gmt` DESC LIMIT 30
SQL error: Unknown column ‘comments_table.comment_ID’ in ‘on clause’
Query: SELECT `posts_table`.`post_title`, `spam_table`.`karma`, `spam_table`.`id` as `spam_id`,`spam_table`.`karma_cmts`, `comments_table`.* FROM `wp_comments` AS `comments_table`, `wp_posts` AS `posts_table` LEFT JOIN `wp_sk2_spams` AS `spam_table` ON `spam_table`.`comment_ID` = `comments_table`.`comment_ID` WHERE `comment_approved` != ‘1′ AND `posts_table`.`ID` = `comments_table`.`comment_post_ID` ORDER BY `comments_table`.`comment_date_gmt` DESC LIMIT 30
SQL error: Unknown column ‘comments_table.comment_ID’ in ‘on clause’
Solution, in your wordpress plugin folder (wp-content/plugin/etc…) look within the file “spam_karma_2_plugin.php” at line#500, you will find:
$query = “SELECT `posts_table`.`post_title`, `spam_table`.`karma`, `spam_table`.`id` as `spam_id`,`spam_table`.`karma_cmts`, `comments_table`.* FROM `”. $wpdb->comments . “` AS `comments_table`, `” . $wpdb->posts .”` AS `posts_table` LEFT JOIN `”. sk2_kSpamTable . “` AS `spam_table` ON `spam_table`.`comment_ID` = `comments_table`.`comment_ID` WHERE $query_where AND `posts_table`.`ID` = `comments_table`.`comment_post_ID` ORDER BY `comments_table`.`comment_date_gmt` DESC LIMIT $query_limit”;
Replace the following with:
$query = “SELECT `posts_table`.`post_title`, `spam_table`.`karma`, `spam_table`.`id` as `spam_id`,`spam_table`.`karma_cmts`, `comments_table`.* FROM (`”. $wpdb->comments . “` AS `comments_table`, `” . $wpdb->posts .”` AS `posts_table`) LEFT JOIN `”. sk2_kSpamTable . “` AS `spam_table` ON `spam_table`.`comment_ID` = `comments_table`.`comment_ID` WHERE $query_where AND `posts_table`.`ID` = `comments_table`.`comment_post_ID` ORDER BY `comments_table`.`comment_date_gmt` DESC LIMIT $query_limit”;
The fix mainly put the whole FROM query item in brackets before the LEFT JOIN, so that SQL can recognized the FROM query in one block and not spilling into the other.
