使用PHP定期检索mysql统计数据，包括空数据 - Retrieving mysql statistical data using PHP regular intervals and including null data

Retrieving mysql statistical data using PHP regular intervals and including null data

所以我试图建立一些很好的统计显示为我的应用程序。我可以这样做，因为我保持命中统计表。它只是跟踪点击，加上一些其他的数据和它发生的时间。我可以查询数据库，以显示在特定日期或过去x天内每天发生了多少次点击，如下面的代码所示。但是，下面的代码只返回有数据的日期。我想显示最近30天的点击量，而不管这一天是否有点击。想法吗?

SELECT DATE(time) AS theday, COUNT( * ) AS thecount
FROM stats
WHERE time <= curdate( )
AND time >= DATE_SUB( curdate(), INTERVAL 30 DAY )
GROUP BY theday ORDER BY time DESC

生产

theday  thecount
2011-11-22  5
2011-11-21  9
2011-11-18  10
2011-11-16  1
2011-11-11  2
2011-11-10  15
2011-11-09  2
2011-10-26  1
2011-10-24  6

可以看到，它跳过没有结果的日期。我理解为什么会这样，因为在数据库中没有这些日期。我想知道我将如何产生一个查询，几乎像上面的工作，但有所有的日期说的间隔。IE:最近30天

你有三个选择:

尝试在应用程序逻辑(php)中迭代日期
生成一个(临时)表，其中包含您需要的日期并与之左连接

应用逻辑实现示例:

<?php
    date_default_timezone_set('Europe/Paris');
    $startdate = strtotime('2011-11-01 00:00:01');
    $days = 60;
    $found_data = array( // this is generated by 1 mysql query
        array('date_field' => '2011-11-02', 'count' => 5),
        array('date_field' => '2011-11-03', 'count' => 1),
        array('date_field' => '2011-11-04', 'count' => 6),
        array('date_field' => '2011-11-08', 'count' => 9),
        array('date_field' => '2011-11-09', 'count' => 3),
        array('date_field' => '2011-11-10', 'count' => 5),
        array('date_field' => '2011-11-12', 'count' => 1),
        array('date_field' => '2011-11-15', 'count' => 1),
        array('date_field' => '2011-11-18', 'count' => 4),
        array('date_field' => '2011-11-21', 'count' => 9),
        array('date_field' => '2011-11-23', 'count' => 1),
        array('date_field' => '2011-11-28', 'count' => 8),
        array('date_field' => '2011-11-30', 'count' => 6),
    );
    foreach ($found_data as $counts) { // we convert the results to a usable form, you can do this in the query, too
        $count_info[$counts['date_field']] = $counts['count'];
    }
    for ($i = 0; $i <= $days; $i++) {
        $date = date('Y-m-d', $startdate+$i*60*60*24);
        printf("%s't%s'n", $date, array_key_exists($date, $count_info) ? $count_info[$date] : 0);
    }
?>