Statistika přístupů v PHP – regionální lokalizace

V tomto článku navážeme na předchozí analýzu domén nejvyšší úrovně popisem takzvané regionální lokalizace, což je určení kraje, města či vysoké školy, ze které se návštěvník stránek připojuje. Krátce se zmíním také o zjištění poskytovatele připojení.

Hotovou aplikaci si můžete prohlédnout a vyzkoušet. V ukázkové verzi se provádí statistika přístupů stránek http://www.czechia.cz/help/.

Jediný způsob, jak provést regionální lokalizaci, spočívá ve hlubším zpracování doménové adresy počítače, o které jsem psal v předchozím článku. Celý postup je poměrně jednoduchý a podobá se například detekci prohlížeče. Doménovou adresu postupně porovnáváme s mnoha regulárními výrazy, které mohou označovat města, vysoké školy a řadu dalších subjektů.

Tabulka region

Vzhledem k tomu, že kromě měst budeme vytvářet i statistiku krajů, potřebujeme dvě vzájemně propojené databázové tabulky region a town. V tabulce region bude všech čtrnáct českých krajů, Slovensko a Vysoké školy, které na internetu tvoří poměrně velkou komunitu.

CREATE TABLE region (
  id int(11) NOT NULL,
  name varchar(50) NOT NULL,
  PRIMARY KEY (id)
);

• id – unikátní označení každého kraje (primární klíč)
• name – kraj

Tabulka town

V tabulce town budou informace o městech (okresech), vysokých školách a podobně. Vytvoříme ji pomocí tohoto SQL dotazu:

CREATE TABLE town (
  id int(11) NOT NULL,
  name varchar(70) NOT NULL,
  ip_regexp varchar(70) NOT NULL,
  region int(11) NOT NULL,
  PRIMARY KEY (id)
);

• id – unikátní označení každého města, VŠ atd. (primární klíč)
• name – město
• ip_regexp – regulární výraz odpovídající danému městu
• region – označení kraje (cizí klíč), obsahuje primární klíče z tabulky region

Soubor, ze kterého jsem při vytváření tabulky town vycházel, obsahuje několik stovek různých doménových adres. Pokud byste si ho chtěli prohlédnout a případně si seznam měst a dalších subjektů rozšířit, můžete si jej stáhnout. Kratičký výtah z tohoto souboru obsahuje následující tabulka.

Klíčová je v tabulce town položka ip_regexp, ve které jsou regulární výrazy detekující města či vysoké školy. Každý poskytovatel připojení používá v doménových adresách různé zkratky měst a krajů, proto se u některých měst objeví v položce ip_regexp hned několik regulárních výrazů oddělených metaznakem „|“. U delších názvů měst (například ostrava.worldonline.cz) stačí zapsat pouze název města (ostrava), u krátkých zkratek (například na.worldonline.cz) je dobré uvést celou adresu, aby nedošlo ke chybné detekci. V uvedeném příkladu by k ní mohlo dojít například u adresy neco.snake.cz.

V závěru tabulky town (od id 201) je několik největších poskytovatelů připojení, u kterých se detekce města, vzhledem k tvaru jejich doménových adres, nedá provést. Na samém konci (od id 301) je pak několik nejdůležitějších domén třetí úrovně. Tyto údaje se netýkají vlastní regionální lokalizace, jsou uvedeny spíše jako příklad, jak zjistit poskytovatele připojení, jehož služby návštěvník využívá, případně další subjekty, které navštěvují naše stránky.

Skript, který provede detekci města, je poměrně krátký a jednoduchý. Z předchozího článku připomenu, že doménová adresa se nachází v proměnné $ip_name. V SQL dotazu, který porovnává doménovou adresu s regulárními výrazy, použijeme příkaz REGEXP, jehož princip jsem se vysvětloval již v článku o detekci prohlížeče. Příkaz ORDER BY id je použit proto, že v tabulce town postupně přecházíme od konkrétnímu k obecnému (města -> poskytovatelé připojení -> domény třetí úrovně). Někdy se může stát, že doménová adresa odpovídá třeba dvěma záznamům. Z tohoto důvodu je použito omezení LIMIT 0,1. Proměnná $area, do které vložíme id města, bude součástí SQL dotazu, který do tabulky access uloží veškeré informace o každém přístupu.

