Insights

MySQL query for getting three most recent posts for last 12 months in WordPress

Ryan Sechrest

Mar 28, 2013 • 5 min read

Sometimes the best plan is no plan at all.

I recently had a need to retrieve the last three posts for the last 12 months that had at least one or more posts in WordPress. Another requirement was to display a “more” link that would redirect the user to the archive of that month, provided there was at least one additional post to show for.

Before we go over how we can do that, here is an excerpt of what the array ultimately will look like:

Array
(
    [201303] => Array
      (
        [month_year] => March 2013
        [more_posts] => 1
        [posts] => Array
          (
            [0] => Array
              (
                [post_title] => Test Post 1
              )

            [1] => Array
              (
                [post_title] => Test Post 2
              )

            [2] => Array
              (
                [post_title] => Test Post 3
              )
          )
      )

    [201302] => Array
      (
        [month_year] => February 2013
        [more_posts] => 1
        [posts] => Array
          (
            [4] => Array
              (
                [post_title] => Test Post 4
              )

            [5] => Array
              (
                [post_title] => Test Post 5
              )

            [6] => Array
              (
                [post_title] => Test Post 6
              )
          )
      )
    ...
)

It’s an array with 12 elements, if there are, in fact, 12 months with posts, where each element contains the name of the month with a corresponding year, a boolean of whether there are more posts to show for, and another array with three post titles linking to the full post.

1. Declaring required PHP variables

We need to declare a few PHP variables to get started:

The WordPress database object $wpdb
An array $blog_posts to store all of our results
A variable $num_months for how many months we want
A variable $num_posts for how many posts we want per month
A variable $num_posts_buffer for how many posts per month we actually need (to see if we have more posts)

global $wpdb;
$blog_posts = array();
$num_months = 12;
$num_posts = 3;
$num_posts_buffer = $num_posts + 1;

2. Declaring required MySQL variables

We also need declare two MySQL variables:

A variable @row_num to track the row number per month
A variable @month_year to track the current month

I’ll share more details on why we need those variables in section 3.2.

$wpdb->query('set @row_num := 0, @month_year := \'\'');

3. Building the MySQL query

This is the meat and potatoes of the post, so we’ll spend a little more time on this. Let’s look at the full query first, then we’ll dissect it.

The MySQL query in PHP:

$query = 'SELECT wpp3.ID, wpp3.post_title, wpp3.month_year, wpp3.month_year_slug ';
$query .= 'FROM (SELECT wpp2.*, ';
  $query .= '@row_num := IF(@month_year = month_year, @row_num + 1, 1) AS row_num, ';
  $query .= '@month_year := month_year ';
  $query .= 'FROM (SELECT wpp1.*, ';
    $query .= 'DATE_FORMAT(post_date, \'%%M %%Y\') AS month_year, ';
    $query .= 'DATE_FORMAT(post_date, \'%%Y%%m\') AS month_year_slug ';
    $query .= 'FROM ' . $wpdb->posts . ' AS wpp1 ';
    $query .= 'WHERE wpp1.post_status = %s ';
    $query .= 'AND wpp1.post_type = %s ';
    $query .= 'ORDER BY wpp1.post_date DESC';
    $query .= ') as wpp2';
  $query .= ') as wpp3 ';
$query .= 'WHERE row_num <= %d ';
$query .= 'LIMIT %d';

The actual MySQL query we’re making once processed by PHP:

SELECT wpp3.id,
  wpp3.post_title,
  wpp3.month_year,
  wpp3.month_year_slug
FROM (
  SELECT wpp2.*,
    @row_num := IF(@month_year = month_year, @row_num + 1, 1) AS row_num,
    @month_year := month_year
  FROM (
    SELECT wpp1.*,
      Date_format(post_date, '%M %Y') AS month_year,
      Date_format(post_date, '%Y%m') AS month_year_slug
    FROM wp_posts AS wpp1
    WHERE wpp1.post_status = 'publish'
      AND wpp1.post_type = 'post'
    ORDER BY wpp1.post_date DESC
  ) AS wpp2
) AS wpp3
WHERE row_num <= 4
LIMIT 48;

3.1 Retrieving most recent published posts

The first subquery retrieves all published posts with all columns, including a new column for the month/year (for display purposes) and another new column for the month/year slug (for indexing purposes). All posts are sorted by most recent first.

Give me all columns
Including the post_date formatted as %M %Y (March 2013) in a new column called month_year
Including the post_date formatted as %Y%m (201303) in a new column called month_year_slug
From the table called wp_posts, referenced as wpp1 from here on out
Where the post_status is equal to publish (not draft, pending, etc)
And the post_type is of type post (not page, revision, etc)
Then order everything by post_date DESC i.e. most recent first

