I recently had to clean up the database for a WordPress installation where several thousand spam comments had managed to slip by the filters and get themselves approved. Naturally, going through and marking each individual one as spam would have been a mind-numbing and infuriating process, and there is no ‘Check for Spam’ button in the approved comments queue. Obviously, you can’t allow those spam comments to just sit there, so here’s the solution. Fair warning: it’s a bit geeky.
Log into your phpMyAdmin interface and navigate to the comments table (wp_comments by default). Click on the SQL tab ,paste this snippet of code in, and click ‘Go.’
UPDATE wp_comments SET comment_approved='0' WHERE comment_approved='1'
This will bump all approved comments back to a pending status, which you can then go into and click the ‘Check for Spam’ button. Depending on your server’s settings and the number of spam comments you have to deal with (7000+ in my case), you may end up with a timeout error of some kind, which is ok. Just back up your browser and repeat the check process until all spam comments have been filtered into the appropriate queue. What you should have left are your legitimate comments. (Any leftover spam you should be able to clean up manually without too much trouble.)
From there, depending on how many legitimate comments you have remaining in the pending queue, you can either do a bulk approve using WordPress’s native functions or simply reverse the SQL command you issued above:
UPDATE wp_comments SET comment_approved='1' WHERE comment_approved='0'
Voila! Like magic (and with a bit of brute force), you’ve just gotten rid of a LOT of approved spam comments. It’s not necessarily the most elegant solution, but it beats having to click on every spam comment and preserves all the legitimate comments your readers have made.
Oh, and don’t forget to empty out your spam queue.