Remove database entries where post_date > expiration date

Question

I have a custom post time that has an expiration date. The expiration date is derived by getting the package_type pf the post (each package type has a different number of days before expiration, referred to as "Alive days"), extracting the value for alive_days, and adding that to the post_date.

I'm wondering if there's a way to remove all the posts where the current date exceeds the expiration date.

$post_id = $post->ID; // get the post ID
$package_id = get_post_meta( $post_id, 'package_select', true ); // get the post's package
$transaction_price_pkg = $monetization->templ_get_price_info( $package_id, '' ); // get the package info
$alive_days = "+" . $transaction_price_pkg[0]['alive_days'] . " days"; // get the "alive days" of the given package

$expired_date = date('Y-m-d H:i:s',strtotime($alive_days, strtotime($post->post_date))); // calculate the expiration date

// Here's where I'm having trouble
$query = $wpdb->get_results("SELECT * FROM {$wpdb->prefix}transactions WHERE {$expired_date >= NOW() ");

A simple var_dump returns NULL. Why doesn't this code work? (there can be multiple reasons...)

Answer 1

In your first sentence i'm assuming you mean, you have a Custom Post Type (not time).

So the post has a package connected, the package has a number of days alive setting. Reading your example code, i think you're approaching this from the wrong angle.

I would do something like this:

// I don't know how you want to call this function, i would use a daily cron-job
function remove_expired_posts() {

  $current_date = new DateTime();

  $args = array(
    'posts_per_page'   => -1,
    'post_type'        => 'YOUR_CUSTOM_POST_TYPE_NAME_HERE',
    'post_status'      => 'publish',
  );
  $posts = get_posts( $args );

  if($posts) {
    foreach($posts as $post) {

      $post_id = $post->ID;
      $post_date = DateTime::createFromFormat('Y-m-d H:i:s', $post->post_date');
      $package_id = get_post_meta( $post_id, 'package_select', true );
      $transaction_price_pkg = $monetization->templ_get_price_info( $package_id, '' ); // make sure you include the $monetization object
      $alive_days = (int)$transaction_price_pkg[0]['alive_days']; 

      $expire_date = $post_date->modify('+ '.$alive_days.' days');

      if($current_date > $expire_date) {
        wp_delete_post( $post_id, true ); // true means bypass bin.
      }
    }
  }
}

NOTE:
I haven't tested this code, but it should work. At least it should give you an idea how to go forward with this.

Regard, Bjorn