Displaying Archives

[timmy0320]

I am having some troubles figuring out how to display an 'Archive' of my posts in my table. I'm reading the SQL manual and I'm pretty sure that I have the SQL syntax down but I'm not quite sure how to implement it into PHP exactly.

 

 

What I'm trying to do is display something resulting like below:

 

2008

  - March

    -(date)title

    -(date)title

 

 

This is what I have so far

<?php
                // check to see if just the year is set or both
                if ($month != '') {
                  $sql = "SELECT * FROM blog_posts WHERE YEAR(date) LIKE '$year' AND DATE_FORMAT(date, '%m') LIKE '$month'";
                } else {
                  $sql = "SELECT * FROM blog_posts WHERE YEAR(date) LIKE '$year'";
                }

             $res = mysql_query($sql, $connection);


            while ($row = mysql_fetch_array($res)){
               // only check if there is one
               $year = date('Y', strtotime($row['date']));
               $month = date('m', strtotime($row['date']));
               $num = mysql_num_rows($res);
               echo "".$year." (".$num.")<br>";
            }

?>

 

It's displaying:

2008 (2)

2008 (2)

 

 

I understand why it's doing that and I know this code is all jacked up but should I be doing a foreach instead of a while and how would the indentions go on this?

 

I also think that my SQL syntax is going to have to change by the way I am trying to do this.

 

Thanks for all the help.