0

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.

2
  • Why use a custom post type and a custom table? A custom post type + post meta is capable of modeling an event, and has the added benefit of leveraging WordPress's built-in caching mechanisms. If you need to use both a CPT and a custom table, the most efficient way to accomplish this would be to use a more advanced SQL query to grab every relevant cal_event at once and merge in the dates from your custom table.
    – bosco
    Mar 30, 2017 at 3:10
  • I wish I could use post_meta it would be so much more efficient. This is a calendar feed created by a plugin that is generating a custom table, making it more difficult to query.
    – RiotAct
    Mar 30, 2017 at 3:39

2 Answers 2

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.

2
  • That works! Thanks for the help! I originally tried to use a join function for the tables which i thought could work since the id's were the same, but I couldn't get it to work properly.
    – RiotAct
    Mar 31, 2017 at 0:26
  • Fo sho! SQL JOINs can be some real tricky business - I still have to look them up every time I need to read or write queries which use them (luckily that doesn't happen very often these days :]).
    – bosco
    Mar 31, 2017 at 0:48
0

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.

6
  • Note that this will query the database count( $events ) + 1 times every time this code is ran (the same number as the code @RiotAct posted in the question will, to be clear). Since every call to get_posts() is just querying for a single post, it would be much more efficient to just grab them all at once with a using a single call to get_posts() with the post__in argument.
    – bosco
    Mar 30, 2017 at 3:14
  • The answer above repeats everything 5 times. This is why I moved my second query out of the first foreach statement so it doesnt loop the results.
    – RiotAct
    Mar 30, 2017 at 4:26
  • @bosco I tried using the post__in, and that works, but how do I access $eventDate?
    – RiotAct
    Mar 30, 2017 at 4:26
  • So that's where it gets a little more tricky ;) . You could create an array mapping eventIDs to eventDates as soon as you get $events - let's call this array $eventDates. What you pass as the post__in argument will actually be array_keys( $eventDates ) - that is, an array containing just the keys from that array, since the keys are the post IDs. Once you get your array of posts from your second query, you can look up the start date for the current post in the loop with $eventDates[ get_the_ID() ]. Did that make sense?
    – bosco
    Mar 30, 2017 at 4:37
  • Im not sure how to do that. can you show an example as an answer?
    – RiotAct
    Mar 30, 2017 at 4:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.