使用 mysql 和 php 只返回一次月份和日期 - Return month and date only once using mysql and php

Return month and date only once using mysql and php

我正在创建一个帖子存档，我想为文章以年/月格式创建存档链接。

数据库中的创建日期以YYYY-MM-DD格式存储，到目前为止已经写了。

$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 />"; 
}

如果我将这些日期存储在我的数据库中

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

然后上面的代码生成链接为

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

它显示了已经添加的月份，我需要做的是将其限制为仅显示一次。

我想我需要更改查询，但不确定如何获得我需要任何帮助的结果，将不胜感激。

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

并相应地

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

你会尝试选择

DATE_FORMAT(DateCreated , '%m/%Y')

而不是"创建日期"？不确定结果，但据我所知，这应该可以解决您的问题。

问候

我认为这个查询非常适合您 - 它选择前 3 个月的字母和时间戳的年份。

SELECT 
   substr(MONTHNAME(DateCreated), 1, 3) as month, 
   YEAR(DateCreated) as year
FROM blog 
ORDER by DateCreated DESC

编辑：经过几次测试，我为您进行了完美的查询 - 它以JAN-2012格式返回日期，因此无需在 PHP 中格式化日期。在这里：

SELECT 
   CONCAT( UPPER(substr(monthname(DateCreated), 1 ,3)), '-', YEAR(DateCreated)) as date
FROM blog 
ORDER by DateCreated DESC

我的最终结果是这样的。

$archive = mysql_query("SELECT DISTINCT YEAR(DateCreated) AS YYYY, MONTH(DateCreated) AS MM FROM blog ORDER BY DateCreated") or die("Could not execute query");
                while($row = mysql_fetch_array($archive) ){
                $Year               = $row["YYYY"];
                $Month              = sprintf("%02s",$row["MM"]);
                // 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 />"; 
                }