使用mysql和php只返回一次月份和日期

Question

I am creating a archive of posts which I want to create archive links for when the articles were created in year / month format.

Created dates in database are stored in YYYY-MM-DD format and have written this so far.

$archive =
  mysql_query("SELECT DISTINCT DateCreated FROM blog ORDER by DateCreated")
  or die("Could not execute query");

while($row = mysql_fetch_array($archive) ){ 
  $Date = explode("-", $row["DateCreated"]);
  $Year = $Date[0];
  $Month = $Date[1];

  // Months of the year.
  $MonthName = array(
    "01" => "JAN",
    "02" => "FEB", 
    "03" => "MAR",
    "04" => "APR",
    "05" => "MAY",
    "06" => "JUN",
    "07" => "JUL",
    "08" => "AUG",
    "09" => "SEP",
    "10" => "OCT",
    "11" => "NOV",
    "12" => "DEC");    

 $archiveData .=
   "<a href='archive.php?".$Year."-".$Month.
   "'>".$MonthName[$Month]."-".$Year."</a><br />"; 
}

If I have these dates stored in my database

2012-04-07, 
2012-05-02, 
2012-05-13, 
2012-02-22, 

Then the above code produces links as

FEB-2012, 
APR-2012, 
MAY-2012, 
MAY-2012, 

It shows the months that have already been added, what I need to do is limit it to show it only once.

I think I need to change the query but not sure how to get the result I require any help would be appreciated.

Answer 1

SELECT DISTINCT
  YEAR(DateCreated) AS yyyy,
  MONTH(DateCreated) AS mm
FROM blog 
ORDER BY DateCreated

and accordingly

$Year = $row["yyyy"];
$Month  = $row["mm"];