按照年和月计算文章后档案

Question

i need to list total post article archive by years and months like this :

Output(my need):

2014
   January(31)
   February(28)
   March(0)
   April(130)
   May(450)
   June(0)
   July(0)
   August(0)
   September(0)
   October(520)
   November(20)
   December(31)

PHP:

$sql = "SELECT title, YEAR(FROM_UNIXTIME(timestamp)) AS YEAR, 
                MONTHNAME(FROM_UNIXTIME(timestamp)) AS MONTH, 
                COUNT(*) AS TOTAL 
         FROM article GROUP BY YEAR, MONTH ORDER BY YEAR DESC, MONTH ";

$newsdata = DB->fetch($sql);

$currentYear = null;

foreach($newsdata AS $news){            
  if ($currentYear != $news['YEAR']){
     echo '<ul>'.$news['YEAR'].'</ul>';
  $currentYear = $news['YEAR'];
  } 
     echo '<li>'.$news['MONTH'].' '.$news['TOTAL'].'</li>';
  }

My code worked but print only month if posted article in this month.

Ouput:

2014
       January(31)
       February(28)
       April(130)
       May(450)
       October(520)
       November(20)
       December(31)

I need to list all month and print total article for each month. if month not posted article print (0) for this month.

how do can i fix my problem? my code/way is true?!

Answer 1

Your database query won't return a row for a month that has no articles. It doesn't know anything about months. So you need to handle this in code by having an array of all months and looping through that. The alternative would be to alter your schema, have another table with all 12 months, and join on that. But I think the following is easier for you:

$months = array( "January", "February", ...  );

Then you can do the following in your output instead:

// Index article counts by month and year for easy lookup
$indexedNewsData = array();
foreach ($newsdata as $news) {
  $indexedNewsData[$news['YEAR']][$news['MONTH']] = $news['TOTAL'];
}

// Then print output
foreach($newsdata AS $news){            
  if ($currentYear != $news['YEAR']){
    echo '<ul>'.$news['YEAR'].'</ul>';
    $currentYear = $news['YEAR'];
  } else {
    // Continue here otherwise we will print each year's data 12x
    continue;
  }
  foreach ($months as $month) {
    $total = intval($indexedNewsData[$news['YEAR']][$month]);        
    echo '<li>'.$month.' '.$total.'</li>';
  }     
}