Sidebar for my archive..group by

[RonKevinT.Manuela]

/*Ok so I have this code and it works fine but the problem is that it gives an out put like this one:

2014
    June
2014
    August
Supposedly I would like to have august just below June...I tried putting the Group by Year(Postdate)..and it almost gave me the solution but the month AUGUST is missing...like this:

2014
    June*/



      <?php
        $stmt = $db->query("SELECT Month(postDate) as Month, Year(postDate) as Year FROM posts ORDER BY postDate");
        while($row = $stmt->fetch()){
            $monthName = date("F", mktime(0, 0, 0, $row['Month'], 10));
            $slug = 'a-'.$row['Month'].'-'.$row['Year'];
            echo"<li>$row[Year]</a><ul class='sub1'><li><a href='$slug'>$monthName</a></li></ul></li>";
        }
        ?>  

[cereal]

You can use group_concat():

SELECT group_concat(DISTINCT MONTH(postDate) ORDER BY postDate) as months, YEAR(postDate) as year FROM posts GROUP BY year;

with DISTINCT will return unique values, then you can explode the values in the month column and loop them in the sub list:

while($row = $stmt->fetch())
{
    $months = explode(',', $row['months']);
    echo '<li>' . $row['year'];
    echo '<ul class="sub1">';

    foreach($months as $month)
    {
        $slug = 'a-'.$month.'-'.$row['year'];
        $monthName = date("F", mktime(0, 0, 0, $month, 10));

        echo "<li><a href='$slug'>$monthName</a></li>";
    }

    echo '</ul>';
    echo '</li>';

}

Otherwise you can loop the results into an array of years as keys and months as arrays, to create the same of below and then print the result:

$rows[2013] = array(3,5,6);
$rows[2014] = array(2,3,7,8);

[RonKevinT.Manuela]

i see..thanks! :3