Keskustelu: Nettisivujen teko: Vierastiedot MySql:llä

Elikkä, idea on tämä: vierastietojen kerääminen mysliin. Halusin mahd. yksinkertaisen kävijälaskurin, josta saa seureevat tiedot helposti ulos:

-vieraiden määrän
-vieraiden määrän/päivää kohden
-latauksien määrän
-latauksien määrän/päivää kohden
-latausten määrän/sivu
-kunkin vieraan latausten määrän/sivu

Päädyin seuraavaan ratkaisuun:

Kaksi taulua mysliin:

Ensimmäisessä taulussa id on auto_increment (TSTMP = aikaleima). Tauluun lisätään rivi vain, jos viimeisestä käynnistä on yli tunti.

|-----------------------------------------------------------------------------|
|id | TSTMP     | PVM       | IP     | HOST  | AGENT     | REFERER  |LATAUKSET|
|-----------------------------------------------------------------------------|
|1  |1079817688 |20.03.2004.|0.0.0.0 |host.fi|Opera/6.05 | http //..|10       |
|-----------------------------------------------------------------------------|

Toiseen tauluun tallennetaan vieraan id, joka on sama kuin ensimmäisessä taulussa, sekä lataukset sivua kohden.

|-----------------------------------|
|id | sivu1 | sivu2 | sivu3 | sivu4 |
|-----------------------------------|
|1  |10     |5      |7      |11     |
|-----------------------------------|

Ja sitten koodi, jolla tämä on toteutettu:

<?php
function dbq($kysely){ //dbq = database query
	require("salasanat.php");
    $connu = mysql_connect("localhost",$tunnus,$salasana) or die ("<strong>Yhteys tietokantaan epäonnistui.</strong>");
    mysql_select_db($tietokanta, $connu);
    if(!($vastaus =  mysql_query($kysely, $connu))) $vastaus = false;
    mysql_close($connu);
    return $vastaus;
}

//  Otetaan muuttujiin kaikki olennainen tieto vieraasta
	if(!($page = $_GET['page'])) $page = "Etusivu";
	$ip = $_SERVER['REMOTE_ADDR'];
	$time = time();
	$pv = date("j.m.Y.");
	$agent = $_SERVER['HTTP_USER_AGENT'];
	$host = gethostbyaddr($REMOTE_ADDR);
	$ref = $_SERVER['HTTP_REFERER'];

