Need MySQL Queries to delete WordPress Posts and Post Meta more than X Days Old

Question

I run a blog where the community can post time-sensitive community links (sports scores and such). After a certain time, those posts are no longer useful, so I want to delete them in batch via a MySQL query, but I don't know how. I imagine that getting rid of those posts entirely is more than just deleting from the wp_posts table, right? There other tables at work per post, aren't there?

I've tried a couple of auto- or batch-delete plugins, but they don't work half the time.

Please, could you provide a MySQL query to delete posts and their pieces older than X days from all relevant tables?

Thank you in advance.

--Nick

Answer 1

Have you tried using the Auto Delete Posts plugin?

Answer 2

How about, instead of deleting posts, you'll just flag them as trashed, or drafts? You'll achieve the same end result, but don't run the risk of corrupting your database.

It will, of course, in the long run make your database very large, but this would seem like a simpler and safer approach to me. As a bonus no information would be lost either.

Edit.

Another aproach could be moving the posts to a separate category, but if you want to do like Demolition man, that's fine too :-)

1) Find all eligible posts and their attachments

SELECT wp_posts.ID, att.ID
FROM wp_posts 
LEFT JOIN wp_posts att ON att.post_parent = wp_posts.ID
WHERE wp.posts.post_date < EXPIRATION_DATE;

2) Find all related comments

SELECT wp_comments.comment_ID 
FROM wp_comments 
WHERE comment_post_ID IN ( <post ID:s selected in 1)> )

3) Delete all meta related to posts and comments, then comments, then posts

DELETE FROM wp_commentmeta WHERE comment_id IN ( <comment ID:s selected in 2)> )
DELETE FROM wp_postmeta WHERE post_id IN ( <post ID:s selected in 1)> )
DELETE FROM wp_term_relationships WHERE object_id IN ( <post ID:s selected in 1)> )
DELETE FROM wp_comments WHERE comment_ID IN ( <comment ID:s selected in 2)> )
DELETE FROM wp_posts WHERE ID IN ( <post ID:s selected in 1)> )

Yes, some of these could be combined, but I like to keep things as simple as possible.

The tables and column names are taken from my WP 3.0-alpha. You might want to check the Database description just to make sure you get it all.