php - 从数据库获取每日销售额-没有销售额的天数应显示0-最近30天的销售额

我需要为过去30天的销售额创建一个统计图表。有几天没有销售，所以这几天我必须显示0。
MySQL数据库

SELECT COUNT(*) as total, DAY(FROM_UNIXTIME(tmstmp)) AS soldDay
FROM tl_voucher_create
WHERE sold = 1
GROUP BY soldDay
ORDER BY tmstmp DESC 0,30

结果的PHP数组

Array
(
    [0] => Array
        (
            [total] => 1
            [soldDay] => 24
        )

    [1] => Array
        (
            [total] => 1
            [soldDay] => 13
        )

    [2] => Array
        (
            [total] => 4
            [soldDay] => 3
        )

    [3] => Array
        (
            [total] => 2
            [soldDay] => 23
        )

使用循环，我将值放入一个新数组：

for ($x = $day; $x < 31; $x++) {
    if (isset($this->_["soldDaily"][$x]["soldDay"])) {
        $total[$x] = $this->_["soldDaily"][$x]["total"];
    } else {
        $total[$x] = 0;
    }
}

如何以正确的顺序正确输出过去30天的值？
提前谢谢

最佳答案

根据实际日期查询：

SELECT COUNT(*) AS total, DATE(FROM_UNIXTIME(tmstmp)) AS sold_date
FROM tl_voucher_create
WHERE sold = 1
  AND sold_date BETWEEN DATE(NOW() - INTERVAL 30 DAY) AND CURDATE()
GROUP BY sold_date
ORDER BY sold_date DESC

假设查询的结果如下所示：

$data = [
    [
        'total' => 7,
        'sold_date' => '2017-02-28'
    ],
    [
        'total' => 19,
        'sold_date' => '2017-02-27'
    ],
    [
        'total' => 8,
        'sold_date' => '2017-02-24'
    ],
    [
        'total' => 5,
        'sold_date' => '2017-02-22'
    ],
    [
        'total' => 12,
        'sold_date' => '2017-02-21'
    ]
];

现在，您需要将PHP基于一个实际的日期对象，而不仅仅是一个数字30循环，否则您将在报告中遇到各种问题和不准确的地方，因为日期并不像从1到30循环那样简单（月有不同的天数，您不能轻易地回到上一个月，夏令时问题，不同时区的日期不同，等等）

// Organise the array by date
$dates = [];
foreach ($data as $day) {
    $dates[$day['sold_date']] = $day['total'];
}

// Loop through the last 30 days and match each iteration with the data
$d = new DateTime();
for ($i = 0; $i < 30; $i++) {

    $date = $d->format('Y-m-d');

    // If there's no data for the specified date, use zero
    $total = isset($dates[$date]) ? $dates[$date] : 0;

    echo '<p>' . $total . ' sold on ' . $date . '</p>';

    $d->modify('-1 day');
}

关于php - 从数据库获取每日销售额-没有销售额的天数应显示0-最近30天的销售额，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/42528750/