How to Manage WordPress comments using SQL

0

In a bid to combat the comment spamming issue, WordPress developers often choose to use plug-ins. If you want to block spam comments on your WP blog permanently and make way for genuine discussions, you need to know how to bring SQL to play. Here are some handy SQL codes that can help you manage WordPress more efficiently than you can even imagine. You can use PHPMyAdmin, a free-to-use and open-source tool, which can make spam removal and comment management a cakewalk for you. Just do not forget to take a backup of the database before you run SQL+ queries on your server.

How to remove all comments from the server database

Use the below-furnished query to remove all comments marked as ‘spam’ from your database.

DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';

The best part is, the code works perfectly on all WP versions including the most recent, WP 3.9.2!

How to disable/enable comments

In the WP database, under the ‘wp_posts’ table, you can find a column named ‘comment_status’. The column will contain the following values for each post or row.

  • Open (comments are open to every visitor)
  • Closed (comments are closed to every visitor)
  • Registered_only (Comments can only be seen by registered and logged-in users)

You may run the following SQL queries (using phpMyAdmin or any other open source tool which is used in MySQL) to change the default comment-management settingas. (again, do not forget to take backup of your database)

  • For global comment enabling, use UPDATE wp_posts SET comment_status = ‘open’;
  • For global comment enabling, use UPDATE wp_posts SET comment_status = ‘closed’;
  • For global comment enabling for registered users, use UPDATE wp_posts SET comment_status = ‘registered_only’;

For global enabling/disabling comments posted before a certain date, however, you need to specify comment_status and then specify the date as well (default date is 2008-01-01). See an example here:

UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2014-08-06' AND post_status = 'publish';

You may run this query more than once each year for enabling/disabling comments on your old blog posts. You need to combine the code with similar ones for managing trackbacks and pingbacks.

How to enable and disable pingbacks?

You can also find a column named ‘ping_status’ in the wp_posts table. The option can be manipulated for managing pingbacks/trackbacks. Use any of the following values for each post (value).

  • Open (everyone can see pingbacks/trackbacks)
  • Closed (no one can see the pingbacks/trackbacks)

Therefore, you can run the following SQL queries to manipulate the default settings for trackbacks/pingbacks.


UPDATE wp_posts SET ping_status = 'open'; (to enable pingback/trackback for all users)

UPDATE wp_posts SET ping_status = 'closed'; (to disable the same)

UPDATE wp_posts SET ping_status = 'closed' WHERE post_date < '2008-01-01' AND post_status = 'publish'; (change the date if you want to disable/enable pingback/trackback before a certain date)

In this case as well, you can run the query quite a few times each year to change the pingback/trackback settings.

Complete and streamlined comments management

If you know how to make SQL work perfectly, WordPress discussion management is as easy as falling off the log. Use the following ‘one-step’ queries to better manage comments on your blog.

For enabling/disabling all discussions

You need to specify the comment status (as above) and then specify the ping status as well. Example here below:

UPDATE wp_posts SET comment_status = 'open', ping_status = 'open' WHERE comment_status = 'closed' AND post_status = 'publish';

For enabling/disabling all discussions before a date

Specify the comment status and the ping status, and then specify the date. Here, an example for you.

UPDATE wp_posts SET comment_status = 'closed', ping_status = 'closed' WHERE post_date < '2008-01-01' AND post_status = 'publish';

SQL, if used properly, can be highly instrumental in WordPress Comment Management. Try SQL language for yourself and you would know why WordPress developers have so much love for this database language.

Share

Tags:

— vytautas

You may also like...