How to Mass Delete Comments From WordPress Using phpMyAdmin

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:

Comments

  1. Muhammad says:

    You are wicked.. it took me 2 hours to come to your blog which totally solved my problem..

    Thank bro.

    Take care.

  2. Herman says:

    Glad my post was able to help you out.

  3. Muhammad says:

    haha, guess what im back here to delete again.. u rock.. no body knows they can view the comments or delete the spam via user-friendly method, they will opt to teach the other non-userfriendly way.. sigh, and I have the problem to find which site is it. actually u can find that information using filezilla or any ftp program and check the config file. :)

    still, take care dude, i owe you.. if u need a buck, email me. :)

  4. Great Video. Clear to the point and it worked perfectly. Many thanks

  5. Dude, you are awesome!!!

  6. Thanks for the video. Super cool, was concerned I might delete everything

  7. Thankyou so much! I got hit with 40,000 spam comments that were auto approved :(

    I was searching for some type of plugin to get rid of them all at once, but I didn’t find any.

    With this method it only took seconds to get rid of them all.

    Thanks again!

  8. You may need to tweak the actual SQL statement as follows:
    DELETE FROM wp_comments WHERE comment_approved = “0”;

  9. Thanks to you . atlast i am free of 1900 spam comments…thanks again .

  10. Hasib says:

    Great.. It reduced the database size to 1.5 MB from 47 MB… Thanks a lot…

  11. Luke says:

    Cheers! this is what I needed to do. Thanks a million :)

  12. Arvi says:

    Awesome, dude. Thanks a lot for this. Saved me hours of my time!

  13. snsd genie says:

    This blog is just superb, I assumed I do know quite a bit, but I’m so mistaken, like the previous saying the extra you already know, the extra you obtain out how little you know. Thanks for the info.

  14. James says:

    Hi

    Thanks for the advice. Those ba*tards has peppered my site with 5,600 comments.

    You saved me a few hours work.

    Ta

  15. "41145 row(s) deleted. ( Query took 8.4005 sec )"

    Whole process took less than five minutes, backup included. :)

    @virtbiz, you’re quite correct.

  16. JasonsCrazy says:

    Nice post, I have re posted this to my visitors. Very nicely done.

  17. Wajid says:

    How about deleting 5000 approved comments without having to do an install phpmyadmin. There is any plugin for that or any direction on how to do it manually. Please help thanks in advance!

  18. Herman says:

    Waji…no plugin that I know of however if you use Linux servers for web hosting ((which most do) phpMyAdmin is already integrated.

  19. Hi Herman, thanks for the clear instruction. WordPress has an option to set the number of comments to be displayed per page. However, it is for sure quick and easy to do it from the database. I just deleted over 85,000 spam comments in a second! Thanks for sharing!

  20. Herman says:

    Toronto…I’m glad the article saved you some time.

  21. WOW Great plugin! At least i deleted all the unwanted spam comments:)

    Thanks!

  22. Ryan says:

    Hi there, I have the same problem as Wajib: I have been bombarded with some 150 000 spam comments that were auto-approved. Does anyone here know how I might mass delete these?

  23. Petri Lopia says:

    I just found this plugin:
    http://wordpress.org/extend/plugins/delete-spam-daily/

    Which allows me delete all comments marked as spam now and/or daily =)
    It warn’s about version testing but I just used it on my blog and all spam messages are gone now (And yes all real comments are still there).

  24. cheech says:

    Excellent, this is EXACTLY what I was looking for. Quick tip in case anyone else is in the same situation…but I have several WordPress Installs. So, after you go into myPHP admin, open up the first WP database and look at the comments row. There is a number there that shows you how many “rows” of data there are. That equates to how many comments are in the table. So I looked through about 10 WP databases until I saw the one I wanted (which had 15,000+ rows of data so it was clearly the right one!). Then I just clicked empty. I lost a handful of good comments in the process but it was worth it in the end because I can get those back easier than it would be to go through and delete all the comments manually. WP wouldn’t even let me trash 30 comments at a time…can’t imagine doing all that manually. Thanks again!

  25. MAC says:

    Hi, this seems to have 1/2 worked for me. When i go to my wordpress comments section it says there are 0 comments, but when i look at each actual blog page, there are still thousands of comments showing in the number field (ex. “5,208 comments”) . How do I get them to show 0 comments on each blog and not just 0 comments on the comments page?

Trackbacks

  1. [...] How to Mass Delete Word Press Comments Posted by root 45 minutes ago (http://www.drostdesigns.com) How to mass delete comments from your word press database using phpmyadmin follow the rest of the screen prompts to download the database to your computer comment spam ways to remove blog spam comments web design discussion is proudly powered by wordpress Discuss  |  Bury |  News | How to Mass Delete Word Press Comments [...]

  2. [...] and will not touch your approved comments. DELETE FROM wp_comments WHERE comment_approved = 0 SourceDisable Comments on Older PostsFor this query, specify the comment_status as either open, closed, or [...]

  3. [...] whomever wrote this. I had over 17,000 spam comments that slipped onto various pages on this site, and I wasn’t [...]

Speak Your Mind

*

CommentLuv badge