Take the 2-minute tour ×
WordPress Development Stack Exchange is a question and answer site for WordPress developers and administrators. It's 100% free, no registration required.

Select event where start date is smaller than current date and end date is greater than current date

Suppose an event A start date is 2013-06-12 and end date is 2013-07-30. I've a query where start date is 2013-06-20 and end date is 2013-06-30. So Event A should show in my query result. but I don't get it. This is my query

$query = "
SELECT DISTINCT ID
FROM {$wpdb->prefix}posts
JOIN {$wpdb->prefix}postmeta m1
ON ( {$wpdb->prefix}posts.ID = m1.post_id )
WHERE
{$wpdb->prefix}posts.post_type = 'tribe_events'

AND {$wpdb->prefix}posts.post_status = 'publish'

AND (

    ( (m1.meta_key = '_EventStartDate' AND m1.meta_value >= '$start_date') AND ( m1.meta_key = '_EventStartDate' AND m1.meta_value <= '$end_date' ))

    OR 
    ( (m1.meta_key = '_EventEndDate' AND m1.meta_value >= '$start_date') AND ( m1.meta_key = '_EventEndDate' AND m1.meta_value <= '$end_date' ) )

    OR
    ( (m1.meta_key = '_EventStartDate' AND m1.meta_value < '$start_date') AND ( m1.meta_key = '_EventEndDate' AND m1.meta_value > '$end_date') )

    )

ORDER BY {$wpdb->prefix}posts.post_date
DESC;
";

$result = $wpdb->get_results($query);

Thanks in advance.

share|improve this question
    
So is this not working? What does it output? What is the question exactly? I'm interested in this topic as well. –  GhostToast Jun 21 '13 at 18:57

1 Answer 1

up vote 0 down vote accepted

You can try to replace this kind of comparison:

AND m1.meta_value >= '$start_date'

with:

AND CAST(m1.meta_value AS DATE) >= '" . date( "Y-m-d H:i:s", $start_date ) . "'

or if you would use Unix timestamp for the meta values then you could try:

AND m1.meta_value+0  >= ". strtotime( $start_date ) ."

Update:

Here is a modification of your query:

$query = " SELECT DISTINCT * FROM {$wpdb->posts}
INNER JOIN {$wpdb->postmeta} m1 ON ( {$wpdb->posts}.ID = m1.post_id )
INNER JOIN {$wpdb->postmeta} m2 ON ( {$wpdb->posts}.ID = m2.post_id )
WHERE {$wpdb->posts}.post_type = 'tribe_events'
AND {$wpdb->posts}.post_status = 'publish'
AND (
       ( ( m1.meta_key = '_EventStartDate' AND CAST( m1.meta_value AS DATE ) >= %s ) AND ( m2.meta_key = '_EventStartDate' AND CAST( m2.meta_value AS DATE ) <= %s ))
       OR 
       ( ( m1.meta_key = '_EventEndDate' AND CAST( m1.meta_value AS DATE ) >= %s ) AND ( m2.meta_key = '_EventEndDate' AND CAST( m2.meta_value AS DATE ) <= %s ) )
       OR
       ( ( m1.meta_key = '_EventStartDate' AND CAST( m1.meta_value AS DATE ) < %s ) AND ( m2.meta_key = '_EventEndDate' AND CAST( m2.meta_value AS DATE ) > %s ) )
    )
ORDER BY {$wpdb->posts}.post_date DESC;";

$query = $wpdb->prepare( $query, $start_date, $end_date, $start_date, $end_date, $start_date, $end_date );
$result = $wpdb->get_results( $query );

where the two INNER JOINS are added since you are filtering with AND on two different meta keys.

share|improve this answer
    
First 2 condition is good. Only need need to know about 3rd condition '(m1.meta_key = '_EventStartDate' AND m1.meta_value < '$start_date') AND ( m1.meta_key = '_EventEndDate' AND m1.meta_value > '$end_date') ) ' Where start date is less then database start date and end date greater then database end date. but those event should show in my result because event is live. –  Anas Jun 22 '13 at 5:17
    
Thank you so much. Yes, it is now working well. Just need to add DISTINCT ID instead of DISTINCT * –  Anas Jun 23 '13 at 6:57
    
ok, great to hear that, please accept the answer (see here) to close the question, thanks ;-) –  birgire Jun 23 '13 at 9:48

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.