KNOWNHOST KNOWLEDGE BASE

Hosting Question? Find the Solution - Browse our Guides, Articles, and How-To's

How To Manage WordPress Posts Using SQL

Category: WordPress
Tags: # # # # #

How to Find WordPress Posts with the Most Spam Comments using SQL?

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

How to Find WordPress Posts with the Most Comments using SQL?

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

How to Turn Off Trackbacks on All WordPress Posts using SQL?

In one fell swoop you can disable trackbacks on all posts by running this SQL statement:

  UPDATE wp_posts SET ping_status = 'closed';

How to Set All Posts to Accept Comments Only from Registered Users using SQL?

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"', '' );

How to Remove Any Text String in WordPress Posts using SQL?

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', '' );

How to Remove Post Revisions from WordPress using SQL?

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);

Finally term_relationships:

  DELETE FROM wp_term_relationships 
  WHERE object_id NOT IN
    (SELECT ID FROM wp_posts);

How to Replace Any Text String in WordPress Posts using SQL?

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' );

How to Clean Up Posts by Removing Specific Shortcodes in WordPress using SQL?

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]', '' ) ;

How to Find All WordPress Posts via Category ID?

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;

How to Change WordPress Post Author and Change Ownership?

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';

How to Delete WordPress Comments Based on Author URL using SQL?

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%’;

Then

  DELETE FROM wp_comments 
  WHERE comment_author like ‘%offendingurlordomain%’;

What are the Default Post Statuses in WordPress?

In wp_posts, post_status can be set to any of these:

  • Publish
  • Future
  • Draft
  • Pending
  • Private
  • Trash
  • Auto-Draft
  • Inherit

What Post Comment Status Should be Used When Turning Comments On or Off?

In wp_posts, comment_status should be “open” when comments are allowed or “closed” when they aren’t.

How to Remove Duplicate Comments in WordPress?

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);

If you want to learn about SQL you can read on MySQL documentation page.

Related Articles: