Пишем Referrer tracker: мал да удал, с хранимыми процедурами MySQL

Часто возникает желание посмотреть отуда приходят люди на ваш сайт, дабы пойти туда почитать что про нас любимых пишут, и рьяно вступить в полемику не отходя от кассы. Казалось бы, такая популярная штука как Google Analitycs должна делать это, но есть одна проблема — в отчете отрезаются GET-параметры, и если вы видите что ссылка с огромного форума, то вам еще предстоит найти нужную тему, что отнимает время (стоит упомянуть, что Google Analitycs требует дополнительного JS кода на страницах, что также отнимает время и трафик).

Варианта у нас 3:

1. Другие JS-based системы, аналоги Google Analitycs — решил не трогать, т.к. сама идея дополнительно довешивать клиентам JS не нравится
2. AwStats и другие средства анализа логов — пуленепробиваемое решение (и вероятно самое лучшее) если есть полный доступ к серверу. Не требует модификации кода вообще. Жаль что у меня shared-хостинг, и доступа к логам напрямую нет.
3. Всякие средства требующие php-инструментации (т.к. код вызывается на каждой странице).

Покопавшись в инете, нашел несколько неплохих вариантов последнего типа(PHP), часть была плоховата по функционалу (в частности плохо показывала referrer-ов), в итоге остановился на TraceWatch, как оказалось Иранского производства. :-) Начав инсталляцию я был в шоке — весь код был обработан обфускатором, и автор в лицензии запрещает его приводить в нормальный вид. Это, наряду с бесплатностью сразу вызвало большое подозрение. После того как эта хрень не смогла заработать не в дефолтном каталоге (пути к инклудам были прописаны абсолютные а не относительные), и попытки поправить это к коде, я понял что ничего хорошего от этой поделки не дождусь.

Тут я и решил написать свой Referrer tracker

Задача стояла так: показывать реффералов с разбивкой по дням. Нужно считать сколько раз откуда пришли (c GET параметрами), фильтровать гугл, больше ничего не нужно. Никакого лога IP, стран, подсчета кол-ва посещений и т.д. (хотя это достаточно просто реализовать)

База данных MySQL 5

CREATE TABLE `ref` (
 `id` int(11) NOT NULL auto_increment,
 `ref_url` varchar(950) NOT NULL,
 `ref_date` date NOT NULL,
 `ref_count` int(11) NOT NULL default '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `url` (`ref_url`,`ref_date`),
 KEY `ref_date` (`ref_date`)
) ENGINE=MyISAM DEFAULT CHARSET=cp1251 AUTO_INCREMENT=138 ;


А вот тут — святой грааль MySQL 5 — хранимые процедуры с проверкой ошибок — гарантирован максимум 1 запрос к БД на 1 юзера. В данном случае иногда все таки возможна(хоть и с очень маленькой вероятностью) ошибка на еденицу при создании записи (т.к. хранимые процедуры не атомарны), но я посчитал что риск/потеря скорости от блокировки/разблокировки таблицы не стоит этой потерянной еденички.

DELIMITER $$
CREATE PROCEDURE `process_url`(new_url VARCHAR(900))
BEGIN
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION UPDATE ref SET ref_count=ref_count+1 WHERE ref_date=CURDATE() AND ref_url=new_url;
  INSERT INTO ref (ref_url,ref_date,ref_count) VALUES(new_url,CURDATE(),1);
END$$

NB: INSERT INTO ref SET ref_url='$ref', ref_date=NOW() ON DUPLICATE KEY UPDATE ref_count=ref_count+1 — благодаря хаброюзеру Nc_soft, мы знаем что вот этот код и проще, и быстрее в 2-4 раза :-)

Собираем статистику

Вот это надо вставить где-нибуть в общем файле, который везде инклудиться. Подразумевается что соединение с БД уже установлено, и БД выбрана:

function process_referrer()
{
  $referrer = $_SERVER["HTTP_REFERER"];
 if(strpos($referrer, "your_site_name_here")===FALSE && strlen($referrer)>0)
 {
  //google support, remove redundant parameters & multiple records for the same query from different countries like google.nl
  if(strpos($referrer, "www.google")!==FALSE)
  {
   if (preg_match("/[?&]q=([^&]+)/", $referrer, $matches)) {
    $referrer = "Google Search: ".htmlspecialchars(urldecode($matches[1]));
   } else
    $referrer = "Google Search: broken query, $referrer";
  }
  //and finally, store it
  sqlexec('CALL process_url("'.mysql_real_escape_string($referrer).'");');
 }
}


Что мы тут видим — не дергаем БД если пользователь ходит по нашим страницам, или если он набрал адрес руками (или хитрый файрвол порезал). Не забываем что злобный хацкер мог бы подсунуть нам SQL Injection в referrer-е, так что mysql_real_escape_string обязательна, как всегда.

Остается главное :-)

Смотрим результаты

 $max_date = sqlcount("select MIN(ref_date) from (select DISTINCT ref_date from ref ORDER by ref_date DESC LIMIT 7) as tmp");
 $result = sqlexec("select * from ref WHERE ref_date>='$max_date' order by ref_date DESC,ref_count DESC");
 $prev_date = "";
 while($row = mysql_fetch_array($result))
 {
  if($row['ref_date']!=$prev_date)
  {
   $articlesList .= "<h1>{$row['ref_date']}</h1>";
   $prev_date = $row['ref_date'];
  }
  //Print $row data here with nice design  
 }

В начале определяются 7 последних дат в БД(хотя можно безусловно от текущей даты отнять 7). Затем получаем все записи за последние 7 дней и выводим с разбивкой по дням. Ссылки сразу можно с target="_blank"

Вспомогательные процедуры

function sqlexec($sql)
{
  if(!($result = mysql_query($sql))) showerror();
  return ($result);
}
function sqlcount($sql)
{
  $res = mysql_fetch_array(sqlexec($sql));
  return ($res[0]);
}


Результат

Можно кликать по ссылкам и сразу читать что про вас пишут :-)