News articles grouped by month and year

[matthew stuart]

PHP and MySQL...

Don't know if I am going to get an answer here, but I am looking for a bit of advice on how to handle grouping of news articles into months and each then into the respective year to make it easier for the user to navigate content. I am trying to acheive this effect:

2012

- July

- June

- March

- January

2011

- December

- October

- April

2010

- November

- You get the idea I hope

I have set up MySQL to use a 'date' field called fld_articledate (formatted as YYYY-MM-DD). I need to structure my recordset query to select by year and group it, but then I also need to group by month relative to that year, but I have no idea of where to begin. So I guess my question is two-part:

• How do I group by year?
• How do I then group by month of that year?

Once I have my queries sorted, I assume I'd print to page with  repeat and nested repeat regions!

Thanks.

Mat

[David_Powers]

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

[Rob Hecker2]

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")

[nstratton]

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.