grouping archive by year and month using php and mysql -

i want create archive list this:

• 2014
  • march
  • feb
  • jan
    • post 1
    • post 2
• 2013
  • november
    • post 1

i using pdo. table m using having postdate datetime. postslug used clean url. coding using is:

    <h1>archives</h1>     <hr />      <ul> <?php $stmt = $db->query("select posttitle, month(postdate) month, year(postdate) year blog_posts_seo order postdate desc"); while($row = $stmt->fetch()){ $posts = $row['posttitle']; $year = $row['year'];     $monthname = date("f", mktime(0, 0, 0, $row['month'], 10));     $slug = 'a-'.$row['month'].'-'.$row['year'];         echo "<li>$year</li>";     echo "<ul><li><a href='$slug'>$monthname</a></li>";          echo "<ul><li><a href='#'>$posts</a></li></ul></ul>"; } ?> </ul> 

the result im getting follows:

2014     may         post 2013     june         post 2013     june         post 2012     june         post 

in short how group posts , months accordingly using php? am beginner in php , mysql. therefore of great if can me in complete coding if know solution. thanks!

how that?

$data = array(); while($row = $stmt->fetch()){   $monthname = date("f", mktime(0, 0, 0, $row['month'], 10));        $data[$row['year']][$monthname][] = array(    'post' => $row['posttitle'],    'slug' => 'a-'.$row['month'].'-'.$row['year']   ); } echo '<ul>'; foreach ($data $year => $yeardata){    echo "<li>$year<br/>";    echo '<ul>';    foreach ($yeardata $month => $monthdata){       echo "<li>$month<br/>";       echo '<ul>';       foreach ($monthdata $number => $postdata){         echo "<li><a href='${postdata['slug']}'>post $number</a><br/>";         echo "<a href='#'>${postdata['post']}</a></li>";       }       echo '</ul></li>';    }    echo '</ul></li>'; } echo '</ul>'; 

this solution php way, should able result sql query too, like:

select     year(postdate) year, month(postdate) month,     group_concat(posttitle) posts     blog_posts_seo group year, month order postdate desc 

which should return posts related year , month in single row (not tested), separated commas. use with separator option specify different separator (check doc).

documentation:

• group , group_concat