2

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');
1

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.

  • No, not the Transients API. Use a wp_cron() – Ralf912 Apr 24 '13 at 8:31
  • 1
    Yeah, cron is a better alternative – RRikesh Apr 24 '13 at 8:32
  • wp_cron() might not be the best option as it is executed only when a visitor open the website. Standard cron jobs will be a better option here which is set to be executed once a day to update all posts expired. – Ghazanfar Mir Dec 20 '13 at 11:55
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.