Mass delete posts in WordPress 2.3.x
Note: in WordPress 2.5.x the mass delete post button has been added. You no longer need to clean it out manually or use plugins.
Please use this tutorial first only with SELECT statements and backup your entire database before you start deleting!!!
WordPress table architecture has undergone a major change from version 2.3 upwards. WordPress does now support “tags” next to categories. This has been done to make assigning ‘terms’ to posts more flexible. The key to understanding manual post deletion, is understanding the underlying table structure. In WordPress 2.2.x the two tables that saved data for posts were: wp_posts, wp_post2cat. Additionally in the table wp_categories the post count for categories was maintained. We will see that despite the differences appear big, a query can still be constructed just as easily as before.
In 2.3.x. the following happens when a post is made:
A new term is added to the wp_terms table if it does not already exist. The tag is added to the wp_term_taxonomy table to identify the type. The type will be identified through three values: category, link_category or post_tag. The number of times a tag or category is used in posting is also counted in the wp_term_taxonomy table. The postId from wp_posts is used in wp_term_relationships to relate the post with wp_term_taxonomy.
Finding the posts you wish to delete
Shows the posts between 1st of december 2005 and 1st of january 2006.
|
1 |
SELECT * FROM wp_posts WHERE post_date > ‘2005-12-01 00:00:00′ AND post_date < ‘2006-01-01 00:00:00′ |
Besides post_type, there is a field called ‘post_status’. You can use post_status to narrow down the results further. Post_status values are: ‘publish’,'draft’,'private’,'static’,'object’,'attachment’,'inherit’,'future’,'pending’. Mostly you won’t need these. From the looks of it, they will no longer be used. When you are on the results page, copy the query that shows you the post ID’s. It’s important you have this for future use.
Counting the posts in the same date range (optional)
|
1 |
SELECT COUNT( * ) FROM wp_posts WHERE post_date > ‘2005-12-01 00:00:00′ AND post_date < ‘2006-01-01 00:00:00′ AND post_type = ‘post’ AND post_status <> ‘attachment’ OR post_status <> ‘static’ OR post_status <> ’object’ |
The reason why we are excluding post_status values here is that WordPress saves a whole lot more in the table wp_posts than post data only if you are pre 2.3! The above are values you are likely to want to exclude during a cleanup. This will give you an idea about how many posts you are deleting in the chosen period.
Examining other tables
With the help of PHPMyAdmin you can now export this selection. You will get a SQL string you can use to examine other tables for the same post_id with small modifications. Use search and replace to match the field names. Example: search for post_id and replace by object_id. This is how the same result is applied to the wp_term_relationships table:
|
1 |
SELECT * FROM wp_term_relationships WHERE (<code>object_id</code> =320) OR (<code>object_id</code> =156) OR (<code>object_id</code> =321) OR (<code>object_id</code> =274) |
Use the same method to examine wp_comments to remove any comments to posts you selected, as well as wp_postmeta.
Updating post count (optional)
Lastly you will need to update the count in the wp_term_taxonomy table to have the correct post amounts per term. The reason this is optional is that WordPress simply corrects the post counts itself everytime there is post connected to another term. It depends on how much you insist on having it perfect first go. The correct way to do this is by using an UPDATE tablename SET count (SUBQUERY) but I have had no luck in finding that one out. I did however manage to create a view which shows you the terms used in the respective period with the term_id and term_taxonomy_id. The count shows the number of times it’s used within the selected period.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
SELECT wp_term_relationships.term_taxonomy_id, wp_terms.term_id, wp_terms.name, COUNT( wp_posts.ID ) AS num
FROM (
(
wp_posts
INNER JOIN wp_term_relationships ON ID = object_id
)
INNER JOIN wp_term_taxonomy ON wp_term_taxonomy.term_taxonomy_id = wp_term_relationships.term_taxonomy_id
)
INNER JOIN wp_terms ON wp_term_taxonomy.term_id = wp_terms.term_id
WHERE post_date > '2007-01-01 00:00:00'
AND post_date < '2007-01-31 00:00:00'
AND post_type = 'post'
AND wp_term_taxonomy.taxonomy = 'post_tag'
GROUP BY wp_terms.term_id
LIMIT 0 , 296 |
If you open a post and save it for categories and tags WordPress will auto-update the number so you won’t be left with non-existing tags or wrong category count numbers. This is basically the most labour intensive, but will still cost you less time than deleting all entries manually from the WordPress backend.
Keeping clean
The last thing to do is cleanup the actual file attachments and images you uploaded during the specified date range. In WordPress 2.x versions this has been majorly improved. All files are now placed in a year and then month directory in wp-contents. Careful now, if you preserved pages and have uploaded file attachments for them (images, etc) you are now at risk of deleting those! Tip: NEVER use the WordPress upload function on pages!! Link attachments and images manually. This is yet another improvement to be done by the WordPress developers. Download your attachments and re-instate them manually.
Now you’re done!
