Manage WordPress comments using SQL

by Jean. 4 Comments -

Although WordPress have an effective built-in comment management interface, for several tasks like deleting bulk comments it is way easier and quicker to use SQL. In this article, I’m going to show you some super useful SQL queries to manage your WordPress comments more easily.

Some things to note

  • Don’t forget to do a backup of your database before testing any of the queries below.
  • Don’t forget to change the default table prefix wp_ by the one used by your database.

Delete all spam comments

When you have over 100,000 spam comments in your spam queue, deleting them using the built-in “delete all spam” button might result in a PHP memory error. To avoid this, just use this simple SQL request to delete all spam at once.

DELETE from wp_comments WHERE comment_approved = 'spam'

Delete all comments between two dates

Had a “spam attack” for a limited time? Here is an easy way to delete all comments between two dates.

DELETE FROM wp_comments 
WHERE comment_date > '2013-11-15 01:10:04'
AND comment_date <= '2013-11-20 00:10:04'

Delete all pending comments

If your "pending comment" queue is filled with 99% spam comments and you don't want to manually review each of them, here is a SQL command to instantly erase all pending comments.

DELETE FROM wp_comments WHERE comment_approved = '0'

Disable comments on all posts at once

Want to disable comments on all of your posts? Instead of closing comments on all your posts the one after the other, why not using this super simple SQL query?

UPDATE wp_posts SET comment_status = 'closed', ping_status = 'closed' WHERE comment_status = 'open'

Disable comments on older posts

To limit spam, why not closing comments on older posts? Here is the SQL to automatically close comments on all posts older than January 1, 2014:

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2014-01-01' AND post_status = 'publish'

Source: http://perishablepress.com/wordpress-discussion-management...

delete comments with a specific url

Even if you're careful when approving new comments, sometimes you might just forget to visit the commenter url and detect a long time after that the linked site is spammy. Here is a very easy way to bulk delete all comments with a specific url, using a simple SQL query.

DELETE from wp_comments WHERE comment_author_url LIKE "%nastyspamurl%" ;

Source: http://www.wprecipes.com/wordpress-tip-bulk-delete-comments...

Search and replace comment text

If there's a specific word or sentence that you want to replace in all comments, here is a very handy SQL query which use the mysql REPLACE function.

UPDATE wp_comments SET `comment_content` = REPLACE (`comment_content`, 'OriginalText', 'ReplacedText')

Globally enable comments for registered users only

A very effective way to dramatically decrease the amount of received spam comment is to enable comments for registered users only. Instead of doing it on each post, here's a SQL query to run in oder to do it on all posts at once.

UPDATE wp_posts SET comment_status = 'registered_only'

Source: http://digwp.com/2010/08/wordpress-sql-comments/

  • http://247techblog.com Mohit Bumb

    Thanks buddy great trick I have lots of spam in comments

  • http://www.coderavenue.com/ Jeff Marlon

    I had usually hurdles while managing my WordPress comments with SQL Server specially to delete comments with a specific url. But now after reading your blog, I am able to manage. I am thankful to you Jean.

  • Tim Nicholson

    This is great stuff, but I don’t think its a good idea to ignore the wp_commentmeta file which will typically have 5-10x as many records as the wp_comments file itself. You’ll need to delete the wp_commentmeta records using DELETE FROM wp_commentmeta WHERE comment_ID IN ( SELECT * FROM wp_comments WHERE comment_approved = ‘spam’ ). Then you can delete the wp_comments themselves as you have described above.

  • chhaya jaiswal

    fhgh