I’ve often thought that WordPress doesn’t handle comments very well. It’s a bit odd considering that it’s a blogging platform after all! But it’s true – it doesn’t have ajax comment posting, front-end moderation, voting, smooth pagination, and a lot of other features we find in modern commenting systems. One of these problems includes the (in)ability to delete comments en masse from your site. Whether it’s pending comments, or even a complete cleanse of previously approved comments.
Trouble Handling Thousands of Comments
I’ve maintained a personal blog since 2006. And in that time, I’ve gotten around 25,000 comments in total as shown here:
Now if I had to delete all these comments in one go, I would either have to:
- Go page by page using the “Bulk Actions” checkbox (not feasible)
- Use a plugin to delete all the comments (annoying for a one-use action)
- Delete the comments directly from the database
In this tutorial, I’ll show you how to do no. 3. Going directly to the database in the backend is the most direct and fastest way to delete all comments of a certain type in WordPress. The only precaution you need is a backup in case something goes wrong.
Step 1: Get the Comments Table Name in the Database
To access the database, login to your cPanel account and open the phpMyAdmin module like this:
Now click the name of your WordPress table and scroll down till you find the table ending with:
It could be something like:
"wp_comments" "xyz_comments" etc...
Different hosts prefix their WordPress table names with different strings of characters randomly. So make sure you get the table name and note it down.
Step 2: Find the Comment Flag you Want to Target
If you click on the comments table, you’ll see all the records appear on the right-hand side of the screen. If you keep scrolling to the right, you’ll see a field called:
In my test blog here, I have two comments – one approved, and one spam:
Taking a look at these two comments in the comments table, you can see that they have the values “1”, and “spam” respectively:
So the “comment_approved” field can take the following values:
- “1” for approved
- “0” for pending
- “spam” for spam comments
Now that we know this, we can construct the SQL query.
Step 3: Building the SQL Query
On the right-hand side of the phpMyAdmin screen after you’ve selected the comments table, choose the “SQL” tab at the top and paste in the following:
To delete all spam comments:
DELETE FROM [comments table name] WHERE comment_approved = 'spam'
Delete all approved comments:
DELETE FROM [comments table name] WHERE comment_approved = 1
To delete all pending comments:
DELETE FROM [comments table name] WHERE comment_approved = 0
Replace [comments table name] with the name of the table you obtained in Step 1. Now click “Go” on the bottom right-hand corner and your query will be executed.
Voila! All your chosen comments have been deleted!
Warning: Take a Backup First with NameHero
Making changes directly to the WordPress database is always dangerous. It’s a great idea to first make a backup before any modifications. To learn how to do this, check out the first three steps of my tutorial on how to upload your website in cPanel.
If something goes wrong, you’ll always have your database backup, and you can use the rest of the tutorial to restore your backup as well! Conversely, NameHero offers daily backups that you can leverage to restore your site in case something goes wrong. It’s a pretty awesome feature that should enable you to sleep peacefully at night!