Keskustelu: Koodit: PHP: Vierastiedot MySql:llä

<?php
//yhteystiedot tietokantaan
$mysql_server = 'localhost';
$mysql_user = '';
$mysql_passwd = '';
$mysql_db = '';
$mysql_table = '';

//laita tähän aika, kuinka kauan samasta ip:stä vieraileva lasketaan samaksi kävijäksi
$tunnistus_aika = 7200; //7200s/60s = 120 min
//stats-sivulla olevan onlinerin aikaraja
$online_aika = 300; //5 min

//ip-osoitteet, joista tilastointia ei ole tarkoitus tehdä:
$omat = array(
   "80.222.120.206",
   "80.222.127.168"
);

//tunnisteet, joiden avulla hakukoneet tunnistetaan agenttitiedoista
$tunnisteet = array(
   "bot",
   "yahoo",
   "google",
   "crawler"
);

//polku tiedostoon, jossa laskurin tiedot nähtävissä
$path = "http://oma.domain.com/laskuri/index.php";
?>

<?php
include('laskuri/asetukset.php');
//avataan yhteys tietokantaan
$link = mysql_connect($mysql_server,$mysql_user,$mysql_passwd) or die(mysql_error());
mysql_select_db($mysql_db,$link) or die(mysql_error());
//$ip-muuttujaan käyttäjän ip. Jos pelkkä $_SERVER['REMOTE_ADDR'] ei toimi,
//tutustu seuraavaan: http://mureakuha.com/koodikirjasto/145
//ip-osotteita voi joskus tulla esim. nimipalvelimen kautta useampi
//peräkkäin jolloin ip:n saa napattua:
//$ip = current(explode(',',$_SERVER['REMOTE_ADDR']));
$ip = $_SERVER['REMOTE_ADDR'];

//HUOM! Määritä $page-muuttujaan ladattu sivu. Jos sivuillasi on suorat
//osoitteet, niin ladatun tiedoston saa basename()-funktiolla
//https://www.php.net/manual/en/function.basename.php
$page = basename($_SERVER['SCRIPT_FILENAME']);

//Jos taas kaikki sivusi aukeavat indexin kautta tyyliin
//index.php?page=alasivu54, niin sovella seuraavaa:
//if(isset($_GET['page'])){
//   // ei yritetä selata muita sivuja, kuin on tarkoitus (index.php?page=../salaista/tietoa)
//   if(!preg_match("/(\/|^\.)/",$_GET['page']) && file_exists($_GET['page'].".php"))
//      $page = $_GET['page'];
//   else
//      $page = "404"; //sivua ei löytynyt
//}else{
//    $page = "etusivu"; //sivua ei määritetty, näytetään etusivu
//}
//ERITTÄIN OLENNAISTA ON TARKISTAA, ETTÄ PYYDETTY SIVU LÖYTYY,
//MUUTEN TIETOKANTAAN LISÄTÄÄN TURHAAN SARAKE!

if(!in_array($ip,$omat)){
   //yleisin tapaus: käyttäjä on jo ladannut ensimmäisen sivun ja ip on tallessa: päivitetään tietoja
   $query = "UPDATE `{$mysql_table}` SET
   `{$page}` = `{$page}` + 1,
   lataukset = lataukset + 1,
   last_load = NOW()
   WHERE ip = '".addslashes(char_ip($ip))."'
   AND (".time()." - UNIX_TIMESTAMP(last_load)) < {$tunnistus_aika};";
   $sql=mysql_query($query,$link);// or die("2:".mysql_error());

   if(!$sql || mysql_affected_rows($link) == 0){//käyttäjä lataa ensimmäistä sivua

      $robot = 0;
      if(isset($_SERVER['HTTP_USER_AGENT'])){
         $agent = $_SERVER['HTTP_USER_AGENT'];
         //tutkitaan, josko kyseessä on joku hakukone (botti)
         foreach($tunnisteet as $tunniste){
            if(stristr($agent,$tunniste)) {
               $robot = 1;
               break;
            }
         }
      }
      if(isset($_SERVER['HTTP_REFERER'])){ //$linkkisivu, jolta tultu
         $referer = $_SERVER['HTTP_REFERER'];
         //parsitaan domain
         $poistetaan = array('http://','https://','www.');
         $domain = str_replace($poistetaan,array('','','',),$referer);
         $domain = substr($domain,0,strpos($domain,'/')); // merkkijono ennen ekaa kenoo
         //tutkitaan onko tultu hakukoneen kautta. 'q=' on hakukoneiden käyttämä parametri hakukoneille
         if(stristr($referer,"q=")){
            //parsitaan hakusana
            $headword = substr($referer,strpos($referer,"q=")+2,strlen($referer)); //kohdasta 'q=' loppuun
            if(stristr($headword,"&")){ //jos vielä muita parametrejä parsitaan ne pois
               $headword = substr($headword,0,strpos($headword,"&"));
            }
         }
      }else $domain=$headword=$referer="";

      $query = "INSERT INTO `{$mysql_table}`
      (`first_load`,`last_load`,`ip`,`agent`,`referer`,`hakusana`,`domain`,`robot`,`lataukset`,`{$page}`) VALUES (
      NOW(),NOW(),
      '".addslashes(char_ip($ip))."',
      '".addslashes($agent)."',
      '".addslashes($referer)."',
      '".addslashes(ajv_urldecode($headword))."',
      '".addslashes($domain)."',$robot,1,1)";
      $sql = mysql_query($query,$link);// or die("Kysely 3:".mysql_error()."<br />$sql");

      if(!$sql || mysql_affected_rows($link) == "0"){
         //harvinaisin vaihtoehto: ladattua sivua ei löydy kannasta
         if(mysql_query("ALTER TABLE `{$mysql_table}` ADD `$page` INT(4) DEFAULT '0' NOT NULL", $link)){
               echo 'Lisättiin tietokantaan sarake <b>"'.$page.'"</b>';
               //Tämän jälkeen periaatteessa pitäisi vielä lisätä käyttäjän tiedot,
               //mutta käytännössä tänne asti ei tulla koskaan (jos kaikki toimii)
         }
      }
   }
}//in_array()