//kokeillaan löytyykö tietokannasta käyttäjän ip:tä
$vast = dbq("SELECT MAX(id) AS id FROM vtiedot WHERE IP = '$ip'");
$row = mysql_fetch_array($vast);
//jos ip:tä ei löydy tietokannasta lisätään se sinne
if(!$userid = $row['id']) {
	//lisätään tiedot...
	dbq("INSERT INTO vtiedot (TSTMP,PVM,IP,HOST,AGENT,REFERER,LATAUKSET)
		VALUES ('$time','$pv','$ip','$host','$agent','$ref','0')");
	//haetaan vieraan id ip:n perusteella
	$vast = dbq("SELECT MAX(id) AS id FROM vtiedot WHERE IP = '$ip'");
	$row = mysql_fetch_array($vast);
	$userid = $row['id'];
	//ja lisätään "lataukset" -tauluun käyttäjän uusi rivi (vieraan id)
	dbq("INSERT INTO lataukset (ID) VALUES ('$userid')");
}

//Haetaan vieraan aikaleima tietokannasta
$vast = dbq("SELECT TSTMP FROM vtiedot WHERE id = '$userid'");
$row = mysql_fetch_array($vast);
$tstmp = $row['TSTMP'];
//jos viimeisestä käynnistä on yli tunti lisätään käyttäjä uudestaan vierastietoihin
if((time() - $tstmp) > 3600) {
	//lisätään tiedot...
	dbq("INSERT INTO vtiedot (TSTMP,PVM,IP,HOST,AGENT,REFERER,LATAUKSET)
		VALUES ('$time','$pv','$ip','$host','$agent','$ref','0')");
	//haetaan vieraan id ip:n perusteella
	$vast = dbq("SELECT MAX(id) AS id FROM vtiedot WHERE IP = '$ip'");
	$row = mysql_fetch_array($vast);
	$userid = $row['id'];
	//ja lisätään "lataukset" -tauluun käyttäjän uusi rivi (vieraan id)
	dbq("INSERT INTO lataukset (ID) VALUES ('$userid')");
	}
//lisätään "lataukset" -tauluun uusi sarake varmuuden vuoksi, joka on $page-muuttujassa oleva
//tulostettava sivu
dbq("ALTER TABLE `lataukset` ADD `$page` INT( 4 ) DEFAULT '0' NOT NULL");
//lisätään "lataukset" -tauluun vieraan id-riville hitti sen sivun kohdalle, joka ladataan
dbq("UPDATE lataukset SET $page = $page + 1 WHERE id = '$userid'");
//lisätään käyttäjän latauksia yhdellä
dbq("UPDATE vtiedot SET lataukset = lataukset + 1 WHERE id = '$userid'");
?>

Nyt kokonaislatausmäärän saa helposti selville "SELECT SUM(LATAUKSET) FROM vtiedot" ja lataukset sivua kohden helposti "SELECT SUM(sivu1) FROM lataukset"

Koodissa ja koko systeemissä ei sinänsä ei ole vikaa, mutta haluaisin tietää onko tämä järkevä ratkaisu? Tietokannan suunnittelu on yllättävan vaikeata :-). Ja varsinkin MySql-koodin suhteen varmasti löytyy optimointia, sen verran aloittelija olen myslin suhteen. Aikamoista purkkaa tuli ainakin omasta mielestä, kun muutaman kaljan jälkeen lähtee tommosta väsäämään.

Kommentteja kiitos!

Et sitten ajatellut heittää tuota koodivinkiksi?

Skriptisi on varmaan ihan hyvä, mutta vie tuollaisenaan uskomattoman määrän resursseja palvelimelta. Ensinnäkään ei ole mitään järkeä avata ja sulkea tietokantayhteyttä jokaisen kyselyn jälkeen, eli skriptin alussa avaus ja lopussa sulkeminen on paljon parempi vaihtoehto.

Itse olen toteuttanut lähes vastaavan systeemin yhdellä taululla. Ja jos löydän vielä sen jostain voin vaikka kertoa enemmän sen toiminnasta.

DSword kirjoitti:

Et sitten ajatellut heittää tuota koodivinkiksi?

No voisihan tuosta vinkinkin väsätä, kuhan sen saa nyt ensiksi järkevästi toteutettua.

ederth kirjoitti:

Skriptisi on varmaan ihan hyvä, mutta vie tuollaisenaan uskomattoman määrän resursseja palvelimelta.

Sama ajatus kävi mielessä, kun aloin tuota toteuttaa. Minimissään tehdään viisi kyselyä tietokantaan ja onhan se aika paljon pelkäksi kävijälaskuriksi. Vaikka yritin ja selasin MySql:n nettimanuaalia, niin en löytänyt ratkaisua, miten nämä kaksi kyselyä voisi yhdistää

"SELECT MAX(id) AS id FROM vtiedot WHERE IP = '$ip'"
"SELECT TSTMP FROM vtiedot WHERE id = '$userid'"

Ja sitten tämä:

"ALTER TABLE `lataukset` ADD `$page` INT( 4 ) DEFAULT '0' NOT NULL"

Nyt scripti siis yrittää lisätä joka kerta taluun uuden sarakkeen. Turha kysely siis, koska 99% sivuista on jo todennäköisesti taulussa. Ja tähän onkin todella yksinkertainen ratkaisu, jonka juuri NYT hokasin( mysql_affected_rows() ). Mutta jos nuo kaksi ylempää kyselyä saisi yhdistettyä, niin kyselyjen määrä tippuisi kolmeen. Se on varmaan jo siedettävä määrä monipuolisemmassa kävijälaskurissa. Ja tosiaan, täytyy muuttaa toi, että yhteys avataan ja suljetaan vain kerran.

Viisi queryä on kyllä ihan liikaa pelkälle kävijälaskurille.
Mielestäni riittää että tallennat jokaisen sivulatauksen yhteydessä uuden rivin sinne tietokantaan, johon siis tallennetaan vaikka request, aikaleima, ip sekä useragent.

Sitten erillisellä sivulla, vaikka jokin tilastot sivu, luetaan taulu muuttujaan ja poistetaan luetut tiedot taulusta, näistä tiedoista muodostetaan tilasto ja tallennetaan se toiseen tauluun taikka tekstitiedostoon, tämä siis vaikka kerran päivässä.

edit: "SELECT TSTMP FROM vtiedot WHERE ip = '$ip' ORDER BY id DESC LIMIT 0, 1"

Kiitos ederth. Yhdistin taulut ja nyt koko homma menee läpi kahdella queryllä ja se riittää minulle.