Заполнить не существующими датами из бд в графике apexcharts?

Question

[stastop]

Делаю график продаж, вроде всё хорошо, но что-то не так(я сделал 16.05 для наглядности):


Да, тут нету дат с 16.04 по 16.05 тк записей нету.
Как сделать вывод что за эти даты 0 продаж?
Скрипт:

<div id="chart">
</div>

<script src="https://cdn.jsdelivr.net/npm/apexcharts"></script>
<script>
<?
$sql = 'SELECT count(`id`) FROM `sells` WHERE id != 1 GROUP BY DATE(datetime)';

$result = $conn->query($sql);
echo "var values=[";
while($row = mysqli_fetch_array($result))
{
echo $row["count(`id`)"].",";
}
echo "];";

$sql = 'SELECT datetime FROM `sells` WHERE id != 1 GROUP BY DATE(datetime)';

$result = $conn->query($sql);
echo "var rows=[";
while($row = mysqli_fetch_array($result))
{
$dt = explode(" ", $row["datetime"]);
echo "'".$dt[0]."', ";
}
echo "];";
?>

var options = {
    chart: {
        height: 280,
        type: "area"
    },
    dataLabels: {
        enabled: false
    },
    series: [
        {
            name: "Кол-во продаж",
            data: values
        }
    ],
    fill: {
        type: "gradient",
        show: false,
        gradient: {}
    },
    xaxis: {
        categories: rows
    }
};

var chart = new ApexCharts(document.querySelector("#chart"), options);

chart.render();
</script>

Comments

[Ипатьев]

Какой ужас. Где же такой говнокод учат писать? зачем тут ДВА запроса? где json_encode()?

Answer 1

[Ипатьев]

Принцип заполнения пустых дат всегда один и тот же:

• получаем данные запроса в массив, который индексируем датой
  
• создаем итоговый массив в цикле, подставляя значение из БД, если оно существует
  

// получаем
$sql = 'SELECT DATE(datetime) `date`, count(id) `count` FROM sells WHERE id != 1 GROUP BY DATE(datetime)';
$result = $conn->query($sql);
$sales = [];
// индексируем
while($row = mysqli_fetch_array($result)) {
    $sales[$row['date']] = $row['count'];
}
// создаем нужный интервал
$begin = new DateTime($date_begin);
$end = new DateTime($date_end);
$interval = DateInterval::createFromDateString('1 day');
$period = new DatePeriod($begin, $interval, $end);
// заполняем итоговый массив
$result = [];
foreach ($period as $day) {
    $date = $day->format("Y-m-d");
    $result[$date] = $sales[$date] ?? 0;
}
// выводим
echo "var rows=".json_encode(array_keys($result));
echo "var values=".json_encode(array_values($result));

Даты начала и конца обычно задаются заранее. Но если их надо получить из результатов запроса, то

$date_begin = array_key_first($sales);
$date_end = array_key_last($sales);

Comments

[stastop]

У вас ошибка:

$result = []

; забыли

Answer 2

[nokimaro]

На SQL можно использовать CTE и конкретно WITH RECURSIVE чтобы сперва сгенерировать интервалы дат с любым шагом и потом сделать JOIN и агрегацию интересующих данных
Возможны проблемы с производительностью запроса при отсутствии правильных индексов.

https://dev.mysql.com/doc/refman/8.0/en/with.html

with recursive cte as (
    select 
        '2024-04-16' dt, 
        '2024-05-16' end_dt
    union all
    select dt + interval 1 day, end_dt from cte where dt < end_dt
)
select c.dt, count(t.id)
from cte c
left join sells t on DATE(t.datetime) = c.dt
group by c.dt