Batch Scanning and Deleting Empty WordPress Posts with WP-CLI

by

I get to do a lot of fun projects with my awesome clients on Codeable. This post was inspired by a client that wanted to delete a lot of WordPress posts with an empty title and content. I have no idea how they got there and neither did the site owner – it could have been a failed import of some kind or plugin that went nuts. Nevertheless they needed to be cleaned and this is a perfect job for my favorite WordPress tool: WP-CLI :). I have split the post into an audit and deletion section.

Auditing Empty WordPress Post Titles and Content Combinations

We are going to approach this methodically by doing some MySQL queries with WP-CLI’s wp db query command looking for empty post titles, content and even custom post types.

Empty Post Title

First we are going to find all of the posts with an empty title.

wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_title=''" --allow-root

So many were output I decided to count them by piping the output to wc and counting the lines -l

wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_title=''" --allow-root | wc -l

Almost 5000! Next we will check for just posts with empty content

4902

Empty Post Content

Now we can look for posts with empty content

wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts where post_content=''" --allow-root

We can count the posts with empty content

wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_content=''" --allow-root | wc -l

Almost 5000 again! Let’s finally check to see if these are the same posts that also have an empty title

4902

Empty Post Title and Content

Finally let’s check the posts with both empty titles and content

wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_content='' AND post_title=''" --allow-root

Let us count those too

wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_content='' AND post_title=''" --allow-root | wc -l

Almost 5000, looks like a pattern 😉

4902

That confirms it! Now we can delete these with WP-CLI

Empty Post Title and Content for Custom Post Types

We can do the same thing for Custom Post Types by adding a post_type parameter, here we are looking for WooCommerce products so the post_type column has been set to product

wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_type='product' AND post_content='' AND post_title=''" --allow-root

Let’s count these with wc and specifically the number of lines -l

wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_type='product' AND post_content='' AND post_title=''" --allow-root | wc -l

Now it is time to delete the empty posts!

Deleting Empty WordPress Post Titles and Content Combinations

Back up the database first before you actually delete anything!

wp db export before-empty-post-clean.sql --all-tablespaces --allow-root

Empty Post Title

This WP-CLI command deletes the posts with empty titles and their associated postmeta, the --force flag deletes them permanently by skipping the trash. The tr command is used to replace any new lines with spaces to keep the formatting that WP-CLI requires.

wp post delete $(wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_title=''" --allow-root | tr '\r\n' ' ') --force --allow-root

Empty Post Content

Here is a similar command but only targets the deletion of posts with empty post content

wp post delete $(wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_content=''" --allow-root | tr '\r\n' ' ') --force --allow-root

Empty Post Title and Content

Finally this command will delete the posts that have an empty title and content

wp post delete $(wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_content='' AND post_title=''" --allow-root | tr '\r\n' ' ') --force --allow-root

This will not delete associated postmeta but is faster since it is interacting with the database directly, could leave orphaned postmeta data leftover!

wp db query "DELETE FROM $(wp db prefix --allow-root)posts WHERE ID IN '($(wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_content='' AND post_title=''" --allow-root | tr '\r\n' ','))'"

Empty Custom Post Types

Last example is for deleting those empty WooCommerce products

wp post delete $(wp db query "SELECT ID FROM $(wp db prefix --allow-root)posts WHERE post_type='product' AND post_content=''" --allow-root | tr '\r\n' ' ') --force --allow-root

Sources

WP-CLI post delete command
Replacing newline with space using tr
Delete from MySQL using subquery as condition