Show only start and end dates after today from repeater field

• ExtrdnryMchn

• November 14, 2018 at 2:42 am

I’ve got a a post type for different tour, and each tour has various occurrences. I’ve used a repeater field to give each occurrence a start and end date with the date picker field.

I need to show upcoming tours by month, and I’ve got most of the way there using this post as a starting point https://support.advancedcustomfields.com/forums/topic/calendar-by-month-with-date-repeater/#post-41019

However, I don’t want the calendar to show anything before the current date. Would it be better to take care of this in the SQL query, or afterwards in PHP? I’ve been trying in PHP but haven’t managed anything yet.

I also need to show the end date for each tour, which I worked out a way to do, but just wondering if there’s a better way to do it in SQL (I feel like there must be).

Here’s my code so far:

global $wpdb;

	// gets start and end dates with post id from ACF repeater
	$rows = $wpdb->get_results( 
        "
        SELECT 
			A.post_id, 
			A.meta_value as startdate, 
			B.meta_value as enddate
		FROM wac_n06_postmeta as A 
		JOIN wac_n06_postmeta as B on A.post_id 
			WHERE A.post_id = B.post_id 
			AND A.meta_key LIKE 'tour_dates_%_tour_start' AND B.meta_key LIKE 'tour_dates_%_tour_end'
		    AND SUBSTRING(B.meta_key,12,5) = SUBSTRING(A.meta_key,12,5)
        "
    );

	setlocale(LC_ALL, 'de_DE');

	$month = -1;

	$order = array();

	// populate order
	foreach( $rows as $i => $row ) {
		$order[ $i ] = $row->startdate;
	}

	// multisort
	array_multisort( $order, SORT_ASC, $rows );

	// Loop through the returned rows
	foreach( $rows as $row) {

	$startdate = $row->startdate;
	$enddate = $row->enddate;
	$post_id = $row->post_id;
	$tour_name = get_the_title($post_id);
	$tour_tags = get_the_tags($post_id);

	$newMonth 	= intval(substr($startdate, 4, 2));
	$time		= strtotime($startdate);

	if($month !== $newMonth) {
		if($month !== -1) {
			// end section
			?>
			</tbody></table></div></section>
			<?php
		}
		
		// start section
		?>
		<section class="content-wrapper calendar">
	  	  <h3><?php echo strftime("%B %Y", $time); ?></h3>
	  	  
	  	  <div class="table-wrapper">
	  	  	<table>
	  	  		<thead><tr><td class="date">Datum</td><td class="tour-name">Name der Tour</td><td class="tour-tag">Tour Kategorie</td><td class="arrow"></td></tr></thead>
	  	  		<tbody>
		<?php
	}

	$month = $newMonth;

	// row
		?>
			<tr onclick="location.href='<?php the_permalink( $post_id ); ?>'">
				<td class="date"><?php echo date('d.m.y', strtotime($startdate)) . ' - ' . date('d.m.y', strtotime($enddate)) ?></td>
				<td class="tour-name"><?php echo $tour_name; ?></td>
				<td class="tour-tag">
					<?php
					foreach($tour_tags as $tour_tag) :  
						echo $tour_tag->name . '&nbsp;'; 
			      	endforeach; 
			      	?>
			    </td>
				<td class="arrow"><i class="fas fa-caret-right"></i></td>
			</tr>

			<?php
	}

	?>
	</tbody></table></div></section>

• ExtrdnryMchn

• December 1, 2018 at 4:09 am

I’ve updated this to have the SQL query take care of the date issues, so it’s now:

SELECT 
			A.post_id, 
			A.meta_value as startdate, 
			B.meta_value as enddate
		FROM wac_n06_postmeta as A 
		JOIN wac_n06_postmeta as B on A.post_id 
			WHERE A.post_id = B.post_id 
			AND A.meta_value > CURDATE()
			AND A.meta_key LIKE 'tour_dates_%_tour_start' AND B.meta_key LIKE 'tour_dates_%_tour_end'
		    AND SUBSTRING(B.meta_key,12,5) = SUBSTRING(A.meta_key,12,5)

But I still haven’t worked out how to make the matching bit less hacky.

Any help much appreciated!