Statistika přístupů v PHP – regionální lokalizace -- PHP -- Vývoj aplikací -- Interval.cz

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

Regionální lokalizací navážeme na předchozí díl. Popíši, jak zjistíte kraj, okres, město či vysokou školu, ze které se návštěvník stránek připojuje. Krátce se zmíním i 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 www.czechia.cz/help.

Jediný způsob, jak provést regionální lokalizaci, je zpracování doménové adresy počítače, o které jsme hovořili již v minulém dílu. 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é označují města nebo vysoké školy.

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 14 č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),
    PRIMARY KEY (id)
);

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

id	name
0	Ostatní
1	Praha
2	Středočeský kraj
3	Jihočeský kraj
4	Plzeňský kraj
5	Karlovarský kraj
6	Ústecký kraj
7	Liberecký kraj
8	Královéhradecký kraj
9	Pardubický kraj
10	Vysočina
11	Jihomoravský kraj
12	Zlínský kraj
13	Olomoucký kraj
14	Moravskoslezský kraj
15	Vysoké školy
16	Slovensko

Tabulka town

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

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

id - unikátní označení každého města, VŠ, … (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. Krátký výtah z tohoto souboru obsahuje následující tabulka:

ppp250.hk.worldonline.cz	olcD001.nextra.cz
ppp96.brno.worldonline.cz	ta005.velkemezirici.czech.net
ppp151.mb.worldonline.cz	kxchav07.vse.cz
ostravaa-162.dialup.vol.cz	a6-22.natur.cuni.cz
varya-61.dialup.vol.cz	dial1-041.ba.nextra.sk
ustia-147.dialup.vol.cz	diamant.sjf.tuke.sk

Klíčová je 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 "|". Regulární výraz ".*" označuje libovolný řetězec. Chceme-li použít metaznak v jeho původním významu, musíme před něj napsat "\". 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 k chybné detekci. V uvedeném příkladu by to mohlo být třeba u adresy isslanstr.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ě. Všechny tyto údaje (od id 201) se sice netýkají vlastní regionální lokalizace, ale 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.

id	name	ip_regexp	region
0	Ostatní		0
1	Hradec Králové	hk\.worldonline\.cz\|hradec\|hrk.*nextra\.cz	8
2	Brno	brno\|brn.*nextra\.cz	11
3	Mladá Boleslav	mb\.worldonline\.cz\|boleslav	2
4	Praha	praha\|pha\.gts\.cz\|phu.*nextra\.cz	1
5	Ostrava	ostrava\|ova.*nextra\.cz	14
6	Kladno	kladno	2
7	Tábor	tabor	3
8	Karlovy Vary	vary\|kva.*nextra\.cz	5
9	Plzeň	plzen\|plz.*nextra\.cz	4
10	Pardubice	pce\.worldonline\.cz\|pardubice	9
11	České Budějovice	cb\.worldonline\.cz\|budejice	3
12	Opava	opava	14
13	Třinec	trinec	14
14	Ústí nad Labem	usti.*vol\.cz\|ul\.worldonline\.cz	6
15	Náchod	na\.worldonline\.cz	8
16	Velké Meziříčí	velkemezirici	10
17	Teplice	teplice	6
18	Ústí nad Orlicí	uo\.worldonline\.cz	9
19	Olomouc	olomouc\|olc.*nextra\.cz	13
20	Rychnov nad Kněžnou	rychnov	8
21	Znojmo	znojmo	11
22	Děčín	decin	6
23	Přerov	prerov	13
24	Liberec	liberec\|lbc.*nextra\.cz	7
25	Ždár nad Sázavou	zdar	10
26	Příbram	pribram	2
27	Chomutov	chomutov	6
28	Písek	pisek	3
29	Břeclav	breclav	11
30	Kroměříž	kromeriz	12
31	Zlín	zlin	12
32	Polička	policka	9
33	Sokolov	sokolov	5
34	Havlíčkův Brod	hb\.worldonline\.cz	10
35	Jablonec nad Nisou	jablonec	7
36	Jihlava	jihlava	10
37	Třebíč	trebic	10
38	Kutná hora	kutnahora	2
39	Trutnov	trutnov	8
40	Chrudim	chrudim	9
41	Šumperk	sumperk	13
42	Krnov	krnov	14
43	Cheb	cheb	5
44	Kyjov	kyjov	11
45	Brandýs nad Labem	brandys	2
46	Prostějov	prostejov	13
47	Sušice	susice	4
48	Vsetín	vsetin	12
49	VUT Brno	vutbr\.cz	15
50	UK Praha	cuni\.cz	15
51	ČVUT Praha	cvut\.cz	15
52	VŠE Praha	vse\.cz	15
53	VŠB TU Ostrava	vsb\.cz	15
54	ZČU Plzeň	zcu\.cz	15
55	MU Brno	muni\.cz	15
57	Mendelova uni. Brno	mendelu\.cz	15
58	JČU	jcu\.cz	15
59	TU Liberec	vslib\.cz	15
60	UHK	uhk\.cz	15
61	Univerzita Pardubice	upce\.cz	15
62	ČZU Praha	czu\.cz	15
63	Uni. Palackého - Olomouc	upol\.cz	15
64	Uni. J. E. Purkyně - UL	ujep\.cz	15
65	VŠCHT Praha	vscht\.cz	15
67	Ostravská univerzita	osu\.cz	15
68	VŠ vojenská Vyškov	vvs-pv\.cz	15
69	Vojenská akademie Brno	vabo\.cz	15
70	Vojenská lék. ak. JEP - HK	pmfhk\.cz	15
71	Slezská u. Opava	slu\.cz	15
72	Košice	ke\.nextra\.sk	16
73	Bratislava	ba\.nextra\.sk	16
74	TU Košice	tuke\.sk	16
75	Most	most	6
76	Česká Lípa	lipa	7
77	Uni. P. J. Šafárika Košice	upjs\.sk	16
78	Uni. Komenského Bratislava	uniba\.sk	16
79	Zvolen	zv\.nextra\.sk	16
80	Trnava	tt\.nextra\.sk	16
81	Žilina	za\.nextra\.sk	16
82	Nitra	nr\.nextra\.sk	16
83	Poprad	po\.nextra\.sk	16
84	Trenčín	tn\.nextra\.sk	16
85	Martin	mt\.nextra\.sk	16
201	Contactel (cz)	contactel\.cz	0
202	IOL (cz)	iol\.cz	0
203	PVT Net (cz)	pvtnet\.cz	0
204	CZ Com (cz)	czcom\.cz	0
205	TELE2 (cz)	tele2\.cz	0
206	Telecom (sk)	telecom\.sk	0
207	Globtelnet (sk)	globtelnet\.sk	0
208	PVT (sk)	pvt\.sk	0
209	Kiwwi (sk)	kiwwi\.sk	0
210	Mistral (UPC)	mistral\.cz	0
301	Ostatní CZ	\.cz	0
302	Ostatní SK	\.sk	0
303	Ostatní COM	\.com	0
304	Ostatní NET	\.net	0

Skript, který provede detekci města, je poměrně krátký a jednoduchý. Z minulého dílu 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, o kterém jsem se zmiňoval již v druhém dílu tohoto seriálu. Příkaz ORDER BY id je použit kvůli tomu, ž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
// odpovídá doménová adresa nějakému záznamu v databázi ?
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"];
    else
        $area = 0;
else:
    $area = 0;
endif;

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, 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 tři tabulky region, town a access. Na první pohled může podmínka spojující 3 tabulky vypadat krkolomně, ale pokud si uvědomíte vztahy mezi tabulkami (primární a cizí klíče) znázorněné 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, tj. výpočet podílu a šířky obrázku, byl komentován ve třetím dílu.

// OBLAST
echo '<p align="center" class="heading">Kraje, města, oblasti</p>';
echo '<table width="680" cellspacing="0" align="center">';
echo '<tr><td width="150" class="tableheader">Oblast</td><td width="50" class="tableheader">Počet</td><td class="tableheader" width="480">Podíl</td class="tableheader">';

// vybíráme kraje podle jejich zastoupení
$query = MySQL_Query("SELECT region.id AS id, region.name AS name, count(*) AS count_it FROM region, town, access WHERE area = town.id AND town.region = region.id AND $sql_access_date GROUP BY region.id ORDER BY count_it DESC");
$max = MySQL_Result($query, 0, "count_it"); // počet přístupů z nejvíce zastoupeného kraje
$move = MySQL_Data_Seek($query, 0); // přesuneme se znovu na začátek

while($result = MySQL_Fetch_Array($query)):
    $rate = BCDiv($result["count_it"], ($pageviews/100), 2); // šířka obrázku znázorňujícího podíl
    $width = Round($result["count_it"]/$max*400); // podíl v procentech
    echo '<tr><td class="tableheader">' . $result["name"] . '</td><td class="tableheader">' . $result["count_it"] . '</td><td class="tableheader"><img src="1.gif" width="' . $width . '" height="8" border="0" alt=""> ' . $rate . ' %</td></tr>';

    $id = $result["id"]; // id právě zpracovávaného kraje

    // vybíráme města podle jejich zastoupení
    $query2 = MySQL_Query("SELECT town.name AS name, count(*) AS count_it FROM town, access WHERE area = town.id AND 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*400);    // šířka obrázku znázorňujícího podíl
        $rate = BCDiv($result["count_it"], ($pageviews/100), 2); // podíl v procentech
        echo '<tr><td>' . $result["name"] . '</td><td>' . $result["count_it"] . '</td><td><img src="1.gif" width="' . $width . '" height="8" border="0" alt=""> ' . $rate . ' %</td></tr>';
    endwhile;
endwhile;

echo '</table><br>';

V příštím dílu se dozvíte, jak vytvořit statistiku hodinové (0-23) a denní (Po-Ne) návštěvnosti. K tomu si přidáme ještě statistiku jednotlivých stránek našeho serveru.

Předchozí články:
Statistika přístupů v PHP – domény nejvyšší úrovně
Statistika přístupů v PHP – vyhledávací fráze
Statistika přístupů v PHP – odkud přicházejí návštěvníci
Statistika přístupů v PHP – rozlišení a barevná hloubka
Statistika přístupů v PHP – detekce operačního systému
Statistika přístupů v PHP – detekce prohlížeče
Statistika přístupů v PHP – počet unikátních návštěvníků

Kebrt, Michal (1. 8. 2002)


2002-08-01 10:01:29	Marek Jelen	Stahnuti
2002-08-01 10:01:31	Marek Jelen	Stahnuti
2002-08-01 14:39:41	Marek Šalanda	Stahnuti
2002-08-04 18:48:01	Mirek	Cely skript
2002-08-04 20:57:04	Michal Kebrt	Cely skript
2002-08-20 16:08:21	Michal Terč	Pokračování
2002-08-27 16:21:56	Leo	Je to sice pekne...
2002-08-30 18:25:01	Michal Kebrt	Je to sice pekne...