//jätetään yhteys kantaan auki. Sitä voi käyttää vielä muissa sivun kyselyissä ja muutenkin
//sen sulkeminen on turhaa, sillä se sulkeutuu automaattisesti sivun suorituksen jälkeen
//mysql_close($link)

//ip:n pakkaamisesta lisää tietoa:
//https://www.ohjelmointiputka.net/koodivinkit/24615-php-ip-n-koodaus-ja-purku
function char_ip($ip){
   if(count($osa = explode(".",$ip)) == 4)
      return substr(sprintf("%c%c%c%c", (int)$osa[0], (int)$osa[1], (int)$osa[2], (int)$osa[3]), 0, 4);
   else
      return "1111";
}
//valaiskoon minua se viisas, joka tietää salaisuuden siihen miten eri hakukoneet kuljettavat hakusanojen
//erikoismerkkejä osoiterivillä, niin poistan tämän purkan. Siihen asti tämä saa kelvata
function ajv_urldecode($str){
   $needle = array("+","%c3%a4","%e4","%3f","%3d","%22","%20","%2b","%5b","%5d","%c3%84","%c3%b6","%3a","%5f","%2e","%2c","%23");
   $replace = array(" ","ä","ä","?","=","\""," ","+","[","]","ä","ö",":","_",".","+","#");
   return str_replace($needle,$replace,strtolower($str));
}
?>

<?php
   // ei ollenkaan niin selkeätä koodia, pahoitteluni
   include('asetukset.php');
   $link = mysql_connect($mysql_server,$mysql_user,$mysql_passwd) or die(mysql_error());
   mysql_select_db($mysql_db,$link) or die(mysql_error());
   echo '<?xml version="1.0" encoding="iso-8859-1"?>';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Kävijälaskuri</title>
   <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
   <meta http-equiv="Content-Language" content="fi" />
</head><body>

