+- Forums.Likebd.Com (http://forums.likebd.com)
+-- Forum: English Forums (http://forums.likebd.com/forumdisplay.php?fid=134)
+--- Forum: Coding (http://forums.likebd.com/forumdisplay.php?fid=136)
+---- Forum: Sql (http://forums.likebd.com/forumdisplay.php?fid=238)
+---- Thread: Wordpress site sql coment for easy to work (/showthread.php?tid=3320)
Wordpress site sql coment for easy to work - Hasan - 07-05-2018
Remove all spam comments from the database
This SQL query removes all comments marked as “spam” from the database:
DELETE FROM wp_comments WHERE wp_comments.comment_approved = 'spam';
Works great in all versions of WordPress including 3.0!
Disabling and enabling comments
In the WordPress database, the “wp_posts” table includes a column called “comment_status”, which may contain one of the following values for each row (i.e., post):
open (comments open to everyone)
closed (comments closed to everyone)
registered_only (comments open for registered/logged-in users)
Given this information, we may execute the following SQL queries (via phpMyAdmin or any other method of querying the database) to manipulate our discussion-management settings for comments:
Note: Remember to backup your database!
Globally enable comments for all users
UPDATE wp_posts SET comment_status = 'open';
Globally disable comments for all users
UPDATE wp_posts SET comment_status = 'closed';
Globally enable comments for registered users only
UPDATE wp_posts SET comment_status = 'registered_only';
Globally enable/disable comments before a certain date
For this query, specify the comment_status as either open, closed, or registered_only. Also, specify the date by editing the 2008-01-01 to match whatever date you want to target.
UPDATE wp_posts SET comment_status = 'closed' WHERE post_date < '2008-01-01' AND post_status = 'publish';
I run this query a few times each year (or as often as I can remember it) to disable comments on old posts. Ultimately, I will combine this query with a similar one (provided below) for pingbacks and trackbacks to manage discussion options with a single step.
Disabling and enabling Trackbacks & Pingbacks
Similar as before, the “wp_posts” table also includes a column called “ping_status”, which applies to both pingbacks and trackbacks, and may contain one of the following values for each row (i.e., post):
open (pingbacks/trackbacks open to everyone)
closed (pingbacks/trackbacks closed to everyone)
Given this information, we may execute the following SQL queries (via phpMyAdmin or any other method of querying the database) to manipulate our discussion-management settings for pingbacks and trackbacks:
Globally enable pingbacks/trackbacks for all users
UPDATE wp_posts SET ping_status = 'open';
Globally disable pingbacks/trackbacks for all users
UPDATE wp_posts SET ping_status = 'closed';
Globally enable/disable pingbacks/trackbacks before a certain date
For this query, specify the ping_status as either open or closed. Also, specify the date by editing the 2008-01-01 to suit your needs.
UPDATE wp_posts SET ping_status = 'closed' WHERE post_date < '2008-01-01' AND post_status = 'publish';
As before, this last query is one that I will be using a few times each year (or as often as I can remember it) to disable comments on old posts. Ultimately, I will combine this query with the comments query to produce the one-step discussion-management query provided below.
Complete, one-step discussion management
Given the queries described above, we may fashion the following “one-step” SQL queries, perfect for complete, plugin-free discussion management:
Globally enable/disable all discussion: comments, pingbacks and trackbacks
For this query, specify the comment_status as either open, closed, or registered_only. Also, specify the ping_status as either open or closed.
UPDATE wp_posts SET comment_status = 'open', ping_status = 'open' WHERE comment_status = 'closed' AND post_status = 'publish';
Globally enable/disable comments, pingbacks and trackbacks before a certain date
For this query, specify the comment_status as either open, closed, or registered_only. Also, specify the ping_status as either open or closed. Finally, specify the date by editing the 2008-01-01 to suit your needs.
UPDATE wp_posts SET comment_status = 'closed', ping_status = 'closed' WHERE post_date < '2008-01-01' AND post_status = 'publish';
That last query is perfect for manual control over the closing of both comments and xbacks for posts from a specific time period. Sure there are plugins for this, but there are also plugins that enable direct SQL commands, making it convenient for granular control over discussion intervals.