Web Design Discussion

Have you been inundated with spam comments in your Word Press Blog?

Recently I helped a customer that had accumulated over 4000 spam comments on her blog. She tried removing them from the Word Press dash board however it only removes 20 comments at a time. This would take forever because there multiple pages of comments to delete.

How to mass delete comments from your Word Press Database using phpMyAdmin

1. Go to phpmyadmin in your cpanel

This is the management panel that comes with your hosting account if it uses the Apache environment. Once you’re logged in cpanel click on the mySQL databases icon then click on the phpmyadmin link at the bottom of the page.

2. Click your Word Press database link

This is located in the top left and will display all the data in tables.

3. Remove all comments

If you want to remove all comments check the wp_comments table then in the drop down box select empty then press go. Remember this will remove both approved and unapproved comments.

How to remove unapproved comments and keep the approved comments

1. Back up your database - this is to make sure you can restore all your files should you make a mistake. To do this click on your database, click export, make sure all your tables are selected, check SQL, click save file, click go. Follow the rest of the screen prompts to download the database to your computer. You can save it as a text or zipped file.

2. Click on the comments table. ( You can click browse to view all the approved and unapproved comments).

4. Click the mySQL tab. This will open up a query box. Delete the default query and type in:

DELETE FROM wp_comments WHERE comment_approved = 0

Click go
This will delete all the 4000 unapproved comments

5. Remove overhead. These are fragments still left in the database that must be removed. To delete them go to ” check all tables with overhead”, select “optimize table” from the drop down box.

Your database size will now be reduced because you’ve removed the 4000 unapproved comments.

If you check your blog you’ll notice all the comments have disappeared.

Tip
To prevent spam from accumulating install the activate the Akismet plugin. This is a spam filter which allows you to mass delete comments within the Word Press administration panel.

Here’s a video tutorial showing how to use phpmyadmin to remove WP Spam Comments:

This entry was posted on Wednesday, April 1st, 2009 at 8:59 am and is filed under Blogging, Spam . You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.