spryt: странствующий вебмастер об авторе | контакты | PHP скрипты | Путешествия | Дешевые авиабилеты | Подкинуть деньжат

Делаем наглядную статистику событий по дням (PHP + Google Charts)

Март 7, 2014

Сегодня у нас «вебдев для самых маленьких», а именно — оформление разной статистический информации графиками. Задача: есть некоторые события (регистрация пользователей, появление сообщений, поисковый запрос и т.д.), логирующиеся в MySQL, требуется наглядно показать динамику (сколько событий в день/месяц/час), вроде такого:

post_stat

Для начала нужно добавить в таблицу события поле `time_added`, тип TIMESTAMP, значение по умолчанию — CURRENT_TIMESTAMP. Не нужно даже ничего менять в php-коде — в это поле будет автоматически добавляться время добавление записи, а в phpmyadmin он будет выглядеть вполне читабельным значением вроде «2014-03-07 18:22:27» (в отличие от записи timestamp-а в int поле). Затем делаем выборку из таблицы:

  1. //Получаем метку даты 30 дневной давности о оформляем его в удобовариемый mysql-ем вид
  2. $last30d=date("c",strtotime("-30 day"));
  3.  
  4. //Делаем выборку событий из базы старше нужного
  5. $res=mysql_query("SELECT COUNT(*),time_added FROM wordstat WHERE time_added>’$last30d‘ GROUP BY DAY(`time_added`)");
  6. while($row=mysql_fetch_array($res)) {
  7.         $key=date("Y-n-d",strtotime($row[‘time_added’]));
  8.         $days[$key]=$row[‘COUNT(*)’];
  9. }
  10.  
  11. //Сортируем массив в нужном порядке
  12. ksort($days);

Не так уж сложно, как видите) Точно так же можно подсчитывать число событий в месяц или год. Следующая задача — построить график по этим данным. Можно конечно использовать какую-нибудь php-gd библиотеку, или даже самостоятельно всё написать, но зачем, если есть готовые либы? Я использую JS-библиотеку Google Charts, обладающую просто безграничными возможностями, главное в нём разобраться.

  1. <script type="text/javascript" src="https://www.google.com/jsapi"></script>
  2. <script type="text/javascript">
  3.  
  4. // Load the Visualization API library and the piechart library.
  5. google.load(‘visualization’, ‘1.0’, {‘packages’:[‘corechart’]});
  6. google.setOnLoadCallback(drawChart);
  7.    // … draw the chart…
  8.  
  9. function drawChart() {
  10.  
  11.     // Create the data table.
  12.     var data = new google.visualization.DataTable();
  13.         data.addColumn(‘date’, ‘Дата’);
  14.     data.addColumn(‘number’, ‘Запросов’);
  15.     data.addRows([
  16.     <?
  17.     foreach($days as $day=>$count) {
  18.                 $date=strtotime($day)*1000;
  19.                 echo "[new Date($date), $count],\n";
  20.     }
  21.     ?>
  22. ]);
  23.         var options = {‘title’:‘Поисковых запросов, в день:’,
  24.         ‘width’:900,
  25.         ‘height’:200,
  26.         ‘legend’:{‘position’:‘none’},
  27.         ‘titleTextStyle’:{‘fontName’:‘Georgia’,‘fontSize’:20,‘bold’:false},
  28.         chartArea: {width: ‘100%’},
  29.         vAxis: {textPosition: ‘in’,minValue: 0},          
  30.     };
  31.  
  32.     // Instantiate and draw our chart, passing in some options.
  33.     var chart = new google.visualization.AreaChart(document.getElementById(‘chart_div’));
  34.     chart.draw(data, options);
  35. }
  36. </script>
  37.  
  38. <div id="chart_div" style="width: 900px; height: 200px;"></div>

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

Задача решена. Да, возможно в MySQL можно задать такой запрос, который выдаст данные в уже нужном отформатированном виде. Уже перед самой публикацией понял, что я дичайше накосячил (до этого просто выбирал все записи из БД и фасовал их по дате в массив), и при более-менее значительных данных скрипт будет либо падать от нехватки памяти, либо очень очень долго думать. Отсюда вывод — не верьте всему тому, что написано в интернете) Гугл и stackoverflow быстро выдали мне нужное решение, описанное выше (там только SQL-запрос сменился). Хотя я пока еще использую старое ресурсоемкое решение, так-как мне нужно много более подробной информации, а не только динамика. Наверняка там и в других местах можно реализовать более удобно и правильно, например метод передачи данных между php и js.

