WordPress.org

Ready to get started?Download WordPress

Forums

Using Custom field to store date & retrieve the post based on criteria (7 posts)

  1. eons
    Member
    Posted 4 years ago #

    Hi wp gurus,

    In daily, there will be few posting done by me, and those posting is related to some events.
    So the event will have a start date & end date, which is a range of date, and i will store this information in the custom field.
    And I would like to present the event in different tab (page).

    Example of my posting:
    Start Date(yyyymmdd) End Date(yyyymmdd)
    Post 1 20100818 20100827
    Post 2 20100818 20100829
    Post 3 20100819 20100901

    So in my wordpress page, I will have 4 different menu tab.
    Tab 1 – Current Event
    Tab 2 - Event End Today
    Tab 3 - Tomorrow Event
    Tab 4 - Next week event (list all event fall on next week)
    Tab 5 - Upcoming Event (list all future events where event date > today)

    Appreciate your helps.

  2. chinmoy
    Member
    Posted 4 years ago #

    How to get the custom field value?

    <?php
    $start_date = get_post_meta($post->ID, "start_date", true);
    $end_date = get_post_meta($post->ID, "end_date", true);
    ?>
  3. eons
    Member
    Posted 3 years ago #

    Anybody can help?

  4. sunriseweb
    Member
    Posted 3 years ago #

    If I interpret your question properly I'm facing the same problem. If I store a dates in custom fields how do I query on these fields in a useful way?

    For example if there are two custom fields "StartDate" and "EndDate" then how do I return all posts where today's date is >= the StartDate and <= the EndDate?

    Since custom fields are stored in the wp_postmeta table with the meta_key as type varchar(255) and the meta_value as type longtext this is not trivial. It definitely does not look like standard paramaters like "meta_key=" or "meta_value" will work.

    I'm thinking maybe using a 'posts_where' filter is the way to go (see http://codex.wordpress.org/Function_Reference/query_posts). However this still isn't easy because of the longtext field type. I'm going to look at some MySQL date/time functions to see if this is possible.

    In the end it may be best to create a custom table with an actual date field instead of storing records as posts or in addition to storing records as posts (e.g. linking the custom table back to the post via the post ID as a foreign key).

  5. sunriseweb
    Member
    Posted 3 years ago #

    Just found this thread:
    http://wordpress.org/support/topic/how-do-i-sort-posts-by-a-custom-field?replies=14

    Check it out - I think the answer resides there.

  6. sunriseweb
    Member
    Posted 3 years ago #

    Try something like this. I store my "checkIn" dates in post_date (the date the post was published) and my "checkOut" dates in the custom field "checkOut".

    //determine what rentals are reserved for date range
      global $wpdb;
      $querystr = "
      	SELECT wposts.*, wpostmeta.*
      	FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
      	WHERE wposts.ID = wpostmeta.post_id
      	AND wpostmeta.meta_key = 'checkOut'
      	AND wposts.post_date <= '$startDate'
      	AND STR_TO_DATE(wpostmeta.meta_value, '%Y-%m-%d %h:%i:%s') >= '$endDate'
      	AND wposts.post_type = 'reservations'
      	";
      echo "<h1>$querystr</h1>";
      $reserved_rentals = $wpdb->get_results($querystr);

    Hmmm - I think the logic is not quite right but you get the point.

  7. sunriseweb
    Member
    Posted 3 years ago #

    Here is what I ended up with:

    //Determine what reservations impact availability.  Three cases:
    // A) CheckIn date of reservation is within search date range: CI >= SD AND CI <= ED
    // B) CheckOut date of reservation is within search date range: CO > SD AND CO <= ED (note that if CheckOut date is equal to start date then doesn't impact availability)
    // C) CheckIn date of reservation is prior to searh date range and CheckOut date is after search date range: CI < SD AND CO > ED
    global $wpdb;
    $querystr = "
      SELECT wposts.*, wpostmeta.*
      FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
      WHERE wposts.post_type = 'reservations'
      AND wposts.ID = wpostmeta.post_id
      AND wpostmeta.meta_key = 'checkOut'
      AND (
             (
              STR_TO_DATE(wposts.post_date, '%Y-%m-%d') >= STR_TO_DATE('$startDate', '%Y-%m-%d')
              AND
              STR_TO_DATE(wposts.post_date, '%Y-%m-%d') <= STR_TO_DATE('$endDate', '%Y-%m-%d')
             )
             OR
             (
              STR_TO_DATE(wpostmeta.meta_value, '%Y-%m-%d') > STR_TO_DATE('$startDate', '%Y-%m-%d')
              AND
              STR_TO_DATE(wpostmeta.meta_value, '%Y-%m-%d') <= STR_TO_DATE('$endDate', '%Y-%m-%d')
             )
             OR
             (
              STR_TO_DATE(wposts.post_date, '%Y-%m-%d') < STR_TO_DATE('$startDate', '%Y-%m-%d')
              AND
              STR_TO_DATE(wpostmeta.meta_value, '%Y-%m-%d') > STR_TO_DATE('$endDate', '%Y-%m-%d')
             )
          )
       ORDER BY wposts.post_date ASC
      ";
    
    $reserved_rentals = $wpdb->get_results($querystr);

Topic Closed

This topic has been closed to new replies.

About this Topic