{"id":95420,"date":"2023-03-10T00:00:06","date_gmt":"2023-03-10T00:00:06","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/?p=95420"},"modified":"2023-03-13T21:30:24","modified_gmt":"2023-03-13T21:30:24","slug":"wordpress-archives-page-mysql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/featured\/wordpress-archives-page-mysql\/","title":{"rendered":"Build a Better WordPress Archives Page Using MySQL"},"content":{"rendered":"
I recently wrote about a way to generate visual, interactive calendars in HTML by combining a calendar table with data from an events table (part 1<\/a> and part 2<\/a>). This technique could be used to overlay any type of data onto calendars, but my specific goal was to make the monthly archives page<\/a> on my blog a little less boring.<\/p>\n

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()<\/code>.<\/p>\n

In this post, I’ll show how I worked around that. But let’s start from the beginning, by starting from the end.<\/p>\n

The End Goal<\/h2>\n

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:<\/p>\n

Archives<\/b>
\nJanuary 2023<\/a> (3)
\nDecember 2022<\/a> (2)
\nNovember 2022<\/a> (5)
\n…ho hum (ad nauseum)<\/div>\n

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):<\/p>\n

\"Initial<\/p>\n

But my vision was for the month to look accurate<\/em>, 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.<\/p>\n

My initial 2-minute sketch looked like this:<\/p>\n

\"Initial<\/p>\n

I fine-tuned it in HTML and CSS like this:<\/p>\n

\"Much<\/p>\n

The HTML is easy enough to generate by hand, if you have a limited calendar and number of events:<\/p>\n

\"HTML<\/p>\n

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.<\/p>\n

You’ll notice those aren’t standard HTML elements like <span><\/code> and <div><\/code> – 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><\/code> element is a grid, set up this way:<\/p>\n

  year \r\n  {\r\n    display: grid; \r\n    grid-template-columns: repeat(auto-fill, 210px);\r\n  }<\/pre>\n
<month><\/code> is just a 210px \u00d7 156px, fixed-dimension wrapper around <days><\/code>, that fills <year><\/code> 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.<\/p>\n
  month \r\n  {\r\n    width:  210px;\r\n    height: 156px;\r\n  }<\/pre>\n
<days><\/code> becomes a grid-within-a-grid, which allows 30-pixel wide <day><\/code> 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><\/code> container:<\/p>\n
  days\r\n  {\r\n    display: grid;\r\n    grid-template-columns: repeat(7, 30px);\r\n    grid-template-rows:    repeat(6, 21px);\r\n  }<\/pre>\n
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<\/a>:<\/p>\n
  \/* for each <days class=d{X}> make {X} the first column *\/\r\n  days.d1 day:first-child { grid-column: 1; }\r\n  days.d2 day:first-child { grid-column: 2; }\r\n  days.d3 day:first-child { grid-column: 3; }\r\n  days.d4 day:first-child { grid-column: 4; }\r\n  days.d5 day:first-child { grid-column: 5; }\r\n  days.d6 day:first-child { grid-column: 6; }\r\n  days.d7 day:first-child { grid-column: 7; }<\/pre>\n
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<\/a> on a mobile device, you’ll see these dimensions are adjusted slightly to fill the screen appropriately.<\/p>\n
How to Get There<\/h2>\n
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:<\/p>\n