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