WordPress.org

1. eons
   Member
   Posted 2 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. MAS
   Member
   Posted 2 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 2 years ago #
   

   Anybody can help?

4. sunriseweb
   Member
   Posted 2 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 2 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 2 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 2 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.