Query between dates using datepicker broken in wp 4.2.1

• robbiegod

• May 2, 2015 at 2:24 am

Original Posts: http://support.advancedcustomfields.com/forums/topic/query-between-dates-using-date-picker-fields/

This thread has a solution that i was able to use up until i updated wordpress to v4.2.1 and I’ve updated ACF to ACF PRO v5 today.

Now the filter i have on the events start and end date no longer works. It will only show dates within the may (the current month).

How it was working before is if an event started in april and ended in june, it would show me that event in april, may, and june.

Any idea what could have broken the filter?

Code samples:

page-events-widget.php – this page outputs the events.

<?php
/* Template Name: Events Widget */
$today = date('Ymd');
$j = 0;

if (isset($_GET['_m'])) {
	
	$current_month = str_pad($_GET['_m'], 2, '0', STR_PAD_LEFT);
	$current_day = "01"; // day one
	$current_year = $_GET['_y'];
	
	$get_last_day = $current_year.$current_month.$current_day;
	$lastday = date("t", strtotime($get_last_day));
	
	$tempstartday = $current_year.$current_month.$current_day;
	$tempendday = $current_year.$current_month.$lastday;
	
	$startday = date('Ymd', strtotime($tempstartday));
	$endday = date('Ymd', strtotime($tempendday));

} else {
	
	$current_month = str_pad(date('m'), 2, '0', STR_PAD_LEFT);
	$current_day = "01"; // day one
	$current_year = date('Y');
	
	$get_last_day = $current_year.$current_month.$current_day;
	$lastday = date("t", strtotime($get_last_day));
	
	$tempstartday = $current_year.$current_month.$current_day;
	$tempendday = $current_year.$current_month.$lastday;
	
	$startday = date('Ymd', strtotime($tempstartday));
	$endday = date('Ymd', strtotime($tempendday));
}

add_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );

$qryevents = array(
	'post_type' => 'events',
	'posts_per_page' => 50,
	'status' => 'published',
	'meta_key' => 'event_start_date',
	'orderby' => 'meta_value',
	'order' => 'ASC',

	// produces meta join and where clauses for the query
	// which will be filtered in functions.php
	'meta_query' => array(
		'relation' => 'AND',
		array(
			'key'     => 'event_start_date',
			'compare' => '>=',
			'value'   => $startday,
			'type' => 'DATE'
		),
		array(
			'key'     => 'event_end_date',
			'compare' => '<=',
			'value'   => $endday,
			'type' => 'DATE'
		)
	)
);

$loop = new WP_Query( $qryevents );
remove_filter( 'get_meta_sql', 'get_meta_sql_date', 10, 2 );

if ( $loop->have_posts() ) :
while ( $loop->have_posts() ) : $loop->the_post();

// Let's format the dates
$get_start_date = get_field('event_start_date');
$get_end_date = get_field('event_end_date');
$event_start_date = DateTime::createFromFormat('Ymd', $get_start_date);
$event_end_date = DateTime::createFromFormat('Ymd', $get_end_date);
// End of date definitions

// Let's get the event start and end times
$get_start_time = get_field('event_start_time');
$get_end_time = get_field('event_end_time');
// end of times

// Let's get the times of the events now
$specify_event_time = "";

$show_event_times = get_field('specify_event_times');
if($show_event_times){
		foreach($show_event_times as $specify_event_time){
			// Do nothing; this puts the yes value into the varible for us to later on the page.
			// echo $specify_event_time;
		}
}
// End of the specify times
?>

<div class="<?php echo (++$j % 2 == 0) ? 'full row' : 'full row alt'; ?>">
<p><a href="<?php the_permalink(); ?>"><?php the_title(); ?></a></p>
	<?php if($specify_event_time == "yes"): ?>
    <p class="event-date"><a href="<?php the_permalink(); ?>"><?php echo $event_start_date->format('M d, Y'); ?> <?php echo $get_start_time; ?> - <?php echo $event_end_date->format('M d, Y'); ?> <?php echo $get_end_time; ?></a></p>
  <?php else: ?>
    <p class="event-date"><a href="<?php the_permalink(); ?>"><?php echo $event_start_date->format('M d, Y'); ?> - <?php echo $event_end_date->format('M d, Y'); ?></a></p>
  <?php endif; ?>
</div>   

<?php
endwhile;

else:
?>
<p>No scheduled events.</p>
<?php endif; wp_reset_query(); ?>

functions.php

// Magic Function to filter start and end dates properly
function get_meta_sql_date( $pieces, $queries ) {
	global $wpdb;

	// get start and end date from query
	foreach ( $queries as $q ) {

		if ( !isset( $q['key'] ) ) {
			return $pieces;
		}

		if ( 'event_start_date' === $q['key'] ) {
			$start_date = isset( $q['value'] ) ?  $q['value'] : '';
		}
		if ( 'event_end_date' === $q['key'] ) {
			$end_date = isset( $q['value'] ) ?  $q['value'] : '';
		}
	}

	if ( ( '' === $start_date ) || ( '' === $end_date ) ) {
		return $pieces;
	}

	$query = "";

	// after start date AND before end date
	$_query = " AND (
		( $wpdb->postmeta.meta_key = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) >= %s) )
  		AND ( mt1.meta_key = 'event_end_date' AND ( CAST(mt1.meta_value AS DATE) <= %s) )
	)";
	$query .= $wpdb->prepare( $_query, $start_date, $end_date );

	// OR before start date AND after end date
	$_query = " OR (
		( $wpdb->postmeta.meta_key = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s) )
  		AND ( mt1.meta_key = 'event_end_date' AND ( CAST(mt1.meta_value AS DATE) >= %s) )
	)";
	$query .= $wpdb->prepare( $_query, $start_date, $end_date );

	// OR before start date AND (before end date AND end date after start date)
	$_query = " OR (
		( $wpdb->postmeta.meta_key = 'event_start_date' AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s) )
  		AND ( mt1.meta_key = 'event_end_date'
  			AND ( CAST(mt1.meta_value AS DATE) <= %s )
  			AND ( CAST(mt1.meta_value AS DATE) >= %s )
  		)
	)";
	$query .= $wpdb->prepare( $_query, $start_date, $end_date, $start_date );

	// OR after end date AND (after start date AND start date before end date) )
	$_query = "OR (
		( mt1.meta_key = 'event_end_date' AND ( CAST(mt1.meta_value AS DATE) >= %s ) )
		AND ( $wpdb->postmeta.meta_key = 'event_start_date'
			AND ( CAST($wpdb->postmeta.meta_value AS DATE) >= %s )
			AND ( CAST($wpdb->postmeta.meta_value AS DATE) <= %s )
  		)
	)";
	$query .= $wpdb->prepare( $_query, $end_date, $start_date, $end_date );

	$pieces['where'] = $query;

	return $pieces;
}

Any idea why this doesnt work in WP v4.2.1 and ACF PRO v5 (latest version, just updated todat 5/1/2015)

• James

• April 13, 2016 at 2:28 am

This reply has been marked as private.