Mass Delete Pending Comments from WordPress

Wednesday, 8 Apr 2009 in WordPress Tips 88 comments

When comments come in for your WordPress blog, they can be one of these three categories.

  1. Approved comments – WordPress has detected that these comments are real comments, and has immediately approved these comments
  2. Pending comments – These comments pass the spam filter, but either WordPress feels they they are some sorta “non human”, or it violates some of the rules you have laid in the blog discussion settings.
  3. Spam comments – If you have the very handy Akismet in place, commented identified as spam will go direct to spam comments.

If your blog is popular to any extent, you can expect to receive a lot of spam comments. These are easy to clear. All you have to do is to click on Comments -> Spam from your WordPress admin page, and you can clear them in one go, with the “Delete all Spam” button.

However, the issue comes when hundreds, or thousands of comments come in and they go into your moderation queue. This means they pass the spam filter.

WordPress allows you to mass delete these comments in the wp-admin – but only in batches of 20 at a time. When you have 853 comments waiting for “moderation”, well, you will start to realise batch deleting in 20s might not be a good idea (you have to do it over 40 times!)

delete-pending-comments

Fortunately, there is a (somewhat) simpler way to do this.

Here are the steps.

#1 Log In to Your Hosting Backend

In my case, and in huge percentage of web hosts around the world, we use CPanel. Depending again on your hosting provider’s settings, the well-known way to access your CPanel is <yourdomain.com>/cpanel.

You will have to key in a username and password.

When you have successfully logged in, you will see a screen like this:

cpanel

#2 Access Your Database Using phpMyAdmin

Look for the phpMyAdmin icon (under databases) and click on it. You should see a page like that when you are in the phpMyAdmin page.

phpmyadmin

Select your WordPress database by clicking on it on the left. You will see a list of tables within that database.

Next, locate the tabs on the right side, and find “SQL”. Click on that.

You will see this screen.

run-mysql-command

#3 Run the Following SQL command on your database

The command is this:

DELETE FROM wp_comments WHERE comment_approved = '0'

You should see a status message saying something like “853 rows affected”.

Then, you are done! Log back in to your WordPress admin panel, and you will find all the pending comments gone! Hurray!

