0

Im using event calendar plugin where I can set event date for posts and it shows in calendar widget. Everything is working as inteded but I would also like to query post by event date.

Problem is event date is stored in separate table named wp_ftcalendar_events. Name of meta key is start_datetime. Table starts with post ID so it's shared for both tables. Unfortunately joining tables is beyond my knowledge.

enter image description here

My try doesn't work:

        <?php
        global $wpdb;
        $date = date("Y-m-d");
             $querystr = "
                SELECT $wpdb->posts.* 
                FROM $wpdb->posts, $wpdb->wp_ftcalendar_events
                WHERE $wpdb->posts.post_parent = $wpdb->wp_ftcalendar_events.post_parent 
                AND $wpdb->posts.post_status = 'publish' 
                AND $wpdb->wp_ftcalendar_events.meta_key = 'start_datetime' 
                AND $wpdb->wp_ftcalendar_events.meta_value > " . $date . " 
                ORDER BY $wpdb->wp_ftcalendar_events.meta_value ASC
             ";


             $pageposts = $wpdb->get_results($querystr, OBJECT);
             foreach ($pageposts as $post): ?>          

        <!-- do stuff -->

        <?php endforeach; ?>
  • What kind of result are you getting? Is that snippet you posted complete? If so, then you need to globalize $wpdb before using it. – Brian Aug 6 '15 at 20:30
  • Edited code. As far as I know it should work. But it doesn't. – th3rion Aug 7 '15 at 5:45
  • Please change the table name to following:- {$wpdb->prefix}your_table_name – WisdmLabs Aug 7 '15 at 6:35
0

Working code:

<?php
global $wpdb;
$date = date("Y-m-d");
$querystr = "
SELECT * 
FROM wp_posts
JOIN wp_ftcalendar_events ON wp_posts.ID = wp_ftcalendar_events.post_parent
WHERE wp_posts.post_status =  'publish'
AND wp_posts.post_type =  'post'
AND wp_ftcalendar_events.start_datetime >= '$date' 
ORDER BY wp_ftcalendar_events.start_datetime ASC 
 ";
 $pageposts = $wpdb->get_results($querystr, OBJECT_K); ?>
 <?php if ($pageposts): ?>

  <?php global $post; ?>
  <?php foreach ($pageposts as $post): ?>
      <?php setup_postdata($post); ?>

      <?php $start_date = date_i18n("Y-m-d", strtotime( $post->start_datetime )); ?>

      <?php the_title(); ?><br>
      <?php echo $date; ?><br>
      <?php echo $start_date; ?><br>
  <?php endforeach; ?>

 <?php endif; ?>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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