News Archive in PHP and MYSQL displaying zero results

Question

I am trying to list all the previous months in which posts have been made and display the number of posts, for example

2011
  November (14)
  October (12)
  April (3)
2010
  December (2)

    etc etc

The following code is how I am attempting to make this work however it doesnt actually return any results and I cannot seem to wrap my head around why.

$sql = "SELECT nid, ndate FROM weaponsnews
    ORDER BY ndate DESC";
$result = $db->query($sql);

$data = array();

while($row = $result->fetch_assoc())
{
    $year = date('Y', strtotime($row['ndate']));
    $month = date('m', strtotime($row['ndate']));

    $data[$year][$month][] = $row;
}
$result->free();

foreach($data as $_year => $_months)
{
    echo $_year. "<br>";
    foreach($_months as $_month => $_entries)
    {
        $mentries = count ($_entries);
        echo $_month. " (" .$mentries. ")";

    }
}

Answer 1

It's easier if you do the count in the MySQL query. For example:

SELECT YEAR(`ndate`) AS 'year', MONTH(`ndate`) AS 'month', COUNT(`nid`) AS 'count'  FROM `weaponsnews ` GROUP BY YEAR(`ndate`), MONTH(`ndate`) DESC

This gives you three columns, where the first is the year, the second is the month, and the third is the number of items in that month. You can then easily iterate through this list with foreach. Something like:

$data = array();
while($row = $result->fetch_assoc()) {
    $data[$row['year']][$row['month']] = $row['count'];
}
$result->free();

This should give you an array like:

$data = array(
    '2001' => array(
        '1' => '83',
        '3' => '102'
    ),
    '2012' => array(
        '6' => '43',
        '9' => '33'
    ),
    '2013' => array(
        '7' => '55'
    )
);

You can print the tree by iterating through the array:

foreach ($data as $year => $months) {
    echo $year.'<br>';
    foreach ($months as $month => $count) {
            echo $month.'('.$count.')<br>';
    }
}