[PHP, MySQL] 방문자 통계 – 1. 소스

홈페이지를 운영하다 보면 가장 알고싶은것은 “내 웹사이트 또는 블로그에 몇명이나 방문했을까?” 입니다. 요즘은 블로그나 카페관리자 시스템이 잘되어있고 홈페이지툴(CMS)에서도 지원하는 기능이라 대부분 사용은 하시면서도 구현은 해보신적이 없는분들이 많습니다. 하지만 프로그래머라면 적어도 한번은 방문자 통계를 직접 구현해보는것도 좋은 경험이 될것이라 생각합니다.

아래는 제가 자주 사용하는 소스코드입니다. 여러분들의 소스는 어떨지 궁금하네요. ㅋ

MySQL Table Schema

CREATE TABLE IF NOT EXISTS 'tb_referer' (
  'seq' int(10) unsigned NOT NULL AUTO_INCREMENT,
  'referer' text,
  'user_agent_code' int(10) unsigned NOT NULL DEFAULT '0',
  'ip' int(10) unsigned NOT NULL DEFAULT '0',
  'sess_key' varchar(64) NOT NULL,
  'page_view' smallint(5) unsigned NOT NULL DEFAULT '0',
  'hash' varchar(256) DEFAULT NULL,
  'createdate' date NOT NULL DEFAULT '2000-01-01',
  'createtime' timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY ('seq'),
  KEY 'ip' ('ip','sess_key')
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tb_referer_navigate` (
  `seq` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `referer_no` int(10) unsigned NOT NULL DEFAULT '0',
  `uri` tinytext,
  `execute_time` smallint(5) unsigned NOT NULL DEFAULT '0',
  `createtime` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`seq`),
  KEY `referer_no` (`referer_no`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `tb_referer_agent` (
  `seq` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `agent_name` text,
  PRIMARY KEY (`seq`),
  KEY `agent_name` (`agent_name`(255)),
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

PHP

// referer.php

class RefererInfo extends Object {
	var	$seq;
	var	$referer;
	var	$uri;
	var	$agent_code;
	var	$agent_name;
	var	$ip;
	var	$sess_key;
	var	$bot			= false;
	var	$page_view		= 0;
	var	$execute_time	= 0;
	var	$hash;
	var	$createtime		= 0;
	
	function hash() {
		$sb	= $this->sess_key . $this->ip;
		$this->hash	= md5($sb);
	}
}

class RefererNavigate extends Object {
	var	$seq			= 0;
	var	$referer_no		= 0;
	var	$uri			= null;
	var	$execute_time	= 0;
	var	$mobile			= false;
	var	$createtime		= 0;
}

class RefererModel {
	private	$avoid_ip	= array(
		'61.247.221.111',
		'61.247.221.112',
		'66.249.84.52',
		'119.63.193.194',
		'119.63.193.195',
		'119.63.193.196',
		'121.156.121.93',
		'121.156.125.105',
		'143.248.223.196',
		'143.248.223.197',
		'143.248.223.203',
		'143.248.223.205',
		'202.179.178.140',
		'202.46.53.62',
		'202.46.63.73',
		'202.46.63.76',
		'202.46.63.79',
		'202.46.63.47'
	);
	
	private	$avoid_agent	= array(
		'Mozilla/5.0 (compatible; meanpathbot/1.0; +http://www.meanpath.com/meanpathbot.html)',
		'Mozilla/5.0 (compatible; MJ12bot/v1.4.3; http://www.majestic12.co.uk/bot.php?+)',
		'Yeti/1.0 (NHN Corp.; http://help.naver.com/robots/)',
		'Mozilla/5.0 (compatible; Googlebot/2.1; +http://www.google.com/bot.html)',
		'Mozilla/5.0 (iPhone; U; CPU iPhone OS 4_1 like Mac OS X; en-us) AppleWebKit/532.9 (KHTML, like Gecko) Version/4.0.5 Mobile/8B117 Safari/6531.22.7 (compatible; Googlebot-Mobile/2.1; +http://www.google.com/bot.html)',
		'Mozilla/5.0 (compatible; Ezooms/1.0; ezooms.bot@gmail.com)',
		'Mozilla/5.0 (compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)',
		'msnbot-media/1.1 (+http://search.msn.com/msnbot.htm)',
		'Mozilla/5.0 (seoanalyzer; compatible; bingbot/2.0; +http://www.bing.com/bingbot.htm)',
		'Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.4 (KHTML, like Gecko; Google Web Preview) Chrome/22.0.1229 Safari/537.4',
		'Mozilla/5.0 (compatible; Statsbot/2.1)',
		'Mozilla/5.0 (Windows; U; Windows NT 5.1; zh-CN; rv:1.8.0.11)  Firefox/1.5.0.11; 360Spider',
		'Mozilla/4.0 (compatible; MSIE 8.0; Windows NT 6.1; Trident/4.0; SLCC2; .NET CLR 2.0.50727; .NET CLR 3.5.30729; .NET CLR 3.0.30729; Media Center PC 6.0; MDDR; .NET4.0C; .NET4.0E; .NET CLR 1.1.4322; Tablet PC 2.0); 360Spider',
		'Wotbox/2.01 (+http://www.wotbox.com/bot/)',
		'Mozilla/5.0 (compatible; AhrefsBot/4.0; +http://ahrefs.com/robot/)',
		'Mozilla/5.0 (compatible; AhrefsBot/5.0; +http://ahrefs.com/robot/)',
		'SAMSUNG-SGH-E250/1.0 Profile/MIDP-2.0 Configuration/CLDC-1.1 UP.Browser/6.2.3.3.c.1.101 (GUI) MMP/2.0 (compatible; Googlebot-Mobile/2.1; +http://www.google.com/bot.html)',
		'DoCoMo/2.0 N905i(c100;TB;W24H16) (compatible; Googlebot-Mobile/2.1; +http://www.google.com/bot.html)',
		'Mozilla/5.0 (compatible; SiteExplorer/1.0b; +http://siteexplorer.info/)',
		'Mozilla/5.0 (compatible; Baiduspider/2.0; +http://www.baidu.com/search/spider.html)',
		'Mozilla/5.0 (compatible; Google-Site-Verification/1.0)',
		'Mozilla/5.0 (compatible; MSIE or Firefox mutant; not on Windows server; + http://tab.search.daum.net/aboutWebSearch.html) Daumoa/3.0',
		'TurnitinBot/2.1 (http://www.turnitin.com/robot/crawlerinfo.html)',
		'Googlebot-Image/1.0',
		'msnbot/2.0b (+http://search.msn.com/msnbot.htm)',
		'W3C_Validator/1.3 http://validator.w3.org/services',
		'facebookexternalhit/1.1 (+http://www.facebook.com/externalhit_uatext.php)',
		'Mozilla/5.0 (compatible; ZumBot/1.0; http://help.zum.com/inquiry)',
		'Comodo-Certificates-Spider'
	);
	
	public function doReferer($difftime = 0) {
		$UserAgent		= @$_SERVER['HTTP_USER_AGENT'];
		$ip				= $_SERVER['REMOTE_ADDR'];
		
		if(in_array($ip, $this--->avoid_ip) || in_array($UserAgent, $this->avoid_agent)) {
			return;	// Bot Pass
		}
		
		$RefererInfo	= @unserialize($_SESSION['REFERER_INFO']);

		if(!$RefererInfo) {
			$RefererInfo	= new RefererInfo();
			
			$RefererInfo->referer		= @$_SERVER['HTTP_REFERER'];
			$RefererInfo->agent_name	= @$_SERVER['HTTP_USER_AGENT'];
			$RefererInfo->agent_code	= $this->getUserAgentCode($RefererInfo->agent_name);
			$RefererInfo->ip			= ip2long($_SERVER['REMOTE_ADDR']);
			$RefererInfo->sess_key		= session_id();
			
			$this->insertReferer($RefererInfo);
		}
		
		$RefererInfo->uri			= $_SERVER['REQUEST_URI'];
		$RefererInfo->execute_time	= $difftime;
		
		$this->insertNavigate($RefererInfo);

		$_SESSION['REFERER_INFO']	= serialize($RefererInfo);
	}
	
	public function getUserAgentCode($Agent = null) {
		$rtn	= 0;
		
		$DB		=& DB::getInstance();
		
		$DB->autocommit(true);
		
		if($stmt = $DB->prepare('SELECT A.seq FROM tb_referer_agent A WHERE A.agent_name = ?')) {
			$stmt->bind_param('s', $Agent);
			$stmt->execute();
			$stmt->bind_result($rtn);
			$stmt->fetch();			
			$stmt->close();
		}
		
		if($rtn == 0) {
			$DB->autocommit(false);
			
			if($stmt = $DB->prepare('INSERT INTO tb_referer_agent SET agent_name = ?')) {
				$stmt->bind_param('s', $Agent);
				if($stmt->execute()) {
					$rtn	= $stmt->insert_id;
				}
				$stmt->close();
			}
			
			$DB->commit();
		}
		
		return $rtn;
	}
	
	public function insertReferer(RefererInfo &$RefererInfo = null) {
		$DB	=& DB::getInstance();
		
		$DB->autocommit(false);
		
		$RefererInfo->hash();
		
		if($stmt = $DB->prepare('INSERT INTO tb_referer SET referer = ?, user_agent_code = ?, ip = ?, sess_key = ?, hash = ?, createdate = CURDATE(), createtime = NOW()')) {
			$stmt->bind_param(
				'siiss',
				$RefererInfo->referer,
				$RefererInfo->agent_code,
				$RefererInfo->ip,
				$RefererInfo->sess_key,
				$RefererInfo->hash
			);
			if($stmt->execute()) {
				$RefererInfo->seq	= $stmt->insert_id;
			}
			
			$stmt->close();
		}
		
		$DB->commit();
	}
	
	public function insertNavigate(RefererInfo &$RefererInfo = null) {
		$DB	=& DB::getInstance();
		
		$DB->autocommit(false);
		
		if($stmt = $DB->prepare('INSERT INTO tb_referer_navigate SET referer_no = ?, uri = ?, execute_time = ?, createtime = NOW()')) {
			$Layout	=& Layout::getInstance();
			
			$stmt->bind_param(
				'isi',
				$RefererInfo->seq,
				$RefererInfo->uri,
				$RefererInfo->execute_time
			);
			$stmt->execute();
			$stmt->close();
		}
		
		if($stmt = $DB->prepare('UPDATE tb_referer SET page_view = page_view + 1 WHERE seq = ?')) {
			$stmt->bind_param('i', $RefererInfo->seq);
			$stmt->execute();
			$stmt->close();
		}
		
		$DB->commit();
	}
}

Referer::getInstance();

사용중인 소스코드에서 불필요한 부분을 제거하고 업로드 하는지라 테스트는 안해봤지만 정상 동작할거라 믿습니다.! (Faith Driven Development).
이제 모든 페이지에서 위 파일을 include 해주고 instance만 얻어와 주면 페이지가 종료될때(destruct)마다 Database에 입력이 되는것을 확인하실 수 있습니다.

include('referer.php');

You may also like...

답글 남기기

이메일은 공개되지 않습니다.

Protected by WP Anti Spam