WP Tweak..Pulling a Field from the DB

Drover

New member
Nov 28, 2006
369
1
0
Houston, TX
I'm using a wordpress custom theme that has this piece of code to prune posts after a number of days (prun_period).

I'm trying to change this to instead prune posts on a certain date. I've created a custom meta field for the expiration date called 'e_date'.

The original code works as intended and looks like this:

Code:
if (get_option("post_prun") == "yes" && get_option("prun_period") != "" && get_option("post_prun") != "") {

    $prun_period = get_option("prun_period");

    $sql = "SELECT `ID` FROM $wpdb->posts WHERE `post_date`<'".date('Y-m-d h:i:s', strtotime("-$prun_period days"))."' AND `post_status`='publish' AND `post_type`='post' LIMIT 10";

    $sql = mysql_query($sql);

    while ($row=mysql_fetch_array($sql)){

        $post_id = (int)$row['ID'];



        if (get_option("prun_status") == "1") {

            $my_post = array();

            $my_post['ID'] = $post_id;

            $my_post['post_status'] = 'draft';

            wp_update_post( $my_post );

        } else if (get_option("prun_status") == "2") {

            wp_delete_post($post_id);

        }

    }

}

?>
I thought I might be able to just change post_date to e_date and then either delete the part where it subtracts the prun_period or else set it to zero, but it gives me an error.

What I'm asking is if someone can tell me how to tweak that original code to prune on the e_date instead of after prun_period. It looks like it should be simple, but I don't know how to do it... :(

Thanks in advance.
 


Code:
$prun_exp_time = get_option("prun_exp_time");

// If you want to use the current time then do this:
//$prun_exp_time = date('Y-m-d H:i:s', time());

$sql = "SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
WHERE `meta_key`='e_date'
AND `meta_value` != ''
AND '$prun_exp_time' > `meta_value` AND `post_status`='publish' AND `post_type`='post' LIMIT 10";

Not sure why the "LIMIT 10" is there but I left it in.
 
logicflux,

I think I understand what you did here, but where is 'prun_exp_time' defined? How will it know what to do with that?

another quick question, exactly which part of the original code should I be replacing with what you posted? Is that just the one line:

Code:
$sql = "SELECT `ID` FROM $wpdb->posts WHERE `post_date`<'".date('Y-m-d h:i:s', strtotime("-$prun_period days"))."' AND `post_status`='publish' AND `post_type`='post' LIMIT 10";

Thanks for the help! Much appreciated!
 
radio,

Thanks. I hadn't seen that one. This code is kind of embedded in a custom theme so I don't know how it would work but I might be able to pull code from that if LogicFlux's fix doesn't work.
 
I replaced this:

Code:
$sql = "SELECT `ID` FROM $wpdb->posts WHERE `post_date`<'".date('Y-m-d h:i:s', strtotime("-$prun_period days"))."' AND `post_status`='publish' AND `post_type`='post' LIMIT 10";
with this:

Code:
$prun_exp_time = date('Y-m-d H:i:s', time());

$sql = "SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
WHERE `meta_key`='e_date'
AND `meta_value` != ''
AND '$prun_exp_time' > `meta_value` AND `post_status`='publish' AND `post_type`='post' LIMIT 10";
but all the posts were just immediately deleted. :(

Any idea what's happening?
 
I replaced this:

Code:
$sql = "SELECT `ID` FROM $wpdb->posts WHERE `post_date`<'".date('Y-m-d h:i:s', strtotime("-$prun_period days"))."' AND `post_status`='publish' AND `post_type`='post' LIMIT 10";
with this:

Code:
$prun_exp_time = date('Y-m-d H:i:s', time());

$sql = "SELECT * FROM $wpdb->posts
LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
WHERE `meta_key`='e_date'
AND `meta_value` != ''
AND '$prun_exp_time' > `meta_value` AND `post_status`='publish' AND `post_type`='post' LIMIT 10";
but all the posts were just immediately deleted. :(

Any idea what's happening?

I hope you backed up.

$prun_exp_time = date('Y-m-d H:i:s', time());

That creates a string formatted representation of the current time. So anything with an e_date older than the current time will be flagged as a draft or be deleted, depending on what get_option("prun_status") is set to.

e_date has to be in this format "2009-08-18 18:11:11".

How does this run? On cron job, manually? Does the e_date field already exist? If so what format are the dates in?

Actually, this query string is probbaly more correct:

Code:
  $sql = "SELECT * FROM $wpdb->posts 
  LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
  WHERE `meta_key`='e_date' 
  AND `meta_value` != '' 
  AND UNIX_TIMESTAMP('$prun_exp_time') > UNIX_TIMESTAMP(`meta_value`) 
  AND `post_status`='publish' AND `post_type`='post' LIMIT 10";
 
If you just want to use the current time as your cutoff period the you can use this:

Code:
  $sql = "SELECT * FROM $wpdb->posts 
  LEFT JOIN $wpdb->postmeta ON($wpdb->posts.ID = $wpdb->postmeta.post_id)
  WHERE `meta_key`='e_date' 
  AND `meta_value` != '' 
  AND UNIX_TIMESTAMP(NOW()) > UNIX_TIMESTAMP(`meta_value`) 
  AND `post_status`='publish' AND `post_type`='post' LIMIT 10";
 
I think I figured out the issue. e_date was m/d/Y. Someone else explained to me whay using Y-m-d makes things so much easier because you don't have to actually convert to a real date format. The math can just be done as a string.

It seems to be working now. Thanks very much. Huge help!