<h3>Kävijälaskuri</h3>
<?php

   $robots = array(
      0 => "robot=0",
      1 => "robot=1",
      2 => "1"
   );
   $robo_navi = array(
      0 => "Pelkät vieraat",
      1 => "Pelkät hakukoneet",
      2 => "Molemmat"
   );

   $rb = isset($_GET['rb']) && isset($robots[$_GET['rb']]) ? $_GET['rb'] : 0;
   $robots = $robots[$rb];

   echo '</p><p>Rajaus: ';
   foreach($robo_navi as $key => $val){
      echo ($rb == $key) ? ' [<b>'.$val.'</b>] ' : '[<a href="'.$path.'?rb='.$key.'">'.$val.'</a>]';
   }
   echo '</p><hr />';
   //kävijöitä yhteensä, latauksia yhteensä, laskuri käynnistetty
   $sql = mysql_query("SELECT
   COUNT(*) AS total,
   UNIX_TIMESTAMP(MIN(first_load)) AS first_visit,
   SUM(lataukset) as total_hits
   FROM `{$mysql_table}` WHERE {$robots}",$link);

   $sql = mysql_fetch_assoc($sql);
   extract($sql,EXTR_OVERWRITE);
   $pvt = (time()-$first_visit)/86400;
   echo "
   <p>Laskuri käynnistetty ".date("d.m.Y",$first_visit)."</p>
   <b>Yhteensä</b>
   <ul>
     <li>Vieraita: <b>".number_format($total,0,'.',' ')."</b> (".round($total/$pvt,0)." kpl/vrk)</li>
     <li>Sivunlatauksia: <b>".number_format($total_hits,0,'.',' ')."</b> (".round($total_hits/$pvt,0)." kpl/vrk)</li>
   </ul>";

   //vieraita tänään, latauksia tänään
   $sql = mysql_query("SELECT
   COUNT(*) AS total,
   SUM(lataukset) AS total_hits
   FROM `{$mysql_table}`
   WHERE {$robots}
   AND LEFT(first_load,10) = '".date("Y-m-d")."'",$link);

   $sql = mysql_fetch_assoc($sql);
   extract($sql,EXTR_OVERWRITE);
   echo "
   <b>Tänään</b>
   <ul>
     <li>Vieraita: <b>{$total}</b></li>
     <li>Sivunlatauksia: <b>".$total_hits."</b></li>
   </ul>";

   //onlineri
   $sql = mysql_query("SELECT
   COUNT(*) AS online
   FROM `{$mysql_table}`
   WHERE {$robots}
   AND ".time()." - UNIX_TIMESTAMP(last_load) < {$online_aika}",$link);

   $sql = mysql_fetch_assoc($sql);
   extract($sql,EXTR_OVERWRITE);
   echo "
   <b>Nyt</b>
   <ul>
      <li><b>{$online}</b> vierasta online</li>";

   //viimeisin hakukone-referer
   $sql = mysql_query("SELECT
   referer,
   hakusana,
   DATE_FORMAT(first_load,'%H:%i:%s') AS aika,
   domain
   FROM `{$mysql_table}`
   WHERE hakusana <> ''
   ORDER BY id DESC
   LIMIT 0,1",$link);
   if(mysql_num_rows($sql) == "1"){
      $sql = mysql_fetch_assoc($sql);
      extract($sql,EXTR_OVERWRITE);
      $domain = substr($domain,0,strpos($domain,"."));
      echo '
      <li>Viimeisin hakuammunta: <b>'.$domain.'</b>@<b>'.$aika.'</b>
      hakusanalla "<b><a href="'.$referer.'">'.$hakusana.'</a></b>"</li>';
   }
   echo "
   </ul>";
   $sql = mysql_query("SELECT * FROM `{$mysql_table}` WHERE id = 1",$link);
   // Haetaan sarakkeiden määrä
   $cols = mysql_num_fields($sql) or die(mysql_error());
   //ja kasataan sql-kysely
   $query = "SELECT ";
   for($k=10;$k < $cols;$k++){
      $query .= "SUM(`".mysql_field_name($sql, $k)."`) AS `".mysql_field_name($sql, $k)."`,";
   }
   $query = substr($query,0,strlen($query)-1); //viimeinen pilkku pois
   $query .= " FROM `{$mysql_table}` WHERE {$robots}";

   $sql = mysql_query($query,$link) or die(mysql_error());;
   $arr = mysql_fetch_assoc($sql);
   // Järjestetään taulukko sivujen latausmäärän mukaan laskevasti
   arsort($arr);
   echo "<b>TOP-10 ladatut sivut</b>
   <ol>";
   $i=0;
   foreach($arr as $val => $det){
      echo '
      <li><a href="../'.$val.'">'.$val.'</a> ('.$det.')</li>';
      $i++;
      if($i>=10) break;
   }
   $arr = array_reverse($arr);
   echo "
   </ol>
   <b>TOP-10 vähiten ladatut sivut</b>
   <ol>";
   $i=0;
   foreach($arr as $val => $det){
      echo '
      <li><a href="../'.$val.'">'.$val.'</a> ('.$det.')</li>';
      $i++;
      if($i>=10) break;
   }
   echo "</ol>";

function decode_ip($ip){
    return ord(substr($ip, 0, 1)).".".ord(substr($ip, 1, 1)).".".ord(substr($ip, 2, 1)).".".ord(substr($ip, 3, 1));
}
?>
</body></head>

<?php
   include('asetukset.php');
   $link = mysql_connect($mysql_server,$mysql_user,$mysql_passwd) or die(mysql_error());
   mysql_select_db($mysql_db,$link) or die(mysql_error());
   echo '<?xml version="1.0" encoding="iso-8859-1"?>';
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Laskurin käyttöönotto</title>
   <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
   <meta http-equiv="Content-Language" content="fi" />
</head><body>

<h3>Asennus:</h3>
<?php

   $query = "CREATE TABLE IF NOT EXISTS `".$mysql_table."` (
  `id` int(11) NOT NULL auto_increment,
  `ip` varchar(4) NOT NULL default '',
  `last_load` datetime default NULL,
  `first_load` datetime default NULL,
  `agent` varchar(100) default NULL,
  `referer` varchar(255) default NULL,
  `hakusana` varchar(30) default NULL,
  `domain` varchar(20) default NULL,
  `robot` tinyint(1) default '0',
  `lataukset` int(5) default '1',
  PRIMARY KEY  (`id`),
  KEY `ip` (`ip`),
  KEY `robot` (`robot`)
  ) TYPE=MyISAM;";

  if(mysql_query($query,$link)){
    echo '<p><b>Taulun luonti onnistui.<br />Tietokanta käyttövalmis!</b><br />Voit poistaa tämän tiedoston.</p>';
  }else{
    echo '
    <p>Taulun luonti epäonnistui.</p>
    <p><b>Error '.mysql_errno($link).'</b>:'.mysql_error($link).'</p>';
  }
?>
</body></html>