Display months from range of dates

[MikeydJR]

I have a MySQL table with data containing a date field. I need to take the start and last dates and list the months in the range. For example:

2007-08-01
2007-08-25
2007-09-15
2007-09-28
2007-10-06
2007-10-25

Would list:

Oct 2007
Sept 2007
Aug 2007

These will be links to a list of data from each month, so I think I need the year-month string (2007-08, 2007-09, etc) also.

Results are displayed on a PHP page. I'm not sure if it's best to handle this is the MySQL Query, or in the PHP page.

Please see my signature for MySQL and PHP versions.

Apache: 2.0.52 | MySQL: 4.1.20 | PHP: 4.3.9 | MS Access 2002 | WinXP SP2

 

[towerbase]

Something like this in MySQL should give you what you need:

SELECT DATE_FORMAT( datum, '%b %Y' ) FROM table
GROUP BY YEAR(datum), MONTH(datum)
ORDER BY YEAR(datum) DESC, MONTH(datum) DESC

If you need the numeric format for the month then use

DATE_FORMAT( datum, '%Y-%m' )

Andrew
Hampshire, UK

 

[MikeydJR]

Thanks, that's perfect. Here's my final code:

SELECT DATE_FORMAT(datum, '%b %Y' ) as viewmonth, DATE_FORMAT(datum, '%Y-%m' ) as linkmonth FROM  table
GROUP BY YEAR(datum), MONTH(datum)
ORDER BY YEAR(datum) DESC, MONTH(datum) DESC

Apache: 2.0.52 | MySQL: 4.1.20 | PHP: 4.3.9 | MS Access 2002 | WinXP SP2