Tutorial :Wordpress post count per month



Question:

I need to run a query in Wordpress to get how many posts there are per month including zeros.

My query right now returns the first part of what I am trying to return:

select  distinct date_format(post_date, '%y') "year",  date_format(post_date, '%b') "month",  from wp_posts  where post_type = 'post'  and post_status = 'publish'  group by date_format(post_date, '%y'), date_format(post_date, '%b')  order by date_format(post_date, '%y') desc, post_date  

It returns something like:

|   year   |   month   |   count   |  ------------------------------------  |   10     |   Jan     |     4     |  |   10     |   Feb     |     2     |  |   10     |   Mar     |     1     |  |   10     |   Apr     |     6     |  |   09     |   Jan     |     4     |  |   09     |   Feb     |     2     |  

I need to return something like:

|   year   |   month   |   count   |  ------------------------------------  |   10     |   Jan     |     4     |  |   10     |   Feb     |     2     |  |   10     |   Mar     |     1     |  |   10     |   Apr     |     6     |  |   10     |   May     |     0     |  |   10     |   Jun     |     0     |  |   10     |   Jul     |     0     |  |   10     |   Aug     |     0     |  |   10     |   Sep     |     0     |  |   10     |   Oct     |     0     |  |   10     |   Nov     |     0     |  |   10     |   Dec     |     0     |  |   09     |   Jan     |     4     |  |   09     |   Feb     |     2     |  |   09     |   Mar     |     0     |  |   09     |   Apr     |     0     |  |   09     |   May     |     0     |  |   09     |   Jun     |     0     |  |   09     |   Jul     |     0     |  |   09     |   Aug     |     0     |  |   09     |   Sep     |     0     |  |   09     |   Oct     |     0     |  |   09     |   Nov     |     0     |  |   09     |   Dec     |     0     |  

I would use rownum in Oracle, but I can't figure out the query using MySQL syntax. Any help is greatly appreciated.


Solution:1

This requires the numbers table trick -

  1. Create a table called NUMBERS:

    DROP TABLE IF EXISTS `example`.`numbers`;  CREATE TABLE  `example`.`numbers` (    `id` int(10) unsigned NOT NULL auto_increment,     PRIMARY KEY  (`id`)  ) ENGINE=InnoDB DEFAULT CHARSET=latin1;  
  2. Populate the table by running the following at least two dozen times:

    INSERT INTO NUMBERS (id) VALUES (NULL)  

That will allow you to generate a list of dates using:

SELECT DATE(DATE_ADD('2009-01-01', INTERVAL n.id MONTH)) AS dt    FROM NUMBERS n  

Now, you can left join your current query to the list of dates:

   SELECT DATE_FORMAT(x.dt, '%y') "year",            DATE_FORMAT(x.dt, '%b') "month",            COUNT(*) AS count       FROM (SELECT DATE_ADD('2009-01-01', INTERVAL n.id MONTH) AS dt               FROM NUMBERS n) x  LEFT JOIN WP_POSTS wp ON MONTH(wp.post_date) = MONTH(x.dt)                       AND YEAR(wp.post_date) = YEAR(x.dt)                       AND wp.post_type = 'post'                       AND wp.post_status = 'publish'      WHERE YEAR(x.dt) IN (2009, 2010)   GROUP BY DATE_FORMAT(x.dt, '%y'), DATE_FORMAT(x.dt, '%b')   ORDER BY DATE_FORMAT(x.dt, '%y') DESC, MONTH(x.dt)  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »