How to mass Delete Pending Posts in WordPress

Written by: Muninder

Updated: December, 30, 2011

As we all know WordPress is the best and popular-most option when it comes to writing/publishing posts and more importantly managing a blog efficiently.
Very often you’ll find blogs opening up to guest posting (like even we have done) in order to allow other bloggers to showcase their writing skills and ultimately share their knowledge with your readers.
But the danger in opening up registrations for guest bloggers attracts another community present on the Internet named as spammers. Unless you have proper editorial rules in place these spam bots will over-load your WordPress database with useless posts (most of them containing only links or adult content).
So you have two options to choose from. The first one is to entirely close new registrations and accept guest posts via contact form or specific guest posting forms or even an email.
The second option is to use anti-spam and registration protection plugins (eg: Pie Register). At first we opted for this option, but surprisingly even after setting up the email verification process we witnessed a huge number of spam or low quality posts.
Finally we moved back to the first option of closing registrations and started accepting guest posts via the contact form. Now one would ask what we should do with the pending posts submitted by the spam bots ?

MySQL query to delete pending posts

SQL Query Window
SQL Query Window

The best option was to clean-it up via a plugin or an SQL query as there were some 3500+ of such posts. Since I’m a bit more comfortable at running direct sql queries I did that to clean-up the pending posts on a single mouse click.
For doing so, just open up your hosting panel and navigate to the phpMyAdmin icon. Open it and select your WordPress database. Now you’ll find an icon which says ‘Query Window’ beneath the phpMyAdmin logo. Open it up and run the code given below.
DELETE FROM wp_posts WHERE post_date < '2010-01-01 17:00:00' AND post_status = 'pending'
This code instructs the table wp_posts to delete the pending posts right from the 1st of January, 2010. Remember the value we pass to the parameter post_status is the most vital thing in this SQL query. So better copy-paste the entire query rather than typing and making a mistake.

Use WP Mass Delete & Bulk Delete plugins

Delete Pending posts plugin
Pics of Bulk Delete & WP Mass Delete

If you are not accustomed to run the queries in MySQL then better use plugins like WP Mass Delete and Bulk Delete which offer enhanced and specific options to facilitate the cause. There are a lot of similar plugins in the WordPress plugins repository but these two looked fine at the out-set.
WP Mass Delete offers much more specific options (like setting the period of the posts to be deleted) as compared to Bulk Delete. So if I were to use a plugin I would go with Mass delete for the simple reason that it offers exactly what I need.
But one very good option I found in the Bulk Delete plugin is the script timeout one. This option comes handy when you’ve say 1000+ posts to be deleted. Sometimes if your webhost is slow then the database may timeout while executing your command.
Sp. thanks to Abhishek for the security tip. It was a life-saver tip of blurring out my database name and host :D.
So which one of the above methods do you use to server this purpose of deleting pending posts ?

by