Comparing meta_value when it's dates

  • Remi Corson

    (@corsonr)


    Automattic Happiness Engineer

    14 years, 2 months ago

    Hi guys,

    i’m doing a booking system for a small hotel and i have a problem in a function. This function aims to return the price indicator depending on the date. This indicator is “low” or “high”.

    The client can create many periods, for example beginning of december, end of december, christmas weekend etc…. and determines which price indicator is linked the period.

    For example christmas weekend will be a high price, and begiinig of december will be low price.

    Each room type of the hotel has a price depending on the period.

    function get_price_indicator( $date )
    {
    global $wpdb;

    $exploded_date = explode(‘-‘, $date); // dd/mm/yyyy
    $date = mktime(0,0,0, $exploded_date[1], $exploded_date[0], $exploded_date[2]);

    query_posts(‘post_type=periods
    &post_status=publish
    &meta_key=date_begins
    &meta_compare=<=
    &meta_value=’.$date.’
    &meta_key=date_ends
    &meta_compare=>=
    &meta_value=’.$date.’
    &orderby=meta_value_num’);

    if (have_posts()) :
    while (have_posts()) : the_post();
    return “”.get_post_meta(get_the_ID(), ‘price_indicator’, true).””;
    endwhile;
    else :
    return “low”;
    endif;
    }

    The function tries to fin a perdiod in which the date is making part of. The matter is that the meta_value field are formated with the mktime() function and is stored as a text field in mysql by wordpress not in a INT format so i cannot compare them.

    Anyone could help me on comparing the comparison of these dates? The aim is to return “low” or “high” if $date is making part of a period beginning at date_starts and ending at date_ends.

    many thanks !

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

  • If you store the dates in the meta_value in the format ‘yyyy/mm/dd’, you can compare them directly to a string in that format.

    Thread Starter Remi Corson

    (@corsonr)

    Automattic Happiness Engineer

    Many thanks vtxyzzy.

    i did that but it still not working, it seems that the probleme is not coming from there.

    OK, I don’t think you can use more than one meta_key/value compare in a query. You will need to code your own sql query. I will give it a try here, but be warned, I am prone to typos! I assume that $date is yyyy-mm-dd and that the meta_values are in the same format.

    $sql = "SELECT p.* FROM $wpdb->posts p
JOIN $wpdb->postmeta pm1 ON (p.ID = pm1.post_id)
JOIN $wpdb->postmeta pm2 ON (p.ID = pm2.post_id)
WHERE p.post_type = 'periods'
AND p.post_status = 'publish'
AND pm1.meta_key = 'date_begins'
AND pm1.meta_value <= '$date'
AND pm2.meta_key = 'date_ends'
AND pm2.meta_value >= '$date'";

    That should give all posts with the correct meta_values.

    Thread Starter Remi Corson

    (@corsonr)

    Automattic Happiness Engineer

    Many thanks !

    i saw that that i cannot compare 2 meta_key, so i tried this:

    query_posts('post_type=periodes
				&post_status=publish
				&posts_per_page=1
				&meta_key=date_endst&meta_compare=<=&meta_value='.$date.'
				&orderby=meta_value_num
				&order=DESC'

    and it’s working excepted when the format for days is 0x, it’s ok when it’s 1x or 2x or 3x

    Sorry, I don’t understand what you mean by a format of 0x, 1x, 2x, or 3x.

    Can you show examples?

    Thread Starter Remi Corson

    (@corsonr)

    Automattic Happiness Engineer

    for example it’s working for the days numbers between 10 and 31 and it’s not working for the numbers from 1 to 9, do you see what i mean ?

    OK – your $date is probably formatted as yyyy-m-d instead of yyyy-mm-dd. For example, you might have 2010-12-1 or 2010-5-22 instead of 2010-12-01 or 2010-05-22.

    Be sure that you format the month and day as two digits, with leading zeros if necessary.

    Thread Starter Remi Corson

    (@corsonr)

    Automattic Happiness Engineer

    Ash ! my $date is correctly formated and it’s still not working for days between 1 and 9

    Make sure the date in the meta_value is also formatted correctly.

    Also, I don’t know what the effect is, but the ‘orderby=meta_value_num’ is almost certainly not correct because you want to order by yyyy-mm-dd which is not numeric. ‘Try just orderby=meta_value’.

Viewing 9 replies - 1 through 9 (of 9 total)
  • The topic ‘Comparing meta_value when it's dates’ is closed to new replies.