You might know this problem: for some reason Akismet didn’t quite work and now you have this old WordPress blog with thousands of spam comments. Of course, you could try the ‘Check for spam’ button in the comments panel, but that might not work for every comment.

So here’s a little guide to help you fix this problem easily using phpMyAdmin (which is probably already installed on your server) and some SQL magic.

Warning: you want to make a backup of your database first before using this guide, in case you accidentally delete more stuff than you want.

Okay, let’s get started! First, log into phpMyAdmin and locate the WordPress database (it’s probably called ‘wp’ or ‘wordpress’). From the left-hand column select the ‘wp_comments’ table, and then on the top, click the ‘Search’ button.

In this new screen look for the ‘comment_author_url’ field. From the ‘operator’ dropdown there select the ‘LIKE %…%’ option and enter ‘http’ in the value field. Now press search.

In my experience virtually all spam uses the ‘website’ field to link to their spam site. This list might also include valid comments. If that’s the case but you’re sure you haven’t had a lot of comments in the post few months you could check for the last ‘valid’ comment, note the ‘comment_ID’ and add that to the search as well (use the > operator in the search tab). Otherwise..it’s either bad luck for those people or you need to weed out all the valid comments by hand…

So, now you’ve got this whole list. To delete them simply press the ‘edit’ link underneath the SQL statement on top of the page. It will probably look something like this:

SELECT *  FROM `wp_comments` WHERE LIKE '%http%'

Change the SELECT * into DELETE so it looks like this:

DELETE FROM `wp_comments` WHERE LIKE '%http%'

You’ll get a warning because you’re gonna delete stuff. Press OK and all those pesky comments will be gone!

If this guide was helpful to you feel free to share it on Twitter or Facebook or leave a comment here.

Add a comment