Support » Plugin: Custom Content Type Manager » Problem with custom date field when using it in custom query

  • Hi,
    Using CCTM I created Custom Post Type (CPT) “coupon” and attached to it few Custom Fields (CF). For example I created CF Date named “exp_date” where I put expiration date for specific coupon.
    I’ve also chosen “MySQL yyyy-mm-dd” as Date Format (this is PL website)

    My goal was to create custom Loop for displaying only those coupons that are going to be ending in next few days, showing only those that their exp_date is today, tomorrow, and so on, and here is custom query for that loop:

    $time_difference = get_settings('gmt_offset');
    $now = gmdate("Y-m-d",(time()+($time_difference*3600)));
     $querydetails = "
       SELECT wposts.*
       FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
       WHERE wposts.ID = wpostmeta.post_id
       AND wpostmeta.meta_key = 'exp_date'
       AND wpostmeta.meta_value >= $now
       AND wposts.post_status = 'publish'
       AND wposts.post_type = 'coupon'
     $pageposts = $wpdb->get_results($querydetails, OBJECT)

    The result of above query is:
    it’s showing all coupons that have something in “exp_date” field, no matter of ending date.
    I was also trying to change $now variable to:
    $now = date("Y-m-d");
    or even:

    $thenow = date_i18n('Y-m-d', time());
    $now = strtotime($thenow);

    but the resutls are the same.

    Question: what is wrong?

    I’m using data from this field in another loop to show which coupons has eneded is some IF variations and I’m using there something like this:

    $theexpdate = get_custom_field('exp_date');
    $theexpdate2 = strtotime($theexpdate);
    $datediff = floor(($theexpdate2 - $thenow2)/(60*60*24));

    and comparing current date to date stored in exp_date field is working there. Just don’t know how to compare date within the query.

    Would be really gratefull for your help. My PHP knowlege is not that good so I think I can’t do it on my own 🙁


Viewing 1 replies (of 1 total)
  • Plugin Contributor fireproofsocks


    I think the problem is your SQL query: you’re not being verbose in your join and it looks like you botched your table prefixes(?). Here’s my example with hard-coded prefixes:

    SELECT *
    FROM wp_posts JOIN wp_postmeta ON wp_posts.ID = wp_postmeta.post_id

    So that doesn’t look like it has anything to do with the CCTM per se. Although you could also use the GetPostsQuery class’ get_posts() method for querying like this. It’s meant to be flexible like that. See the wiki for some info/examples.

Viewing 1 replies (of 1 total)
  • The topic ‘Problem with custom date field when using it in custom query’ is closed to new replies.