When comments come in for your WordPress blog, they can be one of these three categories.
- Approved comments – WordPress has detected that these comments are real comments, and has immediately approved these comments
- 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.
- 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!)
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:
#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.
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.
#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!
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
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.
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!
Pleasure to help, Sam.
My blog has 2 articles and I have 600 Pending Comments 😕
Didnt think about it before. Thanks a lot!
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 …..
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.
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.
Pingback: Delete Pending Comments from WordPress Blog | QUEEN OF KLUDGE
Thanks alot. Worked like a charm! 14,000 deleted in one quick sweep!
Pingback: Golden Brewski » Blog Archive » Delete Pending Comments from WordPress Blog
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:
Which one is it?
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)
I using a review type of theme.
Any other methods?
Got it solved.. My bad.. 🙂 Choose the wrong database with nil comments.. sql is not that bad after all, NOT SO SCARY as i thought. Easy peasy.. 🙂
MORE COMPLETE INFO….
Your f****** command doesn’t work and my blog has turned off!!!
test the things before publushing them!!
I’m sorry to hear your plight. The command works and it has worked for many others as well. Please read the warning right before the SQL command.
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
Its my pleasure Papabear! 🙂
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
Gotta be careful that one, if you are running a blog that has already has a lot of comments. That deletes all approved comments – including the good ones!
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
Pingback: OmniTechNews » Blog Archive » How to All WordPress Spam Comments
This Post Helped me
😛 Thanks! You saved my life (and time) my client had over 17,000 spam awaiting! Great tip.
Great Tip and clearly explained!
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.
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. 😀
Just ran the command and eliminated 25,000 comments and immediately installed Akismet, not sure how I missed it. Thanks for the info.
THANK YOU!!!! This has saved me a lot of angst. 7000 comments gone in a wink 🙂
seriously, you really helped me…. Cheers !!!! deleted 790 spam comments in one go.
Thank you! This saved me a boat load of time today.
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.
I had 30,000. Now I have zero. yay.
Pingback: 3 Ways To Delete All Pending Comments With One Click In WordPress - Gnoted.com
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.
Pingback: Jamazoo Â» Delete your comments in bulk
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).
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. 🙁
Man, thanks so much!
Almost 34K, not too bad.
LOL BoB. I like your graphic!
Yeah very expressive lol, thans for sharing the tuto, very helpful
I just deleted 32,036 spam comments with your instructions. Thank you so much!
You got to try this plug in
much easier for the non technical person like myself!
😯 IT IS GOOD. man this is real help 😎 Cool Thanks for help with nasty spam
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.
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!
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’
THANKS! 61,000 comments deleted!
😉 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.
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
Pingback: Eliminación masiva de Comentarios Pendientes (WordPress)
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…
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 !
This is extermly cool solution!!
Thank you so much for that.
All the best
4615 row(s) deleted. ( Query took 0.2209 sec )
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)
this works! thanks so much!
Hi Gerhard – thanks! The tip was written some time back when there wasn’t this function and we had to manually go page by page to delete the pending comments 🙂
thanks a lot!, you saved me
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.
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.
Thanks for this helpful info. Fixed my 11,000 spam comments issue with my blog. A+ info.
Thank you,fixed my 3k spam comments
Sincere thanks for posting this… it saved me many hours. 🙂
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!
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.
Cheers… I had 400.711 Pending Comments! http://myli.eu/400711
Hi Mathukutty – that deletes all comments, including approved ones. 🙂
Thank you so much! it works!
10410 row(s) deleted. ( Query took 0.8635 sec )
Thank you SO much!! I had no idea what I was going to do 😀
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
Wow. worked great. I was deleting manually and then thought…just do a google search already! thanks.
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!
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.
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!
Is there a way to delete all comments? I need to delete 400,000 approved comments which are also spam.
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.. 😉
Pingback: I’m back, baby! « LAKAMBINI ♥ ORG
Thank you deleete near of 3,500 comment with this Sql Command, i prefer do the select command before the delete after.
I put this link on my personnal Blog to help other peoples.
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?
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.
Perfect tutorial, worked splendidly.
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!!
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.
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 🙂
Years have passed since you wrote this post and its still helping people like me… thank you.
Good clear instruction, thanks a lot.
I have pending comments that I am unable to access from the WP dashboard. Maybe I’ve been hacked, but this is going to be my first action.
This post was a lifesaver, thanks so much!! Was not sure how I was going to get myself out of this one with over 3,000 pending comments to purge.