How to create a archive list from an array of dates in php

Question

I am looking to create a list in the following format:

2012 (2) 
- January (1) 
- March (1) 
2011 (1) 
- March (1)

from am array of dates in the following format:

Array
(
    [0] => Array
        (
            [year] => 2012
            [month] => 3
        )

    [1] => Array
        (
            [year] => 2012
            [month] => 1
        )

    [2] => Array
        (
            [year] => 2011
            [month] => 3
        )

)

this list is provided from the following query, and i'm open to suggestions as how to return the data as well. This was the most logical step for me.

SELECT YEAR(post_date) as `year`, MONTH(post_date) as `month` FROM posts ORDER BY post_date DESC

I'm at a dead end here. I don't know if I'm not seeing something obvious or if I've made this too complicated but I can't figure out where to go from here.

Answer 1

Assuming you might have multiple rows with the same month/year:

# first sort into a 2-level array of post counts
$by_year = array();
$year_counts = array();
foreach ($rows as $row){
    $by_year[$row['year']][$row['month']]++;
    $year_counts[$row['year']]++;
}

# most recent year first!
krsort($by_year);

# and now iterate for display
foreach ($by_year as $year => $months){

    echo "<b>$year</b> ($year_counts[$year])<br />\n";
    echo "<ul>\n";

    # sort jan->dec
    ksort($months);
    foreach ($months as $month => $num){

        $name = date('F', mktime(0,0,0,$month,1,2010));
        echo "<li> $name ($num) </li>\n";
    }

    echo "</ul>\n";
}

This works as you requested, with this input data:

$rows = array(
    array('year' => 2012, 'month' => 3),
    array('year' => 2012, 'month' => 3),
    array('year' => 2012, 'month' => 2),
    array('year' => 2011, 'month' => 1),
);

It outputs this:

2012 (3)
 * February (1)
 * March (2)
2011 (1)
 * January (1)

*updated to show year counts too

Answer 2

You can then perhaps build the array like so:

$someArray = Array();
$query = mysql_query(...);

while (($row = mysql_fetch_assoc($query)) != NULL)
{
    $someArray[] = Array("year" => $row['year'], "month" => $row['month']);
}

In this instance, $someArray[] is notation for "append this element onto the array".

Answer 3

I'm guessing you're trying to create a list of how many articles per month, shown hierarchically by year?

Neil's answer shows how to get results from the database. If you take that as a starting point, then you can iterate over the array and construct your list with counts, then print out the list.

You could also have MySQL count the number of posts per month with a group by statement.

select year(post_date) as `year`, month(post_date) as `month`, count(*) as `count`
from posts
group by concat(year(post_date),month(post_date))
order by post_date desc

Then with something similar to Neil's code, but making some nested arrays for later convenience, you get:

$counts = array();
$query = mysql_query(...);

while (($row = mysql_fetch_assoc($query)) != null) {
    $counts[ $row['year'] ] = array("month" => $row['month'], 'count' => $row['count']);
}

And then print that out as HTML:

echo "<ul>";
foreach($counts as $year => $months) {
    echo "<li>$year <ul>"
    foreach($months as $row) {
        $month = name_for_month($row['month']); //TODO: make this method
        echo "<li>$month ({$row['count']})</li>";
    }
    echo "</ul></li>";
}
echo "</ul>";

Hmm, you also had total count next to the year. Well, you could add something like:

$yearSums = array();
$counts = array();
$query = mysql_query(...);

while (($row = mysql_fetch_assoc($query)) != null) {
    $counts[ $row['year'] ] = array("month" => $row['month'], 'count' => $row['count']);
    $yearSums[ $row['year'] ] += $row['count'];
}

and then print that data out too.

Disclaimer: no guarantee that this is GOOD code, this is just one quick example of how you could do it.