The below query will help you identify the top 7 posts having the most comments that have been flagged as spam.
SELECT comment_post_ID, COUNT(*) as spamcnt FROM wp_comments WHERE comment_approved = 'spam' GROUP BY comment_post_ID ORDER BY spamcnt DESC LIMIT 0, 7
The below query will help you identify the top 7 posts having the most comments.
SELECT comment_post_ID, COUNT(*) as commentcnt FROM wp_comments GROUP BY comment_post_ID ORDER BY commentcnt DESC LIMIT 0, 7
In one fell swoop you can disable trackbacks on all posts by running this SQL statement:
UPDATE wp_posts SET ping_status = 'closed';
The below SQL statement will force users to register in order to be able to comment. It could be extended with conditions, such as only implementing this on posts newer than a certain date or in a particular category only.
UPDATE wp_posts SET comment_status = 'registered_only';
You can use a text replace SQL update statement to erase nofollow markup in post content easy enough. However, keep in mind that if you’ve been writing about SEO or online marketing topics, you could easily be erasing words in your text that you’d like to keep present!
The below statement attempt to avoid this by looking for a lack of spacing before or after the text nofollow, such as one would expect in link markup:
UPDATE wp_posts SET post_content = REPLACE ( post_content, 'nofollow ', '' ); UPDATE wp_posts SET post_content = REPLACE ( post_content, ' nofollow', '' ); UPDATE wp_posts SET post_content = REPLACE ( post_content, ' rel="nofollow"', '' );
Removing text can come in handy when you’ve got spam, competitor mentions, bad words or unwanted text, etc. Sometimes it’s good enough to just remove the text string in question.
UPDATE wp_posts SET post_content = REPLACE ( post_content, 'target text to be removed', '' );
If you compose in the WordPress editor, and make a lot of changes as you go, WordPress will store revision history in the database, so you can easily roll-back to an earlier point in time. This is great if you foobar something or change your mind, but it comes at a price - database size.
Post revisions do get cleaned up automatically, but typically only after 30 or so days. That means, if you’re a fairly prolific author, there can be a load of revisions sitting in the database taking up space.
Revisions can be quickly cleaned with:
DELETE FROM wp_posts WHERE post_type = "revision";
The only problem with cleaning revisions alone is that there’s a lot more to a revision than just an entry in wp_posts. In fact, wp_term_relationships and wp_postmeta have a lot of accompanying information that could get orphaned if we just deleted revisions from wp_posts.
So, the preferred method would be to clean them all
First the posts that are revisions:
DELETE FROM wp_posts WHERE post_type = "revision";
Then the postmeta:
DELETE FROM wp_postmeta WHERE post_id NOT IN (SELECT ID FROM wp_posts);
DELETE FROM wp_term_relationships WHERE object_id NOT IN (SELECT ID FROM wp_posts);
While it’s great to blank out text, like an eraser, there are occasions where you might instead want to replace the text with something different. Maybe you’ve got newer posts or updated information you’d like to swap in. There are many situations that this ability to replace text can come in handy. The SQL isn’t too bad either:
UPDATE wp_posts SET post_content = REPLACE ( post_content, 'target text to be replaced', 'the new text doing the replacing' );
If you’ve switched themes or made other changes that mean you’ve got some shortcodes floating about that you’d like to remove, specific shortcodes can be eliminated with a SQL one-liner:
UPDATE wp_post SET post_content = REPLACE(post_content, '[oldshortcode]', '' ) ;
The below SQL statement assumes your category ID is 123.
SELECT * FROM wp_posts JOIN wp_term_relationships ON (wp_posts.ID = wp_term_relationships.object_id) WHERE wp_term_relationships.term_taxonomy_id = 123;
When you have a change of editorial staff, have an employee leave, or otherwise want to reassign ownership of posts from one author to another, it can be done via SQL:
UPDATE wp_posts SET post_author = 'id-of-new-authorowner' WHERE post_author = 'id-of-old-authorowner';
Spam can be a nasty thing, particularly when it arrives in bulk. For situations like that, the ability to find, and remove, comments in chunks, rather than one at a time, can be a lifesaver.
When you’ve noticed one, or many, different comment authors using a particular URL or domain in their posts - an old, spammy, SEO technique, you can use the below SQL delete statement to remove them all at once. Be sure to replace the text inside the % % with the offending domain, or full URL. Sometimes it’s best to do a select statement to find the size of returned results before actually running a delete statement.
SELECT * FROM wp_comments WHERE comment_author LIKE ‘%offendingurlordomain%’;
DELETE FROM wp_comments WHERE comment_author like ‘%offendingurlordomain%’;
In wp_posts, post_status can be set to any of these:
In wp_posts, comment_status should be “open” when comments are allowed or “closed” when they aren’t.
Ever click something, wait, think nothing happened, and you click it again?
Theoretically WordPress should have duplicate comments, unless someone submits the same thing multiple times. While that may sound a bit far fetched, the case of short comments becomes the litmus test. One word comments like, “Thanks” will get removed, leaving just one Thanks comment behind. Two word, three word - you can see how duplicate comment content can easily happen - because there’s just not enough comment content to make it unique.
However, if you’re wanting to clean up duplicate comments, this SQL statement can do it.
DELETE FROM wp_comments USING wp_comments, wp_comments AS commcompar WHERE (NOT wp_comments.ID=commcompar.ID) AND (wp_comments.comment_content=commcompar.comment_content);