我想創建一個在 x 軸上從最小日期到最大日期連續日期的圖表；并每天計數。我有這張表：ID Date ==============1 2018-01-05 2 2018-01-053 2018-01-074 2018-01-085 2018-01-08這就是我所擁有的，但我無法獲得連續的日期$sql = "SELECT date,COUNT(*) FROM table GROUP BY date";$result = $conn->query($sql);while ($row = $result->fetch_assoc()) { $x_axis[] = $row['date']; $values[] = $row['COUNT(*)'];}所需結果的示例是（當該中間日期沒有記錄時，我想得到零）：2018-01-05 => 22018-01-06 => 02018-01-07 => 12018-01-08 => 2
1 回答
ABOUTYOU
TA貢獻1812條經驗 獲得超5個贊
像這樣做：
$finaldata = array();
$sql = "SELECT date, COUNT(*) total FROM ( SELECT * FROM Table ORDER BY date ASC) AS sub GROUP BY date";
$result = $conn->query($sql);
while ($row = $result->fetch_assoc()) {
$x_axis[] = $row['date'];
$values[] = $row['COUNT(*)'];
}
for($date = $x_axis[0]; strtotime($date) <= strtotime(end($x_axis)); ) {
$key = array_search($date, $x_axis);
$finaldata[$date] = $key === FALSE ? 0 : $values[$key];
$date = date('Y-m-d', strtotime('+1 day', strtotime($date)))
}
echo "<pre>";print_r($finaldata);die;
您的最終數據在$finaldata數組中
- 1 回答
- 0 關注
- 172 瀏覽
添加回答
舉報
0/150
提交
取消