// OBLAST
// odpovida domenova adresa nejakemu zaznamu v databazi ?
$area = 0;
if($ip_name != ‚0‘) {
  $query = mysql_query(„SELECT id FROM town WHERE ‚$ip_name‘ REGEXP ip_regexp ORDER BY id LIMIT 0,1“);
  if ($result = mysql_fetch_array($query))
    $area = $result[‚id‘];
}

Vyhodnocení

Statistiku zapíšeme do tabulky o třech sloupečcích – Oblast (město nebo kraj), Počet přístupů z této oblasti a Podíl přístupů z této oblasti na celkovém počtu přístupů. Statistika krajů a měst se od vyhodnocení jiných statistických údajů liší tím, že v prvním SQL dotazu, který zjišťuje počet přístupů z jednotlivých krajů, spojujeme tabulky region, town a access. Na první pohled může podmínka spojující tři tabulky vypadat krkolomně, ale pokud si uvědomíte vztahy mezi tabulkami (primární a cizí klíče) znázorněné už v prvním článku, je vše jasné. V druhém SQL dotazu, zjišťujícím počet přístupů z měst nacházejících se v daném kraji, nesmíme zapomenout na podmínku WHERE town.region = '$id', kde $id je právě zpracovávaný kraj. Další postup, tedy výpočet podílu a šířky obrázku, byl podrobně komentován v článku o detekci operačního systému.

// OBLAST
echo ‚<h2>Kraje, města, oblasti</h2>‘;
echo ‚<table width=“680″ cellspacing=“0″>‘;
echo ‚<tr><th>Oblast</th><th>Počet</th><th>Podíl</th></tr>‘;
// vybirame kraje podle jejich zastoupeni
$query = mysql_query(„SELECT region.id AS id, region.name AS name, count(region.id) AS count_it FROM access JOIN town ON (area = town.id) JOIN region ON (town.region = region.id) WHERE $sql_access_date GROUP BY region.id ORDER BY count_it DESC“);
$max = mysql_result($query, 0, ‚count_it‘); // pocet pristupu z nejvice zastoupeneho kraje
$move = mysql_data_seek($query, 0); // presuneme se znovu na zacatek
while($result = mysql_fetch_array($query)) {
  $rate = sprintf(‚%.2f‘, $result[‚count_it‘]/($pageviews/100)); // sirka obrazku znazornujiciho podil
  $width = round($result[‚count_it‘]/$max*380); // podil v procentech
  echo ‚<tr class=“tableheader“><td>‘ . $result[‚name‘] . ‚</td><td>‘ . $result[‚count_it‘] . ‚</td><td><img src=“1.gif“ width=“‚ . $width . ‚“ height=“8″ alt=““ /> ‚ . $rate . ‚ %</td></tr>‘;
  $id = $result[‚id‘]; // id prave zpracovavaneho kraje
  // vybirame mesta podle jejich zastoupeni
  $query2 = mysql_query(„SELECT town.name AS name, count(town.id) AS count_it FROM access JOIN town ON (area = town.id) WHERE town.region = ‚$id‘ AND $sql_access_date GROUP BY town.id ORDER BY count_it DESC“);
  while($result = mysql_fetch_array($query2)) {
    $width = round($result[‚count_it‘]/$max*380);// sirka obrazku
    $rate = sprintf(‚%.2f‘, $result[‚count_it‘]/($pageviews/100)); // podil v procentech
    echo ‚<tr><td>‘ . $result[‚name‘] . ‚</td><td>‘ . $result[‚count_it‘] . ‚</td><td><img src=“1.gif“ width=“‚ . $width . ‚“ height=“8″ alt=““ /> ‚ . $rate . ‚ %</td></tr>‘;
  }
}
echo „</table>\n\n“;

Pozn. red.: Tento článek vyšel poprvé 1. 8. 2002. Původní verze článku a k němu vedené diskuse jsou vám k dispozici v ZIP archivech.

Starší komentáře ke článku

Pokud máte zájem o starší komentáře k tomuto článku, naleznete je zde.