PHP和SQL，查询自定义博客的嵌套年、月和帖子数 - PHP & SQL, querying for nested year, month, and number of posts for a custom blog

PHP & SQL, querying for nested year, month, and number of posts for a custom blog

本文关键字：嵌套 SQL 查询自定义 PHP | 更新日期: 2023-09-27

我想在一个链接中显示年，月和博客文章数量的存档列表。像这样:

…等，它将链接到一个页面，它将显示所有的帖子在那个月。

我试着:

SELECT YEAR(date) as year, MONTH(date) as month, ....

and did in php:

<ul>
  <li><?php echo $year; ?>
      <ul>
         <li><?php echo $month; ?> (<?php echo $countpost; ?>)</li>
      </ul>
  </li>
</ul>

但我只是得到一个空白页，我很确定我做了错误的查询。

add database

postid | title | date | content | userid | active

使用这些查询来获得您所描述的输出。

<?php
$con=mysqli_connect("localhost","root","","my_db");
$result=mysqli_query($con,"select distinct YEAR(date) as myyear from table");
while($fetch=mysqli_fetch_array($result))
{
echo "<b>".$fetch['myyear']."</b><br />";
$q1="select MONTH(date) as postmonth,count(*) as postcount from table where year(date)=".$fetch['myyear']." group by MONTH(date)";
$temp1=mysqli_query($con,$q1);
while($temp=mysqli_fetch_array($temp1))
echo "<b>".$temp['postmonth']."</b> (".$temp['postcount'].")<br />";
}
mysqli_close($con);
?>

希望能有所帮助。

试试这个:

SELECT YEAR(date) as year, MONTH(date) as month, COUNT(postid) as countpost
FROM postTable
GROUP BY YEAR(date), MONTH(date);

查看更多示例:SQL Fiddle