Archive by year and month

I'm trying to build a year\month archive.

It refuses to output what i want so i'm hoping one of you might push me in the right direction.

I want to sort by year, and show the months for each year ( if exists) and output it like this:

2012
 - June
 - August 
2011
 - July

my code is this :

$query = "SELECT * FROM article WHERE full_name ='$safe_name' group by year, month";
$result = mysql_query($query) or die(mysql_error());
while($row = mysql_fetch_assoc($result)) {
$year = $row['year'];
$month = $row['month'];
echo "<ul class='year'><li><a>{$year}</a>";
echo "<ul class='months'><li><a>{$month}</a></li></ul>
</li></ul>";
}

But it outputs :

2012
 - june

2012
 - august

2011
 - july

When i group only by year, it will output:

2012
 - june
2011
 - july

i have a "date_posted" row which is datetime (yyyy-mm-dd 00:00:00). On top of that i have 1 row for month and 1 for year ( i know that's dumb, but i couldnt figure out how to do this by just using the "date_posted" row.

Ive read up on some posts on this topic, but its not doing the trick for me. Any help is greatly appreciated!

Solution

This should work. When fetching results, sometimes it's handy to restructure it to be easily usable in iterating a view.

$query = "SELECT * FROM article WHERE full_name ='$safe_name' group by year, month";
$result = mysql_query($query) or die(mysql_error());

while($row = mysql_fetch_assoc($result)) {

   // Get data
   $year = $row['year'];
   $month = $row['month']

   // Structure it
   $archive[$year][] = $month;

}

// Display data
foreach($archive as $year => $months)
{
   // Show year
   echo "<ul class='year'><li><a>{$year}</a>";

   // Month container
   echo "<ul class='months'>"

   // Get months
   foreach($months as $month)
   {
     echo("<li><a>{$month}</a></li>"; 
   }

   // Close Month/Year containers
   echo("</ul></li></ul>");
}