drelgkxl93433 2010-12-20 10:06
浏览 160
已采纳

查找一段时间内每个月的第一天和最后一天

I have a task to make website article stats based on database results. I need to calculate the amount of articles was added monthly and pass these results to JavaScript stats function.

The first what I'm doing is finding the entire period in which articles have been added:

SELECT MIN(date) as startdate FROM articles LIMIT 1 

SELECT MAX(date) as enddate FROM articles LIMIT 1

Now I have the period which starts with $startDate and ends with $endDate. These variables are unix timestamps as they are in the database. The column date in database is also unix timestamp.

Next, I should find the amount of articles have been added in each month doing a query in foreach loop like:

SELECT COUNT(id) as total FROM articles 
    WHERE date > ".$startMonth." AND date < ".$endMonth

But to make that I need to find the first and the last days of each month in the period. And I stucked at this moment. So please can you please suggest me how to find these first and last days as array. I think I should make something like strtotime("+1 month",$date) in a while loop until $startDate equals $endDate. Thanks in advance.

Upd. There are total 46 articles in the database. What I am willing to have are results grouped by month:

SELECT date, COUNT(id) as total FROM `articles`
  WHERE `author`=$author
     GROUP BY date_format(date, '%Y-%m')

results: month posts 11-2010 13 12-2010 33

  • 写回答

1条回答 默认 最新

  • dongqing7789 2010-12-20 10:12
    关注

    It seems that you can achieve this using a GROUP BY, like

    SELECT date_format(`date`, '%Y-%m'), COUNT(id) as total 
    FROM articles 
    GROUP BY date_format(`date`, '%Y-%m');
    

    And you do not need the date boundaries ...

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 求差集那个函数有问题,有无佬可以解决
  • ¥15 【提问】基于Invest的水源涵养
  • ¥20 微信网友居然可以通过vx号找到我绑的手机号
  • ¥15 寻一个支付宝扫码远程授权登录的软件助手app
  • ¥15 解riccati方程组
  • ¥15 display:none;样式在嵌套结构中的已设置了display样式的元素上不起作用?
  • ¥15 使用rabbitMQ 消息队列作为url源进行多线程爬取时,总有几个url没有处理的问题。
  • ¥15 Ubuntu在安装序列比对软件STAR时出现报错如何解决
  • ¥50 树莓派安卓APK系统签名
  • ¥65 汇编语言除法溢出问题