Tweaking Some Code That Pulls a Date from the DB

  • Resolved Drover

    (@drover)


    15 years, 3 months ago

    I’m using a 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:

    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” days.

    Thanks in advance.

Viewing 8 replies - 1 through 8 (of 8 total)

  • You need a php programmer for that tweak.Ask MaxBlog.

    Meanwhile check out the following:

    https://www.baboon.co.in has a free download ,called ptm6.4.0 , that you simply upload to the server and then use your browser to access the index file .From there you can do all kinds of things with your database.At first it sounds hard but it isn’t.True ,it is not automatic to start ,but at least this method is not likely to mess up as long as you get regular database backups.

    Thread Starter Drover

    (@drover)

    You need a php programmer for that tweak.Ask MaxBlog.

    Hmmmm….can you explain why? I mean what is it that complicates this exactly? It would seem to be pretty simple.

    The problem you have is that your e_date custom field will be saved as a string – not as a date. Which makes it difficult to run a date comparison via MySql or PHP. That’s probably why the original code used a time period rather than a date.

    What format are you using for e_date? From the little playing I’ve done with dates in custom fields, yyyy-mm-dd is the way to go. Much easier to try and run a date comparison then.

    Thread Starter Drover

    (@drover)

    it’s m/d/Y right now. Is there a way I can format it as a date just by doing something like date(‘Y-m-d’,’e_date’) or something similar in that code above where I’m doing the comparison?

    Or does it have to actually be saved as one? How would I even do that?

    …and thanks for the help btw. Much appreciated.

    I can format it as a date just by doing something like date(‘Y-m-d’,’e_date’) or something similar

    In my experience, not without exploding your custom meta value string and then delving into mktime() – which is something I’ve worked hard to avoid. ??

    Simply saving a custom date as “2009-08-18” makes life so much easier.

    Thread Starter Drover

    (@drover)

    Simply saving a custom date as “2009-08-18” makes life so much easier.

    So you’re saying I should have them input the date that way? Why is that easier to work with? Is it because it will work like a string?

    Because, if you use strtime(), you should then be able to do a direct comparison against the post date (which is stored in the format ‘yyyy-mm-dd hh:ii:ss’ in wp_posts).

    You can also grab a list of posts in either ascending (or descending) e_date order without having to mess about with date(), time() or mktime():

    $args = array(
	'meta_key' => 'e_date',
	'orderby' => 'start_date',
	'order' => 'ASC'
);
$prune_list = get_posts($args);

    strtotime() will convert your date into a UNIX timestamp, which is very good for doing date math but its also about as cryptic as you can get. You can convert the timestamp back to something readable using date()– date(‘Y-m-d’,$e_date), for example. This is actually the kind of date conversion used in the original working code you posted.

    ‘YYYY-MM-DD’ is the format used by MySQL’s date column. It also works in a datetime column. I’m not sure if that is what esmi is thinking about but that fact does make it a convenient format at times.

    Since you are using a meta field– I assume you mean you are using a meta field stored in the usermeta table and not that you’ve edited wp_posts to add a column– you’ll have to work out a join on the usermeta table and then add a where clause that reads something like ‘AND <your_expiration_date> > NOW()‘. If I’m understanding you right…

Viewing 8 replies - 1 through 8 (of 8 total)
  • The topic ‘Tweaking Some Code That Pulls a Date from the DB’ is closed to new replies.