Tim Owens and I hung out in a coffee shop for a couple of hours before our afternoon visit with the folks at Georgetown’s CNDLS about UMW Domains (more on that in my next post). We were doing some maintenance work on UWM Domains server, and I am trying to learn as much from Tim as I can about server stuff. So, he gave me a pro-tip about the best way to delete large numbers (thousands) of moderated comments in WordPress other than point and click.
With comments marked as spam you can empty the folder (though if there are too many this might be less of load on the server), but there is no easy way to get rid of moderated comments that are spam. And if you get 10,000+, this could take hours to delete manually. Below is a quick trick using an SQL command in phpMyAdmin.
Make sure you know the proper database for your WordPress blog if you have several installed. You can find this in databases or the wp-config file in the blog directory (use File Manager for this). Once you know the proper database, click on phpMyAdmin in the CPanel dashboard.
After that, look for the wp_comments table in the database, and click on that table.
You should now be in the wp_comments table. From there, click on the SQL tab.
You can now add the SQL command
DELETE FROM 'wp_comments' WHERE 'comment_approved' = 0
You’ll get a dialog box confirming the command.
After that, you should have gotten rid of every commented that has not been approved.
This was a huge time saver yesterday, and I am logging it here because I don’t want to forget this trick. Although, as Jason Parkhill noted on Twitter, when it’s WordPress, there is always a plugin for that 😉
— Jason Parkhill (@JasonParkhill) March 4, 2015