Работа с БД. Анализ логов
Автор: Дмитрий Лебедев
Источник: detail.phpclub.net
Хочу снова похвалиться своим "творчеством".
В декабре я описал, как проще всего собирать логи. Сейчас я
покажу, как их можно анализировать.
Что там было в таблице? Дата, адрес от корня виртуального
хоста ($PHP_SELF), броузер, реферер, ip-адрес пользователя и
имя хоста. Строка вставлялась так:
@mysql_query("INSERT INTO logs
(date, ip, host, address, referer, browser) VALUES (NOW(),
'$REMOTE_ADDR', '". gethostbyaddr($REMOTE_ADDR). "',
'$PHP_SELF', '$HTTP_REFERER', '$HTTP_USER_AGENT')");
А что мы хотим видеть в статистике? Просто посмотрим разные
варианты: посещения по дням, распределение посещений по
времени суток. Затем эти же две выборки, только не для всех
логов, а для главной страницы. Распределение по дням недели,
посещаемость за последние недели и месяцы. Количество
посетителей, пришедших с других сайтов. Бывает интересно
посмотреть, сколько посетителей приходило с определённой
ссылки в разные дни. Распределение по времени за определённый
день (неделю, месяц). Адреса, куда уходят с такой-то страницы.
Систематизировать это не так сложно, как кажется. Всё
вышеописаное укладывается в восемь вариантов группировки
таблицы. Адрес, реферер, броузер (увы, тут получается
сравнение строчек "HTTP_USER_AGENT", включающих в себя и
версии, и ОС, а не отдельных броузеров. Тут без обработки на
входе не обойтись), день, неделя, месяц, день недели, час.
На статистику по странам, городам, а так же маршруты
пользователей не замахиваемся — тоже нужна дополнительная
обработка.
Добавим к выбору группировки выбор ограничения по дням
(последние n дней) а так же условия выборки для поля WHERE,
которые можно ввести в текстовое поле, и получим систему, в
которую укладывается те выборки и распределения, которые я
описал.
На самом деле, не так просто оказалось составить список
выборок и привести его к удобному для обработки в программе
виду. В конце концов, я взял и запихал все запросы в массив,
элемент которого выглядит так:
$selection[0] = array(
"name" => "график по дням",
"select" => array("date_format(date,'%e.%m.%Y') as dday", "count(date) as visits"),
"group" => array("dday"),
"order" => array("date DESC"),
"type" => 1
);
Элемент "name" — это название для крутилки,
"type" — тип таблицы. Типов таблиц два — просто
список и список со "столбиками". Если список сортируется по
количеству посещений, то столбики в принципе не нужны, а для
удобства восприятия, например, графика посещений по дням,
график желателен. Остальные элементы можно не комментировать.
В запросе учитываются так же и временные ограничения, и
условие, которое ввёл пользователь. Переменная $type —
номер запрашиваемой выборки.
$days = intval($days);
if ($days>0)
$selection[$type]["where"][] = "date>DATE_SUB(NOW(),INTERVAL $days DAY)";
$where = stripslashes(trim($where));
if (strlen($where)>0)
$selection[$type]["where"][] = "($where)";
После этого рисуется форма (в крутилках первыми строчками
выводятся выбранные значения. Затем строится запрос, которым
узнаётся общее количество строк. Это, надо признать, скользкое
место, потому что в нём никаких упрощений, просто в отличие от
основного запроса, здесь просто отсутствует сортировка. Но
количество строк узнаётся "в лоб" — выбирается всё то же
самое и потом делается mysql_num_rows. Если у кого будут идеи,
можете прислать мне или публиковать свой анализатор (только
ссылку на меня поставьте, пожалуйста).
$amount_request = "SELECT ". implode(", ", $selection[$type]["select"]). " FROM logs ";
if (sizeof($selection[$type]["where"])>0)
$amount_request .= " WHERE ". implode(" AND ", $selection[$type]["where"]);
$amount_request .= " GROUP BY ". implode(", ", $selection[$type]["group"]);
$request = "SELECT ". implode(", ", $selection[$type]["select"]). " FROM logs ";
if (sizeof($selection[$type]["where"])>0)
$request .= " WHERE ". implode(" AND ", $selection[$type]["where"]);
$request .= " GROUP BY ". implode(", ", $selection[$type]["group"]).
" ORDER BY ". implode(", ", $selection[$type]["order"]). " ".
get_limit($page, $amount, $in_page);
Как видите, я использую описанный в предыдущем выпуске
модуль постраничного вывода запросов (get_limit).
Но мало дать неограниченные возможности построения
запросов. Вот, допустим, смотрю я на статистику рефереров по
популярности и хочу посмотреть, как народ шёл ко мне с hackzone.ru. Что
делать? Выбирать нужные параметры крутилок и писать в
текстовом поле "referer like '%hackzone%'" ну просто влом!
Оказывается, это тоже несложно сделать. Два часа
мыслительных усилий и редактирования текста, и вот вышла некое
подобие возможности детализации выборки. Нажимаю "список
рефереров" в закладках (в закладках, потому что в адресе
передаётся параметр "referer not like
'http://detail.phpclub.net%'"), получаю таблицу, в ней в
строке "hackzone.ru" нажимаю на ссылку и вот оно,
распределение зашедших с этого сайта по дням. Можно нажать на
другую ссылку и получить распределение по времени суток —
как угодно.
Какие ещё делать выборки — зависит от вашей фантазии.
Если написанного не хватает, можно добавить свои. Обещаю по
мере сил обновлять и улучшать программу.
В принципе, имея логи у себя на офисной или домашней
машине, можно делать с ними всё, что душе угодно. Надо только
написать обработчик. Делов-то! :) На SpyLog я не претендую. По
крайней мере, для себя я написал такую вещь, которая позволяет
делать многое из того, что угодно душе. И, кстати, не грузить
лишнюю информацию и рекламу Спайлог-информера и прочего.