Server Admin Tips: SQL Command for Mass Deletion of Moderated Comments in WordPress

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.

click_on_phpmyadmin

 

From there, find the database of the site that has all the moderated spam.

Screen Shot 2015-03-04 at 10.02.07 AM

After that, look for the wp_comments table in the database, and click on that table.

wp_comments_table

You should now be in the wp_comments table. From there, click on the SQL tab.

Screen Shot 2015-03-04 at 10.03.02 AM

You can now add the SQL command DELETE FROM 'wp_comments' WHERE 'comment_approved' = 0

Screen Shot 2015-03-04 at 10.06.08 AM

You’ll get a dialog box confirming the command.

comment_delete_dialog

 

After that, you should have gotten rid of every commented that has not been approved.

Screen Shot 2015-03-03 at 9.25.38 PM

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 😉

This entry was posted in Domain of One's Own, umw domains, WordPress and tagged , , , , . Bookmark the permalink.

3 Responses to Server Admin Tips: SQL Command for Mass Deletion of Moderated Comments in WordPress

  1. 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.

    • Reverend says:

      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.

  2. I never want to be a competent sys admin. So far, 100% success.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.