Hello,
I have a table that handles blog entries. I'd like to make SQL query that able to list achieve listing by dates, something like the following:
2007
January (82)
December (12)
2008
February (5)
April(55)
2009
March (18)
and so on.
The PHP scripting to do this task is not the problem. The problem is found in the SQL query result should I get. I will simplify the blog table as following:
id add_date approval
1 2007-12-10 00:00:01 Y
2 2007-12-15 22:15:55 Y
3 2008-02-18 10:10:06 Y
4 2007-12-30 11:25:44 Y
5 2009-02-15 15:52:07 Y
6 2008-06-14 11:22:33 Y
....
I used the following SQL query, but after some time I discovered that it is buggy,
SELECT YEAR(add_date) AS add_year, MONTH(add_date) AS add_month, COUNT(id) AS num_items
FROM blog
WHERE approval = 'Y'
GROUP BY add_month
ORDER BY add_year ASC
I respect from the previous query to return results as:
add_year add_month num_items
2007 12 3
2008 02 1
2008 06 1
2009 02 1
....
The first time I run this script, everything worked fine, but when entries varied and take wide range of dates, it seems that It could not able to catch some months.
A working example for this is found http://www.azzazianesthesia.com , on the right column you will notice box named Anesthesia Wall Archives, it is stopped at march 2009 in spite of being other posts on other months later.
I need to know what's the mistake in the query I use?
