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.
Here’s how:
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.
From there, find the database of the site that has all the moderated spam.
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 😉
@jimgroom @timmmmyboy This simple brute-force plugin has saved my bacon a couple times https://t.co/SIk4fzzD5F
— Jason Parkhill (@JasonParkhill) March 4, 2015
A little tip when you are punking around in the database with the delete query; run it first as a SELECT so you can verify you are choosing the right things to delete, e.g. do first
SELECT FROM ‘wp_comments’ WHERE ‘comment_approved’ = ‘0’This one is pretty simple, but anytime I am deleting I try to make sure I get it written correctly (because typos).
When you have that glowing feeling of having power sprayed the database, take it to the next level- set it up as a cron script so it can keep the cleansing automated– see
http://cogdogblog.com/2014/10/06/take-out-the-trash/
Yes, plugins can do lot of this for you (many plugins are just fronts for simple functions), but I find knowing a bit of what is really going on to be helpful at a conceptual level.
I agree with the conceptual level, and I think Tim’s tutelage over the last year has been awesome. part of me just wants to dig down into this stuff for the next year or so. Not because I have any delusions of being a competent sysadmin, but because I feel like dogging in for a while can help me focus and move into that conceptual space more completely. It gets harder to do the more shit you have to juggle administratively, so I think I have to start making some choices about where I want to spend my time. Oh yeah, and then there are movies, comcis, and #ds106.
I never want to be a competent sys admin. So far, 100% success.