WordPress Events List

Around this time last year, I wrote a post on how to create an Upcoming Events section on your WordPress blog.

The events can be displayed as a list of posts, ordered by the date in which they occur (And events that come and go will automatically be taken off the list of course).

As several of you found out this January though, that code had an issue in it that meant it stopped working with the new year. In this post, we’re going to fix that (Sorry it’s taken me to now to publish a tutorial with the solution!).

How to Add Events

The most important thing is that we won’t be making any changes to how events are added. WordPress 3.0 was released a few months after I wrote the original post, and its custom post types feature could be very useful for this, but the idea here is to fix the system for everyone already using it.

For that reason, we’ll keep adding events by:

  • Adding posts to a category called Events.
  • Specifying the date of the event using a custom field called Date. The format of the date is: mm/dd/yy

Date Entry

To get started, go and and add a few test posts to your Events category now.

Building Our MySQL Query

Instead of a regular WordPress query_posts(), this time we’ll be building a MySQL query.

Start off by finding where in your template you want to insert your list (I’m adding it to the sidebar in this example). Begin by pasting in the following:

1
2
3
4
5
6
7
<ul>
<?php 
// Build a custom query to get posts from future dates.
$querystr = "
    SELECT wposts.* 
    FROM $wpdb->posts wposts, $wpdb->postmeta wpostmeta
    WHERE wposts.ID = wpostmeta.post_id

The code above starts our list, but then we move straight into making the query. The first line of the query (SELECT…) says that we want to return all of the data about each post from the wp_posts table.

The next two lines (FROM and WHERE) specify that we want to work with 2 tables in our query (With the WHERE telling MySQL how to match up corresponding rows).

Now we need to tell MySQL how to find the event posts. To do that, paste the following after what we just added (Although this is on multiple lines, the whole query is just one big PHP string for now).

1
2
3
4
5
AND wpostmeta.meta_key = 'Date'
AND STR_TO_DATE(wpostmeta.meta_value,'%m/%d/%Y') >= CURDATE()
 
AND wposts.post_status = 'publish'
AND wposts.post_type = 'post'

The first line specifies that we’re only interested in posts that have the “Date” custom field. In the second line, we’re using 2 MySQL functions to ask MySQL to work out which events are upcoming (not yet passed).

The STR_TO_DATE function takes the date in the format we entered (mm/dd/yy) and converts it to a datetime value. The CURDATE() function enters the current date as a datetime value as well, so we can compare the two properly (This is where the previous method failed).

Now we’ll finish up our query by specifying how many events we want to list and we’ll use the STR_TO_DATE function again to tell MySQL to order them by the soonest first.

1
2
3
ORDER BY STR_TO_DATE(wpostmeta.meta_value,'%m/%d/%Y') ASC
LIMIT 5
";

Displaying Our Events

The query above will find exactly what we need, so now we need to display it.

The first step is of course to run the query and save the results.

1
$events = $wpdb->get_results($querystr, OBJECT);

With the results in the $events variable, we’ll now set up a loop to run through each one and display it in the list.

1
2
3
4
5
6
7
8
9
10
11
12
 if ($events):
	global $post;
 	foreach ($events as $post):
 		setup_postdata($post); ?>		
			<li>
			<a href="<?php the_permalink(); ?>"><?php the_title(); ?></a>
			</li>
	<?php endforeach;
else : ?>
	<li>Sorry, no events coming up.</li>
<?php endif; ?>
</ul>

The first line checks to see if we found any results. If we did, we then move into a foreach loop that will go through each of the posts individually.

For each one, we use WordPress’ setup_postdata() function to take the data in our resulting post objects and set up the functions we’re used to working with (the_title(); the_permalink(); etc.).

The last few lines just close up our loop, if-statement, and list. And of course, we’ll show a message if there aren’t any events coming up.

And that’s us set! There are plenty of enhancements you could make to this, e.g. custom post types for events, a meta box for entering the date, or even a traditional calendar layout (Though for a small area like a sidebar, I’d stick with the list view).

If you use this code and would like to see how to do any of those, let me know in the comments!

Update (12 March ’11) – Thanks to Amit, Gunfios, and Tracy for spotting errors in the original code!

Enjoy this post? You should follow me on Twitter!