Делаем наглядную статистику событий по дням (PHP + Google Charts)
7 марта, 2014Сегодня у нас «вебдев для самых маленьких», а именно — оформление разной статистический информации графиками. Задача: есть некоторые события (регистрация пользователей, появление сообщений, поисковый запрос и т.д.), логирующиеся в MySQL, требуется наглядно показать динамику (сколько событий в день/месяц/час), вроде такого:
Для начала нужно добавить в таблицу события поле `time_added`, тип TIMESTAMP, значение по умолчанию — CURRENT_TIMESTAMP. Не нужно даже ничего менять в php-коде — в это поле будет автоматически добавляться время добавление записи, а в phpmyadmin он будет выглядеть вполне читабельным значением вроде «2014-03-07 18:22:27» (в отличие от записи timestamp-а в int поле). Затем делаем выборку из таблицы:
-
//Получаем метку даты 30 дневной давности о оформляем его в удобовариемый mysql-ем вид
-
$last30d=date("c",strtotime("-30 day"));
-
-
//Делаем выборку событий из базы старше нужного
-
$res=mysql_query("SELECT COUNT(*),time_added FROM wordstat WHERE time_added>’$last30d‘ GROUP BY DAY(`time_added`)");
-
while($row=mysql_fetch_array($res)) {
-
$key=date("Y-n-d",strtotime($row[‘time_added’]));
-
$days[$key]=$row[‘COUNT(*)’];
-
}
-
-
//Сортируем массив в нужном порядке
-
ksort($days);
Не так уж сложно, как видите) Точно так же можно подсчитывать число событий в месяц или год. Следующая задача — построить график по этим данным. Можно конечно использовать какую-нибудь php-gd библиотеку, или даже самостоятельно всё написать, но зачем, если есть готовые либы? Я использую JS-библиотеку Google Charts, обладающую просто безграничными возможностями, главное в нём разобраться.
-
<script type="text/javascript" src="https://www.google.com/jsapi"></script>
-
<script type="text/javascript">
-
-
// Load the Visualization API library and the piechart library.
-
google.load(‘visualization’, ‘1.0’, {‘packages’:[‘corechart’]});
-
google.setOnLoadCallback(drawChart);
-
// … draw the chart…
-
-
function drawChart() {
-
-
// Create the data table.
-
var data = new google.visualization.DataTable();
-
data.addColumn(‘date’, ‘Дата’);
-
data.addColumn(‘number’, ‘Запросов’);
-
data.addRows([
-
<?
-
foreach($days as $day=>$count) {
-
$date=strtotime($day)*1000;
-
echo "[new Date($date), $count],\n";
-
}
-
?>
-
]);
-
var options = {‘title’:‘Поисковых запросов, в день:’,
-
‘width’:900,
-
‘height’:200,
-
‘legend’:{‘position’:‘none’},
-
‘titleTextStyle’:{‘fontName’:‘Georgia’,‘fontSize’:20,‘bold’:false},
-
chartArea: {width: ‘100%’},
-
vAxis: {textPosition: ‘in’,minValue: 0},
-
};
-
-
// Instantiate and draw our chart, passing in some options.
-
var chart = new google.visualization.AreaChart(document.getElementById(‘chart_div’));
-
chart.draw(data, options);
-
}
-
</script>
-
-
<div id="chart_div" style="width: 900px; height: 200px;"></div>
И вауля, график готов. Кастомизировать его можно как угодно, мне больше нравится такой вид, а не дефолтный. Как я уже писал, возможности у библиотеки богатые (это ж гугл), документация весьма обширная, но её надо читать и вкуривать, и далеко не всё получается как надо (я например не могу настроить сетку внутри графика).
Задача решена. Да, возможно в MySQL можно задать такой запрос, который выдаст данные в уже нужном отформатированном виде. Уже перед самой публикацией понял, что я дичайше накосячил (до этого просто выбирал все записи из БД и фасовал их по дате в массив), и при более-менее значительных данных скрипт будет либо падать от нехватки памяти, либо очень очень долго думать. Отсюда вывод — не верьте всему тому, что написано в интернете) Гугл и stackoverflow быстро выдали мне нужное решение, описанное выше (там только SQL-запрос сменился). Хотя я пока еще использую старое ресурсоемкое решение, так-как мне нужно много более подробной информации, а не только динамика. Наверняка там и в других местах можно реализовать более удобно и правильно, например метод передачи данных между php и js.
PS. Я люблю собирать разнообразную статистику и анализировать её. Правда, не всегдя я делаю это наглядно — просто лень заморачиваться, можно ведь просто таблицу значений вывести) Но когда это публичная статистика, хочется навести красоту, а потом уже сам привыкаешь к такому удобству, и начинаешь делать так везде.
Исходный код оформлен в Pastebin, но для совместимости был скопирован со страницы для печати, вместе со стилями (только javascript-оформление не заработало, поэтому слегка криво подсвечен второй кусок кода)
Спрут, можешь добавишь в блек лист ненормативную лексику, а то они в запросах отображаются) http://prntscr.com/2ype8u
А в чем проблема?)
Херня эти ваши готовые библиотеки. Это не для тру-прогеров. Нужно самому с нуля писать!
Вообще обычно я согласен с первым предложением, но только не в тех случаях, когда это касается графиков, ибо чтобы сделать норм график чисто на пхпбыдлокоде это ОМГ реально, гугл рулит, да. Только вот он прекращает поддержку со временем.
Спрут тут пишет про javascript’овую библиотеку, а всего несколько лет назад у них была другая, для генерации полноценных статичных картинок, теперь она помечена как устаревшая и скоро будет вырублена. А мне, например, JS-графики не уперлись, мне именно картинки нужны, уникальный контент и все такое, ага.
Довелось мне с ним поработать, в итоге переводил все на амчартс, ибо гугл имеет широкий функционал, но все это больше похоже на помойку. Как нужно сделать что-то человеческое, гугл сразу не поддерживает или через задницу все надо делать. И выглядит устаревше имхо.
Ленивый вебмастер, ну ты сам знаешь, что я отправлю тебя писать) (сервер, OS, язык программирования и т.д.) Не использовать достижения других и оставаться на прежнем уровне это стагнация. Лучше вобрать в себя самое лучшее и заниматься движением дальше (если не стоит вопрос образования — тогда строить велосипеды и разбираться полезно)
DimaX, ну она deprecated с 2012-го года, и ничего, работает дальше (продлены до 2015-го уже). На топсапе например на ней графики, мне тоже изображения больше по душе (к тому же их копировать проще). А если немного напрячь гугл, то вот дока для вывода графиков как изображений в тех же Google Charts — https://developers.google.com/chart/interactive/docs/printing (они перешли на JS из-за опций/данных), хоть закопируйся.
К тому же есть и другие как JS-либы графиков, которые можно скачать себе ( http://www.amcharts.com/ например), так и PHP-шные. Тратить свое время на такие рутинные задачи, как графики статистики, весьма бессмысленно на мой взгляд (разве что под совсем специфичные задачи).
<? — ну охуеть.
Немного переписал: http://pastebin.com/ufmYkHyi
Как твой sql-запрос работает я так и не понял. Сделал по-другому так что часть обрабатывашихся пхп действий ушла на сторону mysql. И ajax бы прикрутить по-хорошему, но лень сейчас. Если надо могу сделать позже.
Для графиков рекомендую все же юзать какую-то либу, которую можно положить у себя на хостинге. Гугль периодически любит ВНЕЗАПНО закрывать свои проекты. Я работал с highchart.js — она неплоха, но наверное не лучше миллиона других.
alex donskih, mysqli и DATE_FORMAT — ага, это получше (хотя теперь уже я не могу понять, как работает твой SQL-запрос =). А вот перенос логики в JS-кусок конечно не очень (у меня конечно тоже не айс, но хотя бы без sql-данных и форматирования), все же надо разделять. А почему не pdo кстати? Я использую в CI, там $words=$query->result_array(); , удобно.
Если бы я строил тонну графиков внутри корпоративного портала — возможно. А для этих целей гугловская подходит идеально, ничего загружать не нужно, и синтаксис загрузки данных и форматирования уже привычный. Зато если они у него есть, они развиваются, а не внезапно пропадают.
SUM(value) суммирует значения полей value для строк что мы сгруппировали по дню.
Где? Там где у тебя формируются точки для графика я только имена переменных поменял. По-хорошему ajax-ом надо эти данные грузить, но лень было делать.
Удобно, да. Я по-быстрому накидал. Этот код еще можно улучшить.
alex donskih, ну а COUNT(*) делает тоже самое, только не суммирует, а считает их число (хотя там должно быть что-то вроде COUNT(id_word) ). Там же каждая запись = одно событие.
Одно дело приводить к стандартному виду на пыхе и потом распаковывать, и другое — вставлять в шаблон «$day[‘DATE_FORMAT(`time_added`,\’%Y-%m-%d\’)’]);».
Не аяксом, тут можно $key=date(«Y,n,d»), чтобы сразу в js вставлять дату из массива, только вот ломаю голову, как декремент месяца сделать без 5-строчных преобразований.
Смотри вот так: http://pastebin.com/XDjQkWMC
Ну я же правильно понял, что тебе нужно просуммировать значения за день? Или одна запись = один посетитель?
Это имя ячейки массива такое. Принтани $days на 17 строке и увидишь.
Я тебе сделал вообще без этого геморроя. Из базы просто получаем таймштампы и на основе них строим точки для графика.
PS
Можно код на гитхаб залить и там попилить еще немного если есть желание. Тут в комментах это не очень удобно.
alex donskih, точняк, хотя JS как всегда радует форматами, timestamp*1000)
Именно. Например, табличка регистраций пользователей. Нужно не суммировать, а считать их число.
Ну это-то я понимаю, просто такие вещи в шаблоне писать моветон.
Перемудрил ты как-то, читабельность кода страдает) Переписал еще раз (правда, не стал переезжать на mysqli): http://pastebin.com/nPGu6JRL
С гитхабом не особо дружу ( Да и незачем, код то простенький.
Какие вещи писать моветон? Тебя смущает длинное имя переменной? Это вкусовщина, я считаю. К тому же у тебя-то в коде вообще адЪ 🙂 Не просто js, php и html в одном файле, но еще и php юзается внутри js.
Читабельность страдает это на каких строках?
Гитхаб для того и создан, чтобы удобно обсуждать код и коллективно разрабатывать. Неважно что это за код. Может сейчас самое время познакомиться поближе? 😉
Да, очень смущает (а так же доступ к mysql-полям внутри шаблона). Именно поэтому лучше strtotime, чем UNIX_TIMESTAMP(DATE_FORMAT(time_added, \»%Y-%m-%d\»)). Читабельность.
Вообще-то нет, два файла, и у себя я использую так же — один логика, другой шаблон. А php внутри JS — а как по другому? (ну можно шаблонизаторский аналог использовать, только зачем?) Спасибо за поправку, теперь там нет list и прочего, стало куда лучше (в посте обновил уже). Для такого количества данных готовить JSON-экспорт и импорт малость влом, хотя и стоило бы, тогда было бы совсем по фен-шую)
Как-то у меня фигово с коллективной разработкой)
Андрей привет!
Хотел поинтересоваться, а как ты скрыл robots.txt в том случае, если юзер введет его в браузере?
Два дня парюсь на эту тему. В сети куча противоречивой инфы на это счет!
Кстати, страница, ушедшая в путешествие, очень смешная)))
Не ответишь мне? Я понимаю что через htaccess, но что конкретно туда прописывать, чтобы ботов не обидеть?
Zondervit, я тупо не понял твой вопрос. Попробуй еще раз спросить, предположив, что твой собеседник не обладает способностью к телепатии. Какой сайт, что ты проверяешь, какой результат получаешь, почему ты считаешь его неожиданным и т.д.
У sf роботы проекрасно открыты для всех — http://storyfinder.ru/robots.txt , зачем вообще скрывать его от пользователей, если он для поисковиков? Какой смысл заниматься клоакингом?
Спрут, с чего посоветуешь начать изучение программирования на пхп?
Ок! Понял тебя!
Вводим в браузере spryt.ru/robot.txt — и что видим? Видим прикольную картинку (скорее всего ты сам нарисовал) а рядом с картинкой надпись:
Error 404 — Not Found
К сожалению, страница не найдена. Может она отправилась путешествовать?
Не движок же такие приколы генерит?
Теперь я проделываю тоже с моим доменом zondervit.ru/robot.txt
И вижу, что всю структуру роботса в браузере.
Вот оттого у меня и вопрос возник))) как ты сделал так, чтобы 404 выходило?
Кстати, с трезвел поисковичком затея интересная!
Ах-ха, Zondervit жгет…
Тогда я тоже скрываю robot.txt от посетителей и отдаю только поисковикам, но им то нужен robotS.txt
Может я чего не знаю?
Art, с решения проблем. Или онлайн курсов/книжек php для чайников. Но лучше все же просто решать встающие перед тобой проблемы (пофиксить скрипт например).
И получаем 404-ю ошибку, потому что роботы читают robots.txt. Картинку увы нарисовал не я, я так не умею (
Парни, вот вам смешно, а я за три дня уже зрение посадил)))
Ну да ошибся я не robot нужно писать а robots.txt
Но только когда я ввожу свой домен и robots.txt через / всего моего робота видно как на ладони. На ваших сайтах выскакивает 404!
Неужели никто не знает решения? Почему у вас так, а у меня така опа…
Внимательнее надо быть)
Еще раз повторяю — нафига? У ВСЕХ сайтов роботсы открыты, потому что они для поисковых машин. И любой имеет к ним доступ. Какой смысл вообще их закрывать? Ты же не пароли там хранишь.
Интересный метод, буду пробовать))
Круто, сегодня замутил уже график один, для проекта. В целом решение на 100% устраивает. Единственное что согласен на счет производительности. Для постройки довольно таки подробного графика приходится перерабатывать тонну данных, которые требуют ресурсов.
оо ты GROUP BY юзаешь. крутой) я стараюсь не наворачивать
noxon, будешь смеяться, только после написания поста (и прочтения статьи на хабре) понял, насколько я не понимаю мощи SQL) А уж как я раньше жил без GROUP BY, я вообще не понимаю. Сейчас переписал статистику кликов именно с union-ами, гроупами и прочими премудростями, типа такого:
SELECT domain, id_site, COUNT(id_click) as count, COUNT(DISTINCT(visit_hash)) FROM clicks WHERE time_added>’$last30d’ GROUP BY id_site ORDER BY count DESC LIMIT 10
То есть всю логику по сортировке, суммированию и прочему переложил на БД. Правда, теперь не один запрос, а целых 4 (чтобы все данные обработать — для графиков, суммы, популярные домены), но все равно за счет разгрузки ворочания массива с уже 1500 элементами скорость подросла в несколько раз. Плюс затупил, вызов Google Chart нужно делать из футера, чтобы не тормозить загрузку основной страницы. Код теперь более читабельней и понятней, гуд, хотя обработка списка доменов слегка корява.
Toxic_Cat, ну там уже от данных зависит, угу. Например, если кинуть в гугла данными по дням за последние 365 дней, спасибо он тоже не скажет)
Zondervit, кстати, интересный блог.
Во, а теперь рейтинг.. Хотя странно. Там раньше было 504 sql-запроса (для каждого блога вызывалось три запроса на число постов, дату обновления и подсчет постов по параметрам), но при этом ничего не тормозило и нормально работало (кеш что ли такой хороший). Сначала сделал монструозный LEFT JOIN на три таблицы, но он отрабатывался по 2-3 секунды (хотя тоже сразу уходил в кеш и потом вызывался мгновенно). Не стал мучить, сделал 4 запроса с GROUP BY. Получилось что-то вроде 0.15c -> 0.03c, хотя там замерять надо поточнее. Ну фиг с ним, зато на БД будет меньше нагрузки.
Нда, заниматься всякой оптимизацией и вообще кодингом куда интереснее, чем искать, где бы еще попиарить проект ( Но не ради 50 человек в день конечно.
Решил таки проблему сменой прав на папку на моем на хостинге! Ларец просто открывался, и Яша robots видит)))
Андрей, ты долго изучал программирование до того уровня, чтобы начать писать поисковики по турсайтам?
Хорошо, что наконец-то решил)
Вопрос не в том, как долго (первые скрипты я начал писать в 2006-м, 8 лет назад), а через сколько проектов прошел. Каждый новый и сложный проект способствует более глубокому изучению и накоплению опыта, особенно если их уровень качественно растет (то есть на однотипных задачах можно много лет топтаться на одном месте). Если есть большое желания научится программировать, мозг умеет мыслить логически и алгоритмично, есть много свободного времени — то можно за полгода-год выйти на средний уровень и решать большинство задач, в том числе — поисковик по турсайтам (на самом деле, чисто технически это не настолько сложный проект. Хотя, как считать — в некоторых проектах много рутинных задач, но они несложные — но затягиваются, тут же такой рутины мало, зато нужно строить нехилые алгоритмы, над многими из которых приходилось корпеть часами, а потом еще столько же — добиваться того, чтобы они работали быстро). Или потратить месяцок на какой-нибудь курс (Coursera, а не инфобиз) или книгу для новичков, понять основы, этого вполне хватит чтобы исправлять косяки в плагинах или написанных фрилансерами скриптах.
Понял. Спасибо за ответ) н
Насчет курсов… Много уже чего пересмотрел, но как ты справедливо заметил, попадается одна байда! Здесь даже спецом не нужно быть, чтобы понять всю суть этих проектов. Одни зомботренинги попадаются, с ох…ным количеством понтов, после которых тебе предлагают пригласить в этот кружок своих друзей и получить реферальные.
Буду искать. И еще раз спасибо)))
У «Школы Программирования» вроде бы неплохой курс есть для начинающих, еще есть курсы от «Специалиста».
Андрей, а ты форму комментарием на этом блоге как-то редактировал, в части Имя, E-mail и Сайт? Или это дефолтный вариант?
Спрут, как считаешь CMSE рабочая тема?
Меня Сапа начала напрягать. Ссылок в ОК держит постоянно одно и тоже количество. Такое ощущение что фильтр какой-то стоит. CMSE реально может изменить ситуацию?
Zondervit, нет, это дефолтный. И гугл находится в соседнем окне если что.
Сергей SDL, хз, не пробовал. Говорят работает, но в том же духе что и ТЛ — выкупом кучей дешевых ссылок.
Ну и быдлокод.
Ясно. Чего-то я разочаровываться начал в теме заработка на ссылках. Хорошо что не ссылками едиными зарабатываем. Все таки бизнес реальный намного лучше и прибыльнее бизнеса виртуального.
Был на семинаре http://www.cybermarketing.ru/seminar53.html
В презентации топсапа фигурировала)