Получите данные SQL по отдельности за дни с настоящего момента до 30 дней назад
Я строю аналитическую диаграмму, где мне нужно отображать количество посещений за каждый день за последние 30 дней. Я пытался использовать приведенный ниже цикл, но он не может получить определенные данные и значительно замедляет время загрузки страницы из-за отправки 30 запросов в одном запросе.
Есть ли лучшие способы получить эти данные из базы данных MySQL, сохранив при этом ресурсы сервера и улучшив время загрузки страницы? Любая помощь приветствуется.
$conn = new mysqli("localhost", "db_host", "db_pwd", "db_name");
$d2 = date('c', strtotime('-29 days'));
$date = date("Y-m-d");
$begin = new DateTime($d2);
$end = new DateTime($date);
$end = $end->modify('+1 day');
$interval = new DateInterval('P1D');
$daterange = new DatePeriod($begin, $interval ,$end);
foreach($daterange as $date){
$current = $date->format("Y-m-d");
$sql = "SELECT COUNT(*) FROM stats WHERE link_id=$_GET[id] AND date='$current'";
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
while($row = mysqli_fetch_assoc($result)) { echo $row["COUNT(*)"]; }
} else { echo "no results";}
}
$conn->close();
Да, вы всегда должны использовать их. Почему вы когда-нибудь решили не делать этого?
Ответы 1
Если у вас есть хотя бы одно посещение в день, вы можете просто использовать агрегацию:
select date(dt) as dt_day, count(*) as cnt_visits
from stats
where link_id = ? and dt >= current_date - interval 29 day
group by date(dt)
order by dt_day
Наличие столбца с именем date как-то вводит в заблуждение (хотя MySQL это позволяет). Для ясности я переименовал столбец в dt в запросе.
Если есть дни без посещений, а вы все равно хотите отобразить их с подсчетом 0 посещений, то это немного сложнее. Сначала нужно сгенерировать даты, а затем left join таблицу. Обычно вы используете таблицу календаря — или, в MySQL 8.0, вы можете сделать это на лету с помощью рекурсивного общего табличного выражения:
with recursive cte as (
select current_date as dt_day
union all
select dt_day - interval 1 day from cte where dt_day > current_date - interval 29 day
)
select c.dt_day, count(s.dt) as cnt_visits
from cte c
left join stats s
on s.link_id = ?
and s.dt >= c.dt_day
and s.dt < c.dt_day + interval 1 day
group by c.dt_day
order by c.dt_day
У меня будут дни, когда не будет посещений, не сработает ли этот метод? Есть ли способы разместиться на некоторые дни с посещениями, а некоторые без?
@Austin: я печатал, пока вы комментировали... Смотрите мое обновление.
Помимо переименования столбца «дата», будет ли запрос общего табличного выражения работать прямо из коробки, подключив его к моему текущему оператору запроса, или необходимо внести дополнительные изменения?
@Austin: рекурсивный запрос должен делать то, что вы хотите (я только что добавил фильтр в link_id, о котором забыл. Но обратите внимание, что для этого требуется MySQL 8.0. Я бы рекомендовал сначала запустить запрос непосредственно в вашем любимом SQL-клиенте (phpmyadmin или подобное) перед включением его в ваш PHP-код.
Я протестировал запрос в MySQL, а затем реализовал его в своем коде, и запрос работает нормально. Спасибо за быстрое решение. Я принял ваш ответ, так как это лучшее решение.
@Dharman Поскольку данные, извлекаемые с помощью SQL-запроса, вставляются в базу данных без прямого ввода пользователем, действительно ли для этого экземпляра требуются подготовленные операторы?