News articles grouped by month and year

Here is how I have done that in the past. Let's say you your date field is called CAPTDATE and your table is called test.  Here is the query below.  Basically you are using MySQL to group for you.

SELECT year(CAPTDATE) as year, month(CAPTDATE) as month, CAPTDATE FROM test GROUP BY year(CAPTDATE), month(captdate) ORDER BY CAPTDATE DESC.

PHP and MySQL both have a lot of built in functions for working with dates.

Here is a little code example from one of my projects that is similar to what you want to do. this example uses PDO, but the query is the same if you are using the older mysql extension. the last line builds an array of the result. You may not need this.Also note that the month and year are returned from the query using aliases (month, year); They could just as well be called "mymonth' and myyear, etc. The second to the last like formats the result for display.

$sql=$dbh->prepare("SELECT DISTINCT EXTRACT(YEAR FROM(startdate)) AS year, EXTRACT(MONTH FROM (startdate)) as month FROM courses WHERE startdate > $currentdate order by startdate");

$sql->execute();

while ($monthorder = $sql->fetch(PDO::FETCH_ASSOC)){

$month = $monthorder['month'];

$year = $monthorder['year'];

$monthtext = date('F Y', mktime(0,0,0,$month,1,$year));

$monthlist[]=array('month'=>$month, 'year'=>$year);

Since you want to link to articles, you then need a second query that will retrieve those details based on whatever month is selected. something like this:

("SELECT courses.course_id, title, startdate, enddate, highlight_photo FROM courses WHERE startdate > '$currentdate' AND EXTRACT(YEAR FROM (startdate)) = '$year' AND EXTRACT(MONTH FROM (startdate)) = '$month' ORDER BY startdate")

It depends how many articles you expect to have and how you want to display them.

If there aren't many articles, just select everything using ORDER BY fld_articledate DESC. You can then display them using some PHP conditional logic.

Alternatively, you can create individual queries

SELECT * FROM articles

WHERE YEAR(fld_articledate) = 2012 && MONTHNAME(fld_articledate) = 'June'

ORDER BY fld-articledate DESC