Over a million developers have joined DZone.

Manage WordPress comments using SQL

· Java Zone

Easily build powerful user management, authentication, and authorization into your web and mobile applications. Download this Forrester report on the new landscape of Customer Identity and Access Management, brought to you in partnership with Stormpath.

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/

Building Identity Management, including authentication and authorization? Try Stormpath! Our REST API and robust Java SDK support can eliminate your security risk and can be implemented in minutes. Sign up, and never build auth again!


Published at DZone with permission of Jean-Baptiste Jung, DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

The best of DZone straight to your inbox.

Please provide a valid email address.

Thanks for subscribing!

Awesome! Check your inbox to verify your email so you can start receiving the latest in tech news and resources.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}