The reason I prefix all my columns is to avoid conflicts when dealing with multiple tables that have the same column name, because MySQL then won’t know which column you’re referring to, so I just got into the habit of doing it. wpp1 in this case is an abbreviated version of wp_posts and the 1 refers to the first instance of that able, since we’ll have more later.

SELECT wpp1.*,
      Date_format(post_date, '%M %Y') AS month_year,
      Date_format(post_date, '%Y%m') AS month_year_slug
FROM wp_posts AS wpp1
WHERE wpp1.post_status = 'publish'
      AND wpp1.post_type = 'post'
ORDER BY wpp1.post_date DESC

3.2 Retrieving most recent published posts with row number

The purpose of this subquery is to add a column with a set of row numbers for each month/year combination. The reason we’re doing this is so that we can limit the amount of posts per month/year combination later.

Give me all columns from the previous subquery
Including a row_num column containing the row number
-- If the @month_year variable contains the same month that was set in the last table row (if at all i.e. first month), then take the value of row_num and increment it by one
-- But if we’re in new month/year combination, then we need to start the row_num at 1
From the table instance we created in the previous subquery

SELECT wpp2.*,
  @row_num := IF(@month_year = month_year, @row_num + 1, 1) AS row_num,
  @month_year := month_year
FROM (
  # Query from section 3.1 here
) AS wpp2

3.3 Retrieving four most recent published posts

In this final part we’re limiting our result set to only what we’re interested in.

Give me the post ID
The post title
The month/year as e.g. March 2013
The month/year slug as e.g. 201303
From the table instance we created in the previous subquery
Where the row number row_num is less than or equal to 4 i.e. rows 1, 2, 3 and 4
But no more than a total of 48 posts (4 posts x 12 months)

SELECT wpp3.id,
  wpp3.post_title,
  wpp3.month_year,
  wpp3.month_year_slug
FROM (
  # Query from section 3.2 here
) AS wpp3
WHERE row_num <= 4
LIMIT 48;

4. Execute the MySQL query

Now we’re going to submit the query to MySQL and capture the results as an array. This query is prepared because the values could potentially come from outside of our scope via a function parameter — it’s also best practice.

$posts = $wpdb->get_results(
  $wpdb->prepare(
    $query,
    'publish', 'post', $num_posts_buffer, ($num_months*$num_posts_buffer)
  ),
  ARRAY_A
);

5. Looping and organizing through the results

This part is pretty self-explanatory and now that you have the results, you can organize it in any way you want. Here is what I did, with explanations inline.

// If there are blog posts to loop through
if(!empty($posts)) {

  // Start our counter at zero
  $index = 0;

  // Create a variable to keep track of whether we need to continue looping
  $continue_looping = true;

  // If we need to continue looping and the current post exists
  while($continue_looping && isset($posts[$index])) {

    // Store the current post
    $post = $posts[$index];

    // Assume there are no more posts in this month
    $more_posts = false;

    // If this particular month/year doesn't exist
    if(!isset($blog_posts[$post['month_year_slug']])) {

      // We know this is the first post in the month
      $num_posts_month = 1;

      // If we have posts for all $num_months, we know we're done
      if(count($blog_posts) >= $num_months) {

        // Remember that we're done looping now
        $continue_looping = false;
      } else {

        // Create a new month in our array and save the month/year
        $blog_posts[$post['month_year_slug']]['month_year'] = 
        $post['month_year'];

        // Assume and save that there are no additional posts
        // in the database for that month
        $blog_posts[$post['month_year_slug']]['more_posts'] = false;
      }
    } else {

      // We know this is an additional post in that month
      $num_posts_month++;
    }

    // If we should continue looping at this point
    if($continue_looping) {

      // If we need more posts for this month
      if($num_posts_month <= $num_posts) {

        // Get the permalink for this post
        $permalink = get_permalink($post['ID']);

        // Save this post in our array
        $blog_posts[$post['month_year_slug']]['posts'][$index]['post_title'] =
        '<a href="' . $permalink . '">' . $post['post_title'] . '</a>';
      } else {

        // Record that there are actually more posts
        // in the database then we can display
        $blog_posts[$post['month_year_slug']]['more_posts'] = true;
      }
    }

    // Increment post index
    $index++;
  }
}

return $blog_posts;

If you have any questions or suggestions, I’d love to hear them in the comments below!

Featured image by 2H Media.