Code to auto expire posts after 30 days

Question

I've tried the plugins that are available in the Wordpress plugins section but they seem to fail miserably. Initially, I used Auto Post Expire By User Role which worked for a time, but for some reason now it doesn't.

What I want is to add a code in functions.php that will automatically set as drafts posts which are 30 days older than their published date.

I have the following code but don't know if this is the one that I should be using. Is this correct and if not, what is the correct form?

function expire_posts() {
global $wpdb;
$daystogo = "30";
$sql = "UPDATE wp_posts SET `post_status` = 'draft' WHERE `post_type` = 'post' AND DATEDIFF(NOW(), `post_date`) > '$daystogo')";
}

add_action('wp_head', 'expire_posts');

Answer 1

There are some issues with your query

$daystogo = "30";

$sql =
"UPDATE {$wpdb->posts}
SET post_status = 'draft'
WHERE (post_type = 'post' AND post_status = 'publish')
AND DATEDIFF(NOW(), post_date) > %d";

$wpdb->query( $wpdb->prepare( $sql, $daystogo ) );

You do not want to untrash trashed posts by declaring them as draft, right? And you really do not want to make every automated saved version (post_status = inherit) a draft. So only select published posts.

Use $wpdb-prepare() for EVERY query.

(Edit: Every query that has variable inputs, that is. Don't use prepare for entirely static queries that have no variable inputs.)

Use the WPDB class variables instead of the plain table name $wpdb->posts instead of wp_posts. See the Codex.

Test your query first before code them. Use something like an MySQL frontend or MySQL-Admin. In your sql-query, there is a ) at the end where no one should be.

Answer 2

You just declared the query, but you didn't execute it!

function expire_posts() {
  global $wpdb;
  $daystogo = "30";
  $sql = "UPDATE wp_posts SET `post_status` = 'draft' WHERE `post_type` = 'post' AND DATEDIFF(NOW(), `post_date`) > '$daystogo')";
  $wpdb->query( $sql );
}

Don't forget to always prepare your queries.

It would also be a good idea to use the wp_cron() to run the code at regular time inverval(eg once a day). When hooking to wp_head, the query will run each time a user visits a page.