Support

Account

Home Forums Add-ons Repeater Field Show only start and end dates after today from repeater field

Helping

Show only start and end dates after today from repeater field

  • 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>
    
  • 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!

Viewing 2 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic.

We use cookies to offer you a better browsing experience, analyze site traffic and personalize content. Read about how we use cookies and how you can control them in our Cookie Policy. If you continue to use this site, you consent to our use of cookies.