按年和月排序的PHP MYSQL博客存档菜单 [英] PHP MYSQL Blog Archive Menu by Year and Month

问题描述

我正在寻找一种有效的方法来将所有博客文章整理到以下格式的菜单中:

I'm looking for an efficient way to collate all blog posts into a menu of the following format:

2012

• 8月(6)
• 9月(4)
• 10月(2)

月份表示月份(显然)，括号内的值表示该月份的帖子数.单击后，将搜索该年该月的所有帖子.

Month representing the month(obviously), and the value inside the brackets representing the number of posts in that month. Once clicked, a search will then be made for all posts in that month, in that year.

我需要它是动态的，在当月创建帖子时自动选择11月，并持续到12月，2013年等...

I need it to be dynamic, picking up November automatically when a post is created in that month, and carrying on into December, into 2013 etc etc...

我所拥有的只是每个帖子的UNIX时间戳.我真的很想避免使用单独的函数来收集无尽的复杂数组等.

All I have is a UNIX timestamp for each post. I would really like to avoid using seperate functions to gather endless comlex arrays etc.

非常感谢您的帮助.

推荐答案

从您的问题开始，我了解到您正在尝试提出一个查询，以按月和年对多个元素进行分组.以下应该可以解决问题:

From your question, I understand you're trying to come up with a query to group a number of elements by month and year. The following should do the trick:

SELECT 
    YEAR(dateField) AS YEAR, 
    MONTH(dateField) AS MONTH,
    COUNT(*) AS TOTAL 
FROM table 
GROUP BY YEAR, MONTH

很明显，"dateField"是日期时间/时间戳列的名称，"table"是表的名称.

Obviously, "dateField" being the name of your datetime/timestamp column and "table" being the name of your table.

有关GROUP BY子句和聚合函数(例如上面使用的COUNT(*)函数)的更多信息

More information on the GROUP BY clause and aggregate functions (such as the COUNT(*) function used above) here.

这篇关于按年和月排序的PHP MYSQL博客存档菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！