PS. Я люблю собирать разнообразную статистику и анализировать её. Правда, не всегдя я делаю это наглядно — просто лень заморачиваться, можно ведь просто таблицу значений вывести) Но когда это публичная статистика, хочется навести красоту, а потом уже сам привыкаешь к такому удобству, и начинаешь делать так везде.

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

Порекомендуй друзьям →

39 комментариев

  1. Kassenov

    Спрут, можешь добавишь в блек лист ненормативную лексику, а то они в запросах отображаются) http://prntscr.com/2ype8u

  2. Spryt

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

    А в чем проблема?)

  3. Ленивый вебмастер

    Херня эти ваши готовые библиотеки. Это не для тру-прогеров. Нужно самому с нуля писать!

  4. DimaX

    Херня эти ваши готовые библиотеки. Это не для тру-прогеров. Нужно самому с нуля писать!

    Вообще обычно я согласен с первым предложением, но только не в тех случаях, когда это касается графиков, ибо чтобы сделать норм график чисто на пхпбыдлокоде это ОМГ реально, гугл рулит, да. Только вот он прекращает поддержку со временем.

    Спрут тут пишет про javascript’овую библиотеку, а всего несколько лет назад у них была другая, для генерации полноценных статичных картинок, теперь она помечена как устаревшая и скоро будет вырублена. А мне, например, JS-графики не уперлись, мне именно картинки нужны, уникальный контент и все такое, ага.

  5. Никола

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

  6. Spryt

    Ленивый вебмастер, ну ты сам знаешь, что я отправлю тебя писать) (сервер, OS, язык программирования и т.д.) Не использовать достижения других и оставаться на прежнем уровне это стагнация. Лучше вобрать в себя самое лучшее и заниматься движением дальше (если не стоит вопрос образования — тогда строить велосипеды и разбираться полезно)

    DimaX, ну она deprecated с 2012-го года, и ничего, работает дальше (продлены до 2015-го уже). На топсапе например на ней графики, мне тоже изображения больше по душе (к тому же их копировать проще). А если немного напрячь гугл, то вот дока для вывода графиков как изображений в тех же Google Charts — https://developers.google.com/chart/interactive/docs/printing (они перешли на JS из-за опций/данных), хоть закопируйся.

    К тому же есть и другие как JS-либы графиков, которые можно скачать себе ( http://www.amcharts.com/ например), так и PHP-шные. Тратить свое время на такие рутинные задачи, как графики статистики, весьма бессмысленно на мой взгляд (разве что под совсем специфичные задачи).

  7. xtra

    <? — ну охуеть.

  8. alex donskih

    Немного переписал: http://pastebin.com/ufmYkHyi

    Как твой sql-запрос работает я так и не понял. Сделал по-другому так что часть обрабатывашихся пхп действий ушла на сторону mysql. И ajax бы прикрутить по-хорошему, но лень сейчас. Если надо могу сделать позже.

    Для графиков рекомендую все же юзать какую-то либу, которую можно положить у себя на хостинге. Гугль периодически любит ВНЕЗАПНО закрывать свои проекты. Я работал с highchart.js — она неплоха, но наверное не лучше миллиона других.

  9. Spryt

    alex donskih, mysqli и DATE_FORMAT — ага, это получше (хотя теперь уже я не могу понять, как работает твой SQL-запрос =). А вот перенос логики в JS-кусок конечно не очень (у меня конечно тоже не айс, но хотя бы без sql-данных и форматирования), все же надо разделять. А почему не pdo кстати? Я использую в CI, там $words=$query->result_array(); , удобно.

    Если бы я строил тонну графиков внутри корпоративного портала — возможно. А для этих целей гугловская подходит идеально, ничего загружать не нужно, и синтаксис загрузки данных и форматирования уже привычный. Зато если они у него есть, они развиваются, а не внезапно пропадают.

  10. alex donskih

    (хотя теперь уже я не могу понять, как работает твой SQL-запрос =)

    SUM(value) суммирует значения полей value для строк что мы сгруппировали по дню.

    А вот перенос логики в JS-кусок конечно не очень

    Где? Там где у тебя формируются точки для графика я только имена переменных поменял. По-хорошему ajax-ом надо эти данные грузить, но лень было делать.

    А почему не pdo кстати? Я использую в CI, там $words=$query->result_array(); , удобно.

    Удобно, да. Я по-быстрому накидал. Этот код еще можно улучшить.

  11. Spryt

    alex donskih, ну а COUNT(*) делает тоже самое, только не суммирует, а считает их число (хотя там должно быть что-то вроде COUNT(id_word) ). Там же каждая запись = одно событие.

    Одно дело приводить к стандартному виду на пыхе и потом распаковывать, и другое — вставлять в шаблон «$day[‘DATE_FORMAT(`time_added`,\’%Y-%m-%d\’)’]);».

    Не аяксом, тут можно $key=date(«Y,n,d»), чтобы сразу в js вставлять дату из массива, только вот ломаю голову, как декремент месяца сделать без 5-строчных преобразований.

  12. alex donskih

    Смотри вот так: http://pastebin.com/XDjQkWMC

    Там же каждая запись = одно событие.

    Ну я же правильно понял, что тебе нужно просуммировать значения за день? Или одна запись = один посетитель?

    другое – вставлять в шаблон “$day[‘DATE_FORMAT(`time_added`,\’%Y-%m-%d\’)’]);”.

    Это имя ячейки массива такое. Принтани $days на 17 строке и увидишь.

    тут можно $key=date(“Y,n,d”), чтобы сразу в js вставлять дату из массива

    Я тебе сделал вообще без этого геморроя. Из базы просто получаем таймштампы и на основе них строим точки для графика.

    PS
    Можно код на гитхаб залить и там попилить еще немного если есть желание. Тут в комментах это не очень удобно.

  13. Spryt

    alex donskih, точняк, хотя JS как всегда радует форматами, timestamp*1000)

    Или одна запись = один посетитель?

    Именно. Например, табличка регистраций пользователей. Нужно не суммировать, а считать их число.

    Это имя ячейки массива такое. Принтани $days на 17 строке и увидишь.

    Ну это-то я понимаю, просто такие вещи в шаблоне писать моветон.

    Из базы просто получаем таймштампы и на основе них строим точки для графика.

    Перемудрил ты как-то, читабельность кода страдает) Переписал еще раз (правда, не стал переезжать на mysqli): http://pastebin.com/nPGu6JRL

    С гитхабом не особо дружу ( Да и незачем, код то простенький.

  14. alex donskih

    Какие вещи писать моветон? Тебя смущает длинное имя переменной? Это вкусовщина, я считаю. К тому же у тебя-то в коде вообще адЪ 🙂 Не просто js, php и html в одном файле, но еще и php юзается внутри js.

    Читабельность страдает это на каких строках?

    Гитхаб для того и создан, чтобы удобно обсуждать код и коллективно разрабатывать. Неважно что это за код. Может сейчас самое время познакомиться поближе? 😉

  15. Spryt

    Да, очень смущает (а так же доступ к mysql-полям внутри шаблона). Именно поэтому лучше strtotime, чем UNIX_TIMESTAMP(DATE_FORMAT(time_added, \»%Y-%m-%d\»)). Читабельность.

    Вообще-то нет, два файла, и у себя я использую так же — один логика, другой шаблон. А php внутри JS — а как по другому? (ну можно шаблонизаторский аналог использовать, только зачем?) Спасибо за поправку, теперь там нет list и прочего, стало куда лучше (в посте обновил уже). Для такого количества данных готовить JSON-экспорт и импорт малость влом, хотя и стоило бы, тогда было бы совсем по фен-шую)

    Как-то у меня фигово с коллективной разработкой)

  16. Zondervit

    Андрей привет!
    Хотел поинтересоваться, а как ты скрыл robots.txt в том случае, если юзер введет его в браузере?
    Два дня парюсь на эту тему. В сети куча противоречивой инфы на это счет!
    Кстати, страница, ушедшая в путешествие, очень смешная)))

  17. Zondervit

    Не ответишь мне? Я понимаю что через htaccess, но что конкретно туда прописывать, чтобы ботов не обидеть?

  18. Spryt

    Zondervit, я тупо не понял твой вопрос. Попробуй еще раз спросить, предположив, что твой собеседник не обладает способностью к телепатии. Какой сайт, что ты проверяешь, какой результат получаешь, почему ты считаешь его неожиданным и т.д.

    У sf роботы проекрасно открыты для всех — http://storyfinder.ru/robots.txt , зачем вообще скрывать его от пользователей, если он для поисковиков? Какой смысл заниматься клоакингом?

  19. Art

    Спрут, с чего посоветуешь начать изучение программирования на пхп?

  20. Zondervit

    Ок! Понял тебя!
    Вводим в браузере spryt.ru/robot.txt — и что видим? Видим прикольную картинку (скорее всего ты сам нарисовал) а рядом с картинкой надпись:

    Error 404 — Not Found
    К сожалению, страница не найдена. Может она отправилась путешествовать?

    Не движок же такие приколы генерит?
    Теперь я проделываю тоже с моим доменом zondervit.ru/robot.txt
    И вижу, что всю структуру роботса в браузере.

    Вот оттого у меня и вопрос возник))) как ты сделал так, чтобы 404 выходило?

  21. Zondervit

    Кстати, с трезвел поисковичком затея интересная!

  22. Ленивый вебмастер

    Ах-ха, Zondervit жгет…
    Тогда я тоже скрываю robot.txt от посетителей и отдаю только поисковикам, но им то нужен robotS.txt
    Может я чего не знаю?

  23. Spryt

    Art, с решения проблем. Или онлайн курсов/книжек php для чайников. Но лучше все же просто решать встающие перед тобой проблемы (пофиксить скрипт например).

    Вводим в браузере spryt.ru/robot.txt – и что видим? Видим прикольную картинку (скорее всего ты сам нарисовал) а рядом с картинкой надпись:

    И получаем 404-ю ошибку, потому что роботы читают robots.txt. Картинку увы нарисовал не я, я так не умею (

  24. Zondervit

    Парни, вот вам смешно, а я за три дня уже зрение посадил)))
    Ну да ошибся я не robot нужно писать а robots.txt
    Но только когда я ввожу свой домен и robots.txt через / всего моего робота видно как на ладони. На ваших сайтах выскакивает 404!
    Неужели никто не знает решения? Почему у вас так, а у меня така опа…

  25. Spryt

    Внимательнее надо быть)
    Еще раз повторяю — нафига? У ВСЕХ сайтов роботсы открыты, потому что они для поисковых машин. И любой имеет к ним доступ. Какой смысл вообще их закрывать? Ты же не пароли там хранишь.

  26. Art

    Интересный метод, буду пробовать))

  27. Toxic_Cat

    Круто, сегодня замутил уже график один, для проекта. В целом решение на 100% устраивает. Единственное что согласен на счет производительности. Для постройки довольно таки подробного графика приходится перерабатывать тонну данных, которые требуют ресурсов.

  28. noxon

    оо ты GROUP BY юзаешь. крутой) я стараюсь не наворачивать

  29. Spryt

    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, кстати, интересный блог.

  30. Spryt

    Во, а теперь рейтинг.. Хотя странно. Там раньше было 504 sql-запроса (для каждого блога вызывалось три запроса на число постов, дату обновления и подсчет постов по параметрам), но при этом ничего не тормозило и нормально работало (кеш что ли такой хороший). Сначала сделал монструозный LEFT JOIN на три таблицы, но он отрабатывался по 2-3 секунды (хотя тоже сразу уходил в кеш и потом вызывался мгновенно). Не стал мучить, сделал 4 запроса с GROUP BY. Получилось что-то вроде 0.15c -> 0.03c, хотя там замерять надо поточнее. Ну фиг с ним, зато на БД будет меньше нагрузки.

    Нда, заниматься всякой оптимизацией и вообще кодингом куда интереснее, чем искать, где бы еще попиарить проект ( Но не ради 50 человек в день конечно.

  31. Zondervit

    Внимательнее надо быть)
    Еще раз повторяю – нафига? У ВСЕХ сайтов роботсы открыты, потому что они для поисковых машин. И любой имеет к ним доступ. Какой смысл вообще их закрывать? Ты же не пароли там хранишь.

    Решил таки проблему сменой прав на папку на моем на хостинге! Ларец просто открывался, и Яша robots видит)))
    Андрей, ты долго изучал программирование до того уровня, чтобы начать писать поисковики по турсайтам?

  32. Spryt

    Хорошо, что наконец-то решил)

    Вопрос не в том, как долго (первые скрипты я начал писать в 2006-м, 8 лет назад), а через сколько проектов прошел. Каждый новый и сложный проект способствует более глубокому изучению и накоплению опыта, особенно если их уровень качественно растет (то есть на однотипных задачах можно много лет топтаться на одном месте). Если есть большое желания научится программировать, мозг умеет мыслить логически и алгоритмично, есть много свободного времени — то можно за полгода-год выйти на средний уровень и решать большинство задач, в том числе — поисковик по турсайтам (на самом деле, чисто технически это не настолько сложный проект. Хотя, как считать — в некоторых проектах много рутинных задач, но они несложные — но затягиваются, тут же такой рутины мало, зато нужно строить нехилые алгоритмы, над многими из которых приходилось корпеть часами, а потом еще столько же — добиваться того, чтобы они работали быстро). Или потратить месяцок на какой-нибудь курс (Coursera, а не инфобиз) или книгу для новичков, понять основы, этого вполне хватит чтобы исправлять косяки в плагинах или написанных фрилансерами скриптах.

  33. Zondervit

    Понял. Спасибо за ответ) н
    Насчет курсов… Много уже чего пересмотрел, но как ты справедливо заметил, попадается одна байда! Здесь даже спецом не нужно быть, чтобы понять всю суть этих проектов. Одни зомботренинги попадаются, с ох…ным количеством понтов, после которых тебе предлагают пригласить в этот кружок своих друзей и получить реферальные.
    Буду искать. И еще раз спасибо)))

  34. Art

    У «Школы Программирования» вроде бы неплохой курс есть для начинающих, еще есть курсы от «Специалиста».

  35. Zondervit

    Андрей, а ты форму комментарием на этом блоге как-то редактировал, в части Имя, E-mail и Сайт? Или это дефолтный вариант?

  36. Сергей SDL

    Спрут, как считаешь CMSE рабочая тема?
    Меня Сапа начала напрягать. Ссылок в ОК держит постоянно одно и тоже количество. Такое ощущение что фильтр какой-то стоит. CMSE реально может изменить ситуацию?

  37. Spryt

    Zondervit, нет, это дефолтный. И гугл находится в соседнем окне если что.

    Сергей SDL, хз, не пробовал. Говорят работает, но в том же духе что и ТЛ — выкупом кучей дешевых ссылок.

  38. Roman

    Ну и быдлокод.

  39. Сергей SDL

    Ясно. Чего-то я разочаровываться начал в теме заработка на ссылках. Хорошо что не ссылками едиными зарабатываем. Все таки бизнес реальный намного лучше и прибыльнее бизнеса виртуального.
    Был на семинаре http://www.cybermarketing.ru/seminar53.html
    В презентации топсапа фигурировала)

