Как вывести подсчёт статистики из базы данных?

Question

[Виктория Масс]

Здравствуйте, подскажите как вывести подсчёт статистики., Сегодня, Вчера, За неделю, За месяц.

Мне друг ранее показывал как вывести на сегодня, а как вывести другие я не могу понять.

//За сегодня
$today = mysql_result(mysql_query("
SELECT 
count(*) 
FROM 
statistics_couter_visit 
WHERE 
date >= ".(mktime(0, 0, 0, date('m'), date('d'), date('Y')))
)
,0);

Подскажите варианты плиз.

Answer 1

[alexalexes]

Несколько Count + умение обращаться с временнЫми функциями, и у вас получится универсальный запрос со статистикой:
Если scv.date типа date или datetime:

select count(case when scv.date >= CURDATE() then 1 end) today_count,
       count(case when scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) and scv.date < CURDATE() then 1 end) yesterday_count,
       count(case when scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) then 1 end) week_count,
       count(case when scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) then 1 end) month_count
from statistics_couter_visit as scv
where scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) -- тут ограничение по макс. окну просмотра статистики, то есть по month_count

Если scv.date типа timestamp (или любое другое целочисленное представление):

select count(case when scv.date >= UNIX_TIMESTAMP(CURDATE()) then 1 end) today_count,
       count(case when scv.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) and scv.date < UNIX_TIMESTAMP(CURDATE()) then 1 end) yesterday_count,
       count(case when scv.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 WEEK)) then 1 end) week_count,
       count(case when scv.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) then 1 end) month_count
from statistics_couter_visit as scv
where scv.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) -- тут ограничение по макс. окну просмотра статистики, то есть по month_count

PS: Естественно, по полю scv.date должен быть создан индекс.

Comments

[Виктория Масс]

Это сложно для меня, нужно разжевывать мне по полочкам:)

[alexalexes]

Ну, берите поисковик и разжевывайте самостоятельно:
- Обычный count(*)
- count(distinct полем)
- count(с case выражением)
- Что такое CURDATE(), NOW()
- DATE_SUB()
- Математика с типами данных date и datetime (вычитание, сдвиги, вычисления всяких понедельников взад и вреред, первых дней месяцев. года и т.д., правильное вычитание месяцев, определение возраста в целых годах и т.д.)
- Перевод типов данных времени в строку и обратно (форматирование)
- Сравнение дат

[Виктория Масс]

Ну например , так надо вставить

$today = mysql_result(mysql_query("
select count(case when scv.date >= CURDATE() then 1 end) today_count,
       count(case when scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) and scv.date < CURDATE() then 1 end) yesterday_count,
       count(case when scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) then 1 end) week_count,
       count(case when scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) then 1 end) month_count
from statistics_couter_visit as scv
where scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
)
,0);

[alexalexes]

Вам, наверное, еще рановато править модули, где идет взаимодействие с СУБД, раз у вас простая модификация и просмотр результатов запроса через PHP вызывает боль.
Изучите этот раздел справки https://www.php.net/manual/ru/ref.mysql.php
Наверное, чтобы познакомится с результатами запроса, нужно что-то такое набросать:

$result = mysql_query("select count(case when scv.date >= CURDATE() then 1 end) today_count,
       count(case when scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 DAY) and scv.date < CURDATE() then 1 end) yesterday_count,
       count(case when scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 WEEK) then 1 end) week_count,
       count(case when scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) then 1 end) month_count
from statistics_couter_visit as scv
where scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) -- тут ограничение по макс. окну просмотра статистики, то есть по month_count");
if(!$result)
{
  echo "Could not successfully run query from DB: " . mysql_error();
  exit();
}
$row = mysql_fetch_assoc($result);
if ($row)
{
    echo 'today_count: '.$row["today_count"].'; ';
    echo 'yesterday_count: '.$row["yesterday_count"].'; ';
    echo 'week_count: '.$row["week_count"].'; ';
    echo 'month_count: '.$row["month_count"].'; ';
    // следовательно, значение для $today можно получить таким образом:
   $today = $row["today_count"];
}

mysql_free_result($result);

PS: Заменить бы тут mysql_* функции на mysqli_*, но кто-ж это будет делать.

[Виктория Масс]

Спасибо большое я всё сделала, но пишет всё по нулям, вы писали про это PS: Естественно, по полю scv.date должен быть создан индекс. - это как?

[alexalexes]

Нужно выполнить команду create index в среде MYSQL, либо воспользоваться инструментарием phpmyadmin, если такой используется.
Индекс нужен, чтобы оптимально получить все возможные выборки в тех запросах, где есть условия по этому полю.

но пишет всё по нулям,

Ну, тогда нужно проверять, извлекается ли что-то по простым запросам, типа:
select * from statistics_couter_visit as scv
И если подрезать выборку в пределах одного месяца:

select *
  from statistics_couter_visit as scv
where scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
order by scv.date

[Виктория Масс]

Поставила и нули исчезли

$result = mysql_query("
select *
  from statistics_couter_visit as scv
where scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH)
order by scv.date
");
$row = mysql_fetch_assoc($result);

[alexalexes]

Строки при этом нужно по-другому извлекать.
Пример с count-ами заточен под извлечение одной строки с конкретными названиями полей.

while($row = mysql_fetch_assoc($result))
{
  var_dump($row);
}

[Виктория Масс]

alexalexes, вот такая картина вышла

[Виктория Масс]

вот как в базе выглядит, я установила индекс к date

[alexalexes]

Не совсем корректно использовать строковый тип, чтобы записывать даты.
Куда уместнее использовать тип timestamp или datetime.
Я добавил второй вариант запроса, который будет работать в вашем случае.

Из-за того, что тип поля строковый индекс будет работать молоэффективно из-за преобразования значений как целое число.

[Виктория Масс]

Спасибо большое сработало, я применила datetime, но есть проблемка счётчиком.
Проверка существует ли пользователь в базе в течение до 23:59 часов.
чтобы не было накрутки:)

$user_attendance_profile = mysql_fetch_array(mysql_query("
SELECT * FROM 
statistics_couter_visit 
WHERE 
tag='$base_quest[tag]' AND 
ip='$ip' AND 
browser='$browser' AND 
date >= ".date('Y-m-d 23:59:59')."
")
);

Помогите как исправить, плиз

[alexalexes]

$user_attendance_profile = mysql_fetch_array(mysql_query("
SELECT * FROM 
statistics_couter_visit 
WHERE 
tag='".mysql_real_escape_string($base_quest['tag'])."' AND 
ip='".mysql_real_escape_string($ip)."' AND 
browser='".mysql_real_escape_string($browser)."' AND 
date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL -1 DAY)) -- полночь следующего дня
")
);

1. Не нужно использовать время PHP, для MySQL предусмотрены функции curdate() и now() - от них можно получать текущую отметку времени.
2. mysql_* функции небезопасные, их нужно заменить вдумчиво на mysqli_*. любой мамкин хакер, захотевший проанализировать на SQL-инъекции ваш сайт, найдет черный ход к вашей базе через юзер-агент браузера.
Хоть я экранировал входные параметры через mysql_real_escape_string, но это отчасти снимает проблему инъекций. Нужно использовать подготавливаемые запросы, которые есть только в mysqli и PDO реализациях.

[Виктория Масс]

я думала тока post запросы нужно mysql_real_escape_string :) спасибо большое:)

[Виктория Масс]

alexalexes, вот теперь ещё как вывести ЗА ВСЁ ВРЕМЯ? а то искала в инете так и не смогла найти.

[alexalexes]

Либо отдельным запросом получаете:

select count(*) total_count
from statistics_couter_visit as scv

Либо добавляете еще один count без case условий в запрос по всей статистике, но убираете ограничение в where:

select count(case when scv.date >= UNIX_TIMESTAMP(CURDATE()) then 1 end) today_count,
       count(case when scv.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 DAY)) and scv.date < UNIX_TIMESTAMP(CURDATE()) then 1 end) yesterday_count,
       count(case when scv.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 WEEK)) then 1 end) week_count,
       count(case when scv.date >= UNIX_TIMESTAMP(DATE_SUB(CURDATE(), INTERVAL 1 MONTH)) then 1 end) month_count,
      count(*) total_count -- статистика за все время
from statistics_couter_visit as scv
-- тут нет where, выборку не урезаем, чтобы получать полный подсчет строк

[Виктория Масс]

а в WHERE нужно походу переписать строку scv.date >= DATE_SUB(CURDATE(), INTERVAL 1 MONTH) ?

[alexalexes]

Ее убрать надо.

[Виктория Масс]

Спасибо, не хотите поработать за платно:) а то меня уже 2 раза кинули мошенники

[Виктория Масс]

alexalexes, Согласны?

[alexalexes]

Нет, я не работаю на фрилансе.

[Виктория Масс]

Тогда бесплатно

[Виктория Масс]

Вот ещё, я хочу вывести id запись, мне нужна проверка существует или нет id
я пытаюсь вставить это
* FROM id,

не получается:(

[Виктория Масс]

alexalexes, ааа теперь поняла:) интересно сможете мне помочь в подзагрузки страниц ?

Answer 2

[Сергей Соловьев]

Что это за статистика?

Если имеется ввиду статистика использования БД, то есть специальная схема performance_schema - пиши к ней запросы.

Но скорее всего, ты имеешь ввиду статистику своего сайта/приложения/API (в общем прикладного использования).
Это уже более сложная тема, т.к. необходимо:
- Спроектировать механизм сбора статистики - API/эндпоинты/функциии
- Спроектировать БД для ее хранения - таблицы/схемы + запросы

Также не забывай, что статистики накапливается очень много и тебе потребуются оптимизации разные. Тут тоже не очень просто:
- Для хранения статистики - разные БД (скорее всего у тебя реляционная, но для статистики лучше использовать nosql БД, например, временных рядов)
- Необходимо знать особенности этой БД, т.к. нагрузки возможно будут высокими
- Принять во внимание то, что место может просто закончиться и другие неожиданные ситуации

Просто так на твой вопрос не ответить, т.к. под каждую ситуацию свое решение

Comments

[Виктория Масс]

Подсчёт посещаймости на сайте