We recently had a mishap while trying out IntenseDebate integration on woikr.com. While importing all our existing comments, it marked all spam comments as approved. It left us with over 6000 comments out of which over 50% were spam and we had no way to separate them from the legitimate ones. It was a disaster.
We finally fixed the issue by using Akismet and some simple SQL scripts. If you have a similar issue, this is something that may help you.
Akismet is a great tool for blocking spam on your WordPress blog, but what if you’re realized and installed it too late and you’ve already got thousands of comments?
When you go to the “Comments” section of WordPress dashboard and click on “Check for spam”, Akismet only “tags” any previous entries as possible and doesn’t “mark as spam”.
As long as they’re not marked as spam, they will continue to show up on the word press posts which isn’t something you’d want. Follow these steps to fix this issue.
Use Akismet to verify the approved comments and tag as spam
Visit the word press Dashboard’s comments section and click on “All” and then “Check for spam”. This will tag them as spam comments but wouldn’t mark them as spam.
Backup your word press database
There are excellent plug-in to backup your database, one of which is “WP Database Backup“. This is so that if you screwed up your database tables – you not loose all the “genuine” comments too.
Accessing phpMyAdmin/Wordpress database tables (Advanced)
Simpler approach – If you’ve got less comments that got approved “Spam”, then you can manually visit each of the comment from the word press dashboard and mark them as “spam”.
In case you’ve already got thousands of spam comments, then proceed further.
Open phpMyAdmin or any other way to access the database tables/data for your word press Blog. If you’re on Dreamhost, it’s at https://panel.dreamhost.com -> MySQL Databases (Tool Box section) -> Click on the phpMyAdmin link against your database hostname.
Query to fetch all the “Approved but Tagged as spam” by Akismet
SELECT *
FROM wp_comments a, wp_commentmeta b
WHERE
a.comment_ID = b.comment_ID AND
a.comment_approved = ‘1’ AND
b.meta_key = ‘akismet_result’ AND
b.meta_value = ‘true’;
Query to update all the Akismet tagged as “spam”.
UPDATE wp_comments SET comment_approved = ‘spam’
WHERE comment_id in (
SELECT distinct(b.comment_id)
FROM wp_commentmeta b
WHERE
b.meta_key = ‘akismet_result’ AND
b.meta_value = ‘true’
);
commit;
The update query should mark all the “Akismet tagged spam” to WordPress Spam.
Verify your comments count
If your blog posts are still reflecting wrong comment count, then use the plug-in “Web Ninja Comment Count Fixer“. It will recheck the comment count and update the database appropriately. It’s pretty simple and straightforward.
Voila, you’re done marking all the spam comments in your approved queue. You can now verify that it’s been fixed and permanently out your “Spam Queue”.
