Query posts based on previous query

Question

I am trying to output some calendar events from a custom table which are also tied to a custom post type. I have this working except that in my foreach loop, the last date is repeated in each row. I need some help using the right method of getting the $eventDate correctly. Here is my code:

<?php
$today = date('Y-m-d');
global $wpdb;
$events = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."calp_events WHERE start >= '$today' ORDER BY start ASC LIMIT 5");
foreach ($events as $event) :
   $eventID = $event->post_id;
   $eventDate = $event->start;
endforeach;

global $post;
$args = array (
    'post_type' => 'calp_event',
    'ID' => $eventID,
);
$myposts = get_posts($args);
foreach ($myposts as $post) : setup_postdata($post);
    echo $eventDate.' '.the_title().'<br>';
endforeach;
?>

Somehow I need to be able to get the $eventDate in the last loop.

Answer 1

As discussed in the comments, instead of using the ID query argument to get each 'calp_event'-type post individually, you can pass an array of IDs to post__in in order to grab them all at once, thus reducing the number of database transactions in your code to 2.

By creating an array mapping post IDs to event start dates from the results of your 3rd-party table query, you can easily obtain the value to pass to post__in by using array_keys() to get an array of just post IDs. Then later in the loop, you can look up the start date using the same post-ID-to-start-date map.

Since you're looping through query results and using template tags in a manner similar to WordPress's "Loop", you might also consider using a new WP_Query object instead of get_posts() to allow you to use a more conventional "Loop" logic to display your results.

global $wpdb;

$today  = date('Y-m-d');
$events = $wpdb->get_results( "SELECT * FROM " . $wpdb->prefix . "calp_events WHERE start >= '$today' ORDER BY start ASC LIMIT 5" );

// Create an array using event post IDs as keys and start dates as values
$eventDates = array();
foreach( $events as $event ) {
  $eventDates[ $event->post_id ] = $event->start;
}

// Query for the 'calp_event' posts indicated by the earlier query.
// Since the keys of $eventDates are the post IDs returned by the previous query, 
// array_keys( $eventDates ) will return an array consisting of those IDs
$eventQuery = new WP_Query( array(
  'post_type' => 'calp_event',
  'post__in'  => array_keys( $eventDates )
) );

// Use conventional Loop logic to display your results
if( $eventQuery->have_posts() ) {
  while( $eventQuery->have_posts() ) {
    $eventQuery->the_post();

    // Accessing the $eventDates array with the current post ID as a key will produce
    // the event start date which that ID is mapped to.
    echo $eventDates[ get_the_ID() ] . ' ' . the_title() . '<br>';
  }

  // Reset global post data so we don't muck up the main query
  wp_reset_postdata();
}
else {
  echo 'No upcoming events!';
}

You could reduce this to a single query by using more advanced SQL to grab all of the necessary posts and event start-dates in one go.

Answer 2

You need to go for the nested loop. Try this

<?php
    $today = date('Y-m-d');
    global $wpdb;
    $events = $wpdb->get_results("SELECT * FROM ".$wpdb->prefix."calp_events WHERE start >= '$today' ORDER BY start ASC LIMIT 5");

    global $post;

    foreach ($events as $event) :
       $eventID = $event->post_id;
       $eventDate = $event->start;
       $args = array (
        'post_type' => 'calp_event',
        'ID' => $eventID,
       );
       $myposts = get_posts($args);
       foreach ($myposts as $post) : setup_postdata($post);
           echo $eventDate.' '.the_title().'<br>';
       endforeach;

       // wp_reset_postdata();
    endforeach;

?>

I moved your second foreach block inside the first one, so you will have everytime different $eventID and $eventDate. If it give you same data everytime try removing wp_reset_postdata(); comment block.