I started with SQL Server because that’s what I know, but I knew that wouldn’t be my final solution. Like many of you I’m sure, my blog runs on WordPress and MySQL. To make matters worse, my host is stuck on MySQL 5.7, which is missing some major conveniences I’ve been spoiled with in SQL Server, and that have been added to MySQL in the meantime – most notably, common table expressions (CTEs) and ROW_NUMBER().

In this post, I’ll show how I worked around that. But let’s start from the beginning, by starting from the end.

The End Goal

Think about the page (or sidebar) where your blog’s monthly archives (or any monthly breakdown of events, really) are typically represented in this simple way:

A while back, I made a change to that page on my site so that each month was represented by a visual calendar. But it was static CSS; every month looked the same, with the only changes being the month name and the clickable number (representing number of posts in that month):

But my vision was for the month to look accurate, with the first day starting on the right weekday, along with clickable days when blog posts were published (including special highlighting for days with multiple posts), clickable months to take you to an index for the month, and clickable years to take you to an index for that year.

My initial 2-minute sketch looked like this:

I fine-tuned it in HTML and CSS like this:

The HTML is easy enough to generate by hand, if you have a limited calendar and number of events:

But since my blog anthology spans more than 16 years and, presumably, you don’t want to muck with that HTML every month and for every new post you publish, we’ll need some programmatic help to make this automatic.

You’ll notice those aren’t standard HTML elements like <span> and <div> – I made my own because it was much easier to build and debug without extra noise, and also so I wouldn’t have to override any styling applied by templates, plug-ins, and WordPress itself. The <year> element is a grid, set up this way:

<month> is just a 210px × 156px, fixed-dimension wrapper around <days>, that fills <year> in a responsive way, thanks to the auto-fill grid. The dimensions are arbitrary, but were meant for the calendar to fill months 6, 4, or 3 wide, depending on browser window size.

<days> becomes a grid-within-a-grid, which allows 30-pixel wide <day> elements to flow 7 columns wide and up to 6 rows high (since some months that start on a Friday or Saturday actually have 5 Sundays), within their <month> container:

Finally, as I described in an earlier post, starting the weekday accurately (e.g. in the right grid column) relies on this ingenious CSS from Zell:

No, this isn’t the most responsive design I’ve ever created, because I rely on explicit pixel sizes, but if you view the site on a mobile device, you’ll see these dimensions are adjusted slightly to fill the screen appropriately.

How to Get There

My plan seemed simple: just port the same T-SQL queries I already wrote to MySQL, pull them from a PHP page, and call it a day. But, as hinted earlier, limitations in MySQL 5.7 made me step back. I thought about the things I needed to help me automate HTML generation, and how some would be complicated by the lack of modern language features, and by me being more comfortable writing MySQL queries than PHP code:

• A calendar table representing all of the dates I would ever need to display:
  • Start from the first day of the first month of my very first post (December 2006)
  • End at the last day of some month in the future
  • For each month, whether it is the first or last month in a (partial!) year
  • For each day, attributes that indicate:
    • Whether it is the first or last day of the month
    • For the first of each month, the weekday it falls on
• Data from wp_posts for every published post:
  • The date of the post
  • The number of posts published that day
  • Aggregated data for each month
  • Aggregated data for each year

I started with the built-in calendar table that will serve many other purposes down the road. Following at least some of WordPress’ existing naming conventions, I used this syntax to generate 8,432 rows, covering December 2006 (the month of my first post) through the end of 2029:

You can experiment with the contents of such a table in this db<>fiddle, but keep in mind this may generate a forward-compatibility warning in some versions.

I rely on INFORMATION_SCHEMA having enough columns in a WordPress installation, and this gets me well beyond the needed 8K+ rows. If you don’t have enough rows where you’re implementing this, you can cross join the view again. In MySQL 8.0, I probably would have used a recursive CTE to generate a series of days (though I would have to manage cte_max_recursion_depth to get more than 1,000 rows). This would avoid cross joins, identifying the number of days required, and pesky loop variables that might produce unwanted warnings.

…but, I don’t have that luxury on my site today.

I moved on to creating multiple stacked views to aggregate valid post data by day, then by month, and then by year. I did it this way to prevent the approach from becoming overly complicated in one giant query and to avoid repeating expressions multiple times at the same level. And also because I know I will be able to find other uses for some of these individual views.

First, a view to aggregate posts by day (and filter out drafts and other post types from all subsequent views):

Then we can use that view to aggregate by month:

And finally by year:

Putting it All Together

To combine our aggregated post data with the overall calendar table, we can select from the latter and outer join to the views. I expose a bunch of additional columns here because many will be used to either inject directly into the output HTML or help with conditional logic to decide what CSS classes to add and whether to end an HTML element:

The complicated part, of course, is building the final HTML output, which can be a very iterative process to get right – not just when there is a lot of data, but also when there are several edge cases. And when you want the source more legible and easier to troubleshoot, it pays to have additional whitespace peppered in there (like \r, \n, and \t).

I added a few niceties here, like prettier title attributes for clickable days, and no pluralization bugs for days with one or multiple posts:

Again, some of this was much more useful to me while developing than it is for the final output.

Anyway, I’m probably breaking all kinds of tribal coding conventions and best practices when working with MySQL, but I’m okay with that, since this is about the first thing I’ve ever published that wasn’t written in Transact-SQL.

Finally, the PHP that queries the view and renders the HTML is rather simple, because we’ve done all the work in the query. Though it can’t be called from a regular page; you need to do this from a custom page template and have your regular archives page inherit from it:

And now, because the views automatically return data up to and including the current month, you don’t have to touch this again as months and years roll over, outside of the case where your blog is still running in 2030. Or, maybe, if you unpublish posts from the oldest month and want to stop that month from showing up empty. With my previous solution I had hard-coded the list of years and months, and only pulled the number of posts dynamically from the database, so this is already reducing my workload by a little bit every month.

You can see this in action on sqlblog.org, and view the source to confirm the output is as expected. You can also view the CSS, which I left in the HTML source instead of squirreling it away into a separate CSS file, and didn’t minify it so you could grok it easily. Just right-click the page, choose View Page Source, and search for:

Further Enhancements

A couple of enhancements I’ve contemplated but not published, and may cover in a future post:

• List the post titles in the title tag – currently I just show how many posts are on a given day, but the title(s) could be surfaced by the underlying views in order to display them as well (granted, a title tag isn’t a great place to put a lot of information).
• Make single-post days redirect right to the post – the archive-by-date-parts page in WordPress defaults to showing a post listing, even in cases where there’s only one post. Seems then having to click on that post is an unnecessary step, and either the direct URL could be put into the calendar in that case, or I could dig into the WordPress code or the theme template and have the destination page check the count and decide what to do. This could work for months and years, too, since any of those slices could contain just a single post.
• Provide more granular calendars – the underlying views could also group by tag or category, allowing an archives page to present a calendar just for that subset of posts. Showing a calendar for my Bad Habits series, for example, would really highlight when I was very active on Stack Overflow and coming across a lot of terrible stuff there. In multi-author blogs (like this one!), you could make it so users see a visual calendar just for the selected author.

Conclusion

How you might implement this in your own blog will differ, depending on where you’re hosting, what version of MySQL you’re using, and the theme you’ve implemented. But the core concept is the same: build a calendar table, outer join to your events table, and then build the HTML. No more boring, never-ending lists of month names!