为我的博客网站创建存档

Question

我的数据库表类似于：

id    year   month 
1     2011   november
2     2011   november
3     2011   october 

我需要创建一个查询，以便它返回如下内容：

2011
    november
    november
    october 

在php脚本中正确的查询语法是什么？

下面是我使用的代码：

<?php

$uid = $_SESSION['uid'];
$sql = "SELECT year, GROUP_CONCAT(month) AS months FROM articles GROUP BY year";
$res = mysql_query ($sql) or die (mysql_error());
if (mysql_num_rows($res) > 0) {
    while ($rows = mysql_fetch_assoc($res)) {
        foreach ($rows AS $row) {
            echo $row['year'] . "<br>";
            $months = explode(",", $row['months']);
            foreach ($months AS $m) {
                echo $m . "<br>";
            }
        }
    }
}
?>

Answer 1

您可以使用GROUP_CONCAT()返回逗号分隔的月份列表：

SELECT year, GROUP_CONCAT(month) AS months GROM tbl GROUP BY year

Returns:
2011  november,november,october

或者只选择这两列并处理代码中的显示/表示：

SELECT year, month FROM tbl WHERE year = 2011

Returns
2011   november
2011   november
2011   october

在代码中，循环并仅在年份发生变化时显示年份。

更新，因为代码示例看起来很有必要...

// Results from above GROUP_CONCAT() query already fetched & stored in `$rowset`:    
while ($row = mysql_fetch_assoc($res)) {
   $rowset[] = $row;
}

// Now $rowset is a 2D array containing all rows.
foreach ($rowset as $row) {
  // Output the year
  echo $row['year'] . "\n";

  // months are comma-separated via GROUP_CONCAT()
  // explode them into an array
  $months = explode(",", $row['months']);
  foreach ($months as $m) {
    // Output the months
    echo $m . "\n";
  }
}

Answer 2

如果我没理解错你的问题，我想一个简单的"ORDER BY“子句就能帮你解决这个问题，类似于：

SELECT * FROM table_name ORDER BY year DESC

然后，使用脚本语言显示数据，一个好主意可能是(也许)获取第一行的年份，将其存储在时间变量中，然后循环各行并检查年份是否已更改，如果已更改，则更改上述变量的值，如下所示：

$current_year = $data_set[0]['year'];
foreach ( $data_set as $data_row )
{
     $current_year = ( $data_row['year'] != $current_year) ? $data_row['year'] : $current_year;
     //Do something with it and/or the rest of the data
}

希望这能对你有所帮助。

干杯。