Comments

  1. thank you! This was a huge help when my blog had gotten bot-blasted with spam — 132K comments in the space of a day. I’m set up with akismet now, so should be good

  2. Wow Tyler, 132K?! Imagine if you were to do it 20 at a time. You’d take 9 hours if you took 5 seconds for each batch of 20! Muahah 🙂

    … and actually my client’s blog was already protected with Akismet, and these 800+ still came through.

  3. 😛

    Wow, you’re a lifesaver! These have been pissing me off so much lately…finally installed akismet plugin and going to look into a few others, but there was NO WAY I was going to delete 21,000+ spam comments haha. Thank you!

  6. Thank you!!

    In addition, if you are running WordPress Mu, you would need to add the number of each blog, so your command line would look like:

    DELETE FROM wp_1_comments …..

  7. Good info but when i browsed the interface I saw there was the option of clicking one icon to “empty” the entire wp-comment table (when mouse over it said truncate).

    As i did not have any comments I wanted to preserve this was the fastest and easiest solution for me.

    Once click and it was all gone including all the one already marked as spam WONDERFUL without your post I would not have looked at that option.

    WARNING – The method I mention is only good if you want remove ALL comments.

  8. Good tips. However it only work best if we want to delete all comments. It will be bad if we mistook any real comment in the pending for spam.

  9. Pingback: Delete Pending Comments from WordPress Blog | QUEEN OF KLUDGE

  11. Pingback: Golden Brewski » Blog Archive » Delete Pending Comments from WordPress Blog

  12. Hello, thanks for the detailed description. I went into blue host, and found myPHPadmin but I don’t know which WordPress database is the one I want to delete all the comments on. It’s listed as:
    _wrdp01
    _wrdp02
    _wrdp03
    …..

    Which one is it?

  13. I found it.. but.. it didn’t work..

    I’m using a different template, so I think that is why this cannot be done.. it came up with the following:

    Deleted rows: 0 (Query took 0.0003 sec)

    Hmmm.. why?
    I using a review type of theme.

    Any other methods?

  15. Your f****** command doesn’t work and my blog has turned off!!!
    test the things before publushing them!!
    stupid a******!!!

  17. I can’t tell you enough how much I appreciate this and how much time you just saved me. I giggled when you mentioned the huge number of 853 messages. When the script finished I had the huge sigh of relief when it said 9465. Those 9000+ spammed messages has been a weight around my neck for over a year. They arrived before I understood how comments worked or that there were such things as anti-spam scripts. I had no way to remove them except for 20 at a time and I’m not that bored.

    Thank you 9465 times

  19. Thanks very much for this. It didn’t work for me, as the comments showed up as “approved.” I took a chance and changed the 0 to a 1, and it worked like a charm. My blog’s still there, too!

    If you spam comments are “approved”, try this:

    delete from wp_comments where comment_approved=1

  21. Thanks much for the tip. It’s helped me clear some 5000+ spam comments from my recently revived blog.

    One thing to add: It’s safer to check beforehand how many comments will be deleted without first deleting it – think peace of mind.

    The following SQL will return the number of comments that will be deleted:

    SELECT count(*) FROM wp_comments WHERE comment_approved=0;

    The result should match the number of comments awaiting moderation. Only then do you run the “delete” SQL:

    DELETE FROM wp_comments WHERE comment_approved=0;

    Thanks again for the tip
    !

  22. Pingback: OmniTechNews » Blog Archive » How to All WordPress Spam Comments

  26. Hi Tan,

    Wow you have shared this method in detail… I just posted the same tip at my blog but i have not added those screenshots.. thank you for giving me ideas, if needed i will add screenshots to my post to.

    Keep up good work.
    Regards,
    Mohsin

  27. Thanks so much for this post. You just saved me deleting over 4,500 spam comments by hand! I now have Akismet installed so hopefully I can prevent most of the spam comments from here on out. Again, many thanks for your time-saving post. 😀

  32. Thank You. I had over 4,500 comments and I too do not enjoy deleting comments and wasting hours of my life on senseless crap. You would think wordpress would have this in the comment section by now…but they don’t. I even looked around for plug-ins that had additional functionality…but none. running sql commands are scary..but as long as it works thats just find with me.

  34. Pingback: 3 Ways To Delete All Pending Comments With One Click In WordPress - Gnoted.com

  35. Yay! 8000 spam comments gone!! Thank you so much! 😆
    Now to install Askimet like I should have in the first place, and then write a post about your helpful advice. Thanks again.

  36. Pingback: Jamazoo » Delete your comments in bulk

  37. Thanks for the info, it saved me from the madness of manual deletion. One note, the new WordPress release allows you to change the number of comments posted per page to review (more than 100 seems to freeze it up though).

  38. Thanks! 4,000 spams gone in 0.001 seconds. 😀 True John Ireland; I set it to show 500 comments on the page – boy was that a mistake. 🙁

  45. Wow that was awesome! I don’t know much about mySQL queries but that was as simple as it gets. I just deleted 1841 comments.

  46. This is great but I have a question. Got to the page in my cpanel but I have 1 main domain and 12 subdomains on this one hosting account. How do I know which database file is the file to the 1 site I want to delete comments on? I don’t want to delete all the comments to all the site, just the one site. But the database files are all numbers and I have no way of knowing which file number is to which site. Help!

  47. If NOT workind check your prefix before wp_comment.
    Usually you have some letters before
    HEREwp_comments WHERE comment_approved = ‘0’

    OR YOU CAN USE THIS FOT SPAM
    DELETE FROM (SOMETHING)wp_comments WHERE comment_approved = ‘spam’

  49. 😉 Hey thanks for this tutorial it has saved me a ton of time. I had 1685 comments and was wondering how to delete them all rather than 20 at a time.
    Thanks. 🙂

  50. a good tip, although editing PHP MY ADMIN requires more care than wordpress UI so i would say act with caution and regually backup your database

  51. Pingback: Eliminación masiva de Comentarios Pendientes (WordPress)

  52. The easiest way to do this is to go to the pending comments page and then select screen options from the upper right corner. There you can choose to show for example 1000 comments and then just use the bulk delete…

  53. Thank you ! Thank you ! Thank you ! Thank you ! Thank you ! Thank you ! Thank you ! Thank you ! Thank you ! Thank you ! Thank you ! Thank you ! Thank you ! Thank you !

  56. Hi there.
    Thanks for the above. There is a slower, but more secure way to quickly rid unwanted comments and keep wanted ones.
    In Wp Admin, check the number of total comments in either spam, approved or trash. eg 1009 comments

    Select wp_comments on the left of PHPMy Admin.
    There is an option button Show, next to it, enter the number total unwanted comments as explained above. (You can also enter eg 10 000 records and it will show all the comments at once)

    Select the dropdown for pending, approved, spam or trash you wish to delete the comments from.

    This will show all the records up to eg 1009.
    Now simply scroll to the bottom and click Select All
    Scroll through the comment authors and identify wanted comments, uncheck them and then click on the red X at the bottom (Delete)

  60. The easiest way I found was to go to the pending comments page and then choose screen options from the upper right corner. There you can set wp to show for example 1000 comments per page. And then you can just use the bulk delete option.

  61. Thanks Kian, one of the blogs I host got hit by 1+ million spam comments, there was no other way of cleaning it than going to the MySQL console.

  65. I have a different CP for hosting but your info worked there too! The line of code was the key. Also my comments were approved not held for moderation so I changed the approved=0 to approved=1 and it worked!

  66. After opening phpMyAdmin page, I just clicked on the EMPTY button (Actions – 5th button) against wp_comments and all comments gone. I didnt use any sql.

  71. This was great, I had over 32,000 spam comments. Deleting them 20 at a time would have been …. well I don’t even want to think about it

  73. WOW! Finally a solution. THANK YOU SO MUCH! Just deleted 185,998 comments, all spam even though I have Askimet 🙁 I’ve added key words to block spam, any other suggestions to prevent this from happening again? Thank, No-Idea-What-Im-Doing!

  74. Really big thanks. I deleted over 600 SPAM comments by one click. Btw, is there any powerful method to block SPAM comments? I intalled SPAM plugin but that’s noe effective.

  75. Thank you so much for this. I have been so frustrated with trying to figure out a way to delete these spam comments all at once.

    Thanks again for sharing your technical knowledge!

    Warm wishes,
    Eileen

  77. Thank you for your help.. I have over 1300 spam comments in my blog and it’s really help me to delete those spammy comments.. 😉

  78. Pingback: I’m back, baby! « LAKAMBINI ♥ ORG

  79. Thank you deleete near of 3,500 comment with this Sql Command, i prefer do the select command before the delete after.

    Thank’s

    I put this link on my personnal Blog to help other peoples.

  80. I have about 27k comments that not only made it through the Spam filter, but also made it through the moderation queue somehow. So I have 27k APPROVED spam comments. Around 100 of those are actually legitimate comments that I really want to keep. Is there any shortcut that I can use?

  81. Just found out that one way to minimize the number of times to have to bulk “move to trash” is to go to Screen Options and select a larger number of comments to display on a page.

  83. Thanks a TON!! I have been struggling with how to get rid of these comments for the last few months. I had run my blog for a couple years before installing Askimet, and had racked up quite a collection of pending comments.

    62294 pending comments deleted in about 3 seconds flat!!!

    Very nice. Blog runs a lot smoother now too. Great info, thanks again!!

    -Shaun Cartwright

  84. FANTASTIC

    Words cannot say how much I appreciate your clear easy instructions.
    I am completely Technophobic and incompetent but within 5 minutes 21000 spam entries had been zapped.
    Great stuff.
    thanks
    Mike

  85. So happy I found this information – just saved me a huge a mount of time – thanks!

    Endless amounts of spam comments gone in a second 🙂

    Thanks
    Darren