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

1 Views

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

Read More
$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.

Post Views: 1

Related posts

1 comment

  1. 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.

Comments are closed.