Комментарий:

Андрей «Spryt» Гиацинтов (VK)
Email: me@spryt.ru

Путешествия:

Сейчас: Самара, Россия
Посещенные страны:
открыть все | закрыть все

Посмотреть на Google.Maps

Как дешево путешествовать по Азии

Как найти идею для сайта

Я зарабатываю:

Рубрики:

Итоги(127) Я(57) Блоггинг(48) Таиланд(44) Заработок(35) Философия(34) Бред(34) Путешествия(32) Georgy(21) Сателлиты(17) Малайзия(16) Колумбия(15) SEO(14) Непал(13) Мои проекты(11) вебдев(11) Чианг Май(10) Филиппины(8) Индонезия(8) Бангкок(7) Ява(7) Шри-Ланка(7) Домены(6) Краби(6) Борнео(6) Эквадор(6) скрипты(5) Паттайя(5) Лаос(5) Самуи(5) Камбоджа(5) Кордильеры(4) Каталоги(3) Обзоры блогов(3) Буржунет(3) Cameron Highlands(3) Вокруг Аннапурны(3) Покхара(3) Helambu trek(3) Гонконг(3) Велотрип(3) Вьетнам(3) Россия(3) Гаджеты(2) Бредовые идеи(2) Визы(2) Куала-Лумпур(2) Катманду(2) Сингапур(2) деревня(2)

Архив:

Поиск по блогу:

SEO анализ сайта: