How to display data using this method?

<?php include("db.php");
$result = mysql_query("SELECT YEAR(local_date) AS year ,MONTH(local_date) AS month ,count(ID) AS posts FROM tbl_localnews
        GROUP BY YEAR(local_date), MONTH(local_date) ORDER BY local_date DESC");
if(mysql_num_rows($result))
	{
while ($row = mysql_fetch_array($result)) {
	print("<br><a href='news.php?year=".$row['year']."&month=".$row['month']."'>News for ".$row['month'].",".$row['year']."</a>");	
 }
}
?>

What is the wrong with this guys? Why is it that when i view the month of November 2010 it also view the month of 2009?Can anyone help me?

<?php include("db.php");

$query = mysql_query("SELECT distinct YEAR(local_date) as year FROM tbl_localnews order by local_date desc");
while($row = mysql_fetch_array($query)) {
      //$row = array_unique($r);
      $unique_year = $row['year'];
      echo($unique_year)."<br>";
      
      $query2 = mysql_query("select distinct monthname(local_date) as month from tbl_localnews where local_date like '$unique_year%' order by local_date desc");
      while($r2 = mysql_fetch_array($query2)) {
              //$row2 = array_unique($r2);
            $unique_month = $r2['month'];
			print("<a href='news.php?month=$unique_month'&year=$unique_year>News for $unique_month</a><br>");	
              //echo($unique_month); 
      }

}
?>

try this method

SELECT YEAR( post_date ) AS `year` ,
            MONTH( post_date ) AS `month` ,
                count( ID ) AS posts
    FROM posts_table
        GROUP BY YEAR( post_date ) , MONTH( post_date )
             ORDER BY post_date DESC

this will show in example:

year  |  month  |  posts
2010  |  8      |  15
2010  |  9      |  11

this is the wordpress method to show the full archive but if you want a simple method like show all the posts under each month like:

2009

• 12.11. Title
• 03.03. Title
• 01.01. Title

2008

• 11.12. Title
• 04.03. Title
• 02.03. Title
• 16.02. Title

and this is that metohd with the smarty

$rows = $db->query(\'select year, title from news\');
$smarty->assign(\'news\', $rows);

{if count($rows) > 0}
  {assign var=lastYear value=0}
  <ul><li>
  {foreach name=foo from=$rows item=item}
    {if $smarty.foreach.foo.first}
      {$row.year}<ul>
    {elseif $lastYear != $row.year}
      </ul></li><li>{$row.year}<ul>
    {/if}
    {assign var=lastYear value=$row.year}
    <li>{$row.title}</li>
  {/foreach}
  </ul></li></ul>
{/if}

The other one is to group the values in php:

$rows = $db->query(\'select year, title from news\');
$news = array();
foreach($rows as $row) {
  $news[$row[\'year\']][] = $row[\'title\'];
}

$smarty->assign(\'news\', $news);

<ul>
{foreach from=news key=year item=rows}
  <li>{$year}<ul>
  {foreach from=$rows item=row}
    <li>{$row}</li>
  {/foreach}
  </ul></li>
{/foreach}
</ul>

what about

GROUP BY YEAR(record_date), MONTH(record_date)

Check out the date and time functions in MySQL.