order events by start date – working query

  • I’ve seen here a lot of topics about ordering events by start date. Unfortunately without answer. After a while I came up with working code. It is a Custom Select Query comining two tables. Showing posts that start_datetime is >= than current date and ordering ASC. Maybe it would help someone. 

    <?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 $start_date; ?><br>

  <?php endforeach; ?>

 <?php endif; ?>

    https://www.ads-software.com/plugins/ft-calendar/

Viewing 1 replies (of 1 total)
  • Thread Starter th3rion

    (@th3rion)

    Example of code with working pagination with WP-Paginate plugin but you can use default WP controls or other plugin – just change last part. 

    <?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'
GROUP BY wp_posts.ID
ORDER BY wp_ftcalendar_events.start_datetime DESC
";

$total_record = count($wpdb->get_results($querystr, OBJECT_K));
$paged = get_query_var('paged') ? get_query_var('paged') : 1;
$post_per_page  = 10;
$offset = ($paged - 1)*$post_per_page;
$max_num_pages = ceil($total_record/ $post_per_page);

$wp_query->found_posts = $total_record;
$wp_query->max_num_pages = $max_num_pages;

$limit_query = " LIMIT ".$post_per_page." OFFSET ".$offset;  

$pageposts = $wpdb->get_results($querystr.$limit_query, OBJECT_K); ?>

<?php if ($pageposts): ?>

<?php global $post; ?>

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

<?php setup_postdata($post); ?>

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

<!-- do stuff -->

<?php echo $start_date; ?> <!-- example how to display event start date -->

<?php endforeach; ?>

<?php endif; ?>

<?php if(function_exists('wp_paginate')) { wp_paginate(); } ?>
Viewing 1 replies (of 1 total)
  • The topic ‘order events by start date – working query’ is closed to new replies.