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:

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.

«
»

29 Responses to “How to Mass Delete Comments From WordPress Using phpMyAdmin”

  1. Muhammad Says:
    June 16th, 2009 at 1:24 am

    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:
    June 16th, 2009 at 8:33 am

    Glad my post was able to help you out.

  3. How to Mass Delete Word Press Comments | fix my credit Says:
    June 16th, 2009 at 8:54 pm

    [...] 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 [...]

  4. Muhammad Says:
    October 23rd, 2009 at 5:12 am

    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. :)

  5. David Henderson Says:
    October 25th, 2009 at 12:01 pm

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

  6. Book Inspector Says:
    January 28th, 2010 at 12:55 am

    Dude, you are awesome!!!

  7. Jonathan Ingram Says:
    March 1st, 2010 at 3:51 pm

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

  8. Internet Marketing Tips Says:
    April 23rd, 2010 at 3:32 pm

    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!

  9. VIRTBIZ Designer Says:
    June 27th, 2010 at 9:21 pm

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

  10. saptak mandal Says:
    July 12th, 2010 at 2:56 pm

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

  11. Hasib Says:
    August 1st, 2010 at 10:32 pm

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

  12. Luke Says:
    October 28th, 2010 at 7:03 pm

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

  13. Arvi Says:
    November 8th, 2010 at 12:24 pm

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

  14. snsd genie Says:
    December 18th, 2010 at 12:24 am

    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.

  15. James Says:
    January 3rd, 2011 at 4:00 pm

    Hi

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

    You saved me a few hours work.

    Ta

  16. Christopher Junx Says:
    January 8th, 2011 at 1:40 pm

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

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

    @virtbiz, you’re quite correct.

  17. 20 Helpful Wordpress SQL Queries Snippets Says:
    March 24th, 2011 at 2:33 am

    [...] 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 [...]

  18. 20 Helpful WordPress SQL Queries Snippets | Preukson Says:
    March 27th, 2011 at 10:03 am

    [...] Source [...]

  19. Wpfail.com » 20 Helpful WordPress SQL Queries Snippets Says:
    May 4th, 2011 at 7:26 am

    [...] Source [...]

  20. JasonsCrazy Says:
    July 2nd, 2011 at 11:29 am

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

  21. Wajid Says:
    September 3rd, 2011 at 6:13 pm

    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!

  22. Herman Says:
    September 4th, 2011 at 7:04 am

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

  23. Just a quick thanks… | Laughin Jude Says:
    October 16th, 2011 at 4:34 pm

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

  24. Toronto Internet Marketing Says:
    October 18th, 2011 at 7:28 pm

    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!

  25. Herman Says:
    October 21st, 2011 at 7:23 am

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

  26. 20 Useful WordPress Database Tools | DesDevWeb Says:
    November 3rd, 2011 at 8:32 am

    [...] Source [...]

  27. Cure a Yeast Infection Says:
    December 21st, 2011 at 6:47 am

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

    Thanks!

  28. Ryan Says:
    December 29th, 2011 at 4:24 am

    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?

  29. Herman Says:
    December 30th, 2011 at 8:19 am

    Ryan..just follow the steps in the article to mass delete comments..even auto-aproved ones.

Leave a Reply