Date: 02/10/05 (MySQL Communtiy)    Keywords: php, mysql, browser, database, sql

[Error: Irreparable invalid markup ('

') in entry.  Owner must fix manually.  Raw contents below.]


I've written  little PHP/MySQL script to parse HTTP requests from hidden <img> tags in LJ posts (e.g. <img src="http://ohsonline.no-ip.com/ljview.php?post-id=74765" style="display:none;" />), archiving it all in a MySQL database and providing a little analysis.  So far it's basic, and only really provides the IP, post ID, referring user, timestamp, browser, and platform.  I still need to add the source to pump out page hits based on post, ip, browser, platform, etc.  

Anyways, somebody's probably come up with a similar/better solution before, but as always, feel free to hack away :) 

ljLog - Demo



/**

@author Michael Bommarito
@version 20050209-2
@license GPL
Keep track of who views your LJ, complete with analysis by post, 
browser, operating system, and referring URL.
*/


$db = new mysqli('localhost', 'root', 'password', 'ljlog');
if( mysqli_connect_errno() ) {
	die("Unable to contact database server.  Try again later...");
}

if( isset($_REQUEST['post-id']) ) {
	$post_id = $db->real_escape_string($_REQUEST['post-id']);
	$user_ip = ip2long($_SERVER['REMOTE_ADDR']);
	$user_agent = $_SERVER['HTTP_USER_AGENT'];
	$user_referer = $_SERVER['HTTP_REFERER'];
	
	$query = "SELECT SQL_CACHE * FROM `ljview_post` WHERE `ljview_post_id` = $post_id";
	$res = $db->query($query);
	if( $res->num_rows > 0 ) {
		$user_post_id = $res->fetch_object()->ljview_post_id;
		$query = "UPDATE `ljview_post` SET `ljview_post_hits` = `ljview_post_hits` + 1 WHERE `ljview_post_id` = $post_id";
		$db->query($query);
	} else {
		$query = "INSERT INTO `ljview_post` VALUES($post_id, 1)";
		$db->query($query);
		$user_post_id = $db->insert_id;
	}
	$res->free();
	
	if( $user_ip > 0 ) {
		$query = "SELECT SQL_CACHE * FROM `ljview_ip` WHERE `ljview_ip_ip` = $user_ip";
		$res = $db->query($query);
		if( $res->num_rows > 0 ) {
			$user_ip_id = $res->fetch_object()->ljview_ip_id;
			$query = "UPDATE `ljview_ip` SET `ljview_ip_hits` = `ljview_ip_hits` + 1 WHERE `ljview_ip_ip` = $user_ip";
			$db->query($query);
		} else {
			$query = "INSERT INTO `ljview_ip` VALUES(NULL, $user_ip, 1)";
			$db->query($query);
			$user_ip_id = $db->insert_id;
		}
		$res->free();
	}
	
	if( $user_referer != '' ) {
		$query = "SELECT SQL_CACHE * FROM `ljview_referer` WHERE `ljview_referer_string` = '$user_referer'";
		$res = $db->query($query);
		if( $res->num_rows > 0 ) {
			$user_referer_id = $res->fetch_object()->ljview_referer_id;
			$query = "UPDATE `ljview_referer` SET `ljview_referer_hits` = `ljview_referer_hits` + 1 WHERE `ljview_referer_string` = '$user_referer'";
			$db->query($query);
		} else {
			$query = "INSERT INTO `ljview_referer` VALUES(NULL, '$user_referer', 1)";
			$db->query($query);
			$user_referer_id = $db->insert_id;
		}
		$res->free();
	}
	
	$query = "SELECT SQL_CACHE * FROM `ljview_user_agent` WHERE `ljview_user_agent_string` = '$user_agent'";
	$res = $db->query($query);
	if( $res->num_rows > 0 ) {
		$user_user_agent_id = $res->fetch_object()->ljview_user_agent_id;
		$query = "UPDATE `ljview_user_agent` SET `ljview_user_agent_hits` = `ljview_user_agent_hits` + 1 WHERE `ljview_user_agent_string` = '$user_agent'";
		$db->query($query);
	} else {
		$query = "INSERT INTO `ljview_user_agent` VALUES(NULL, '$user_agent', 1)";
		$db->query($query);
		$user_user_agent_id = $db->insert_id;
	}
	$res->free();
	
	$ts = time();
	$query = "INSERT INTO `ljview` VALUES (NULL, $user_ip_id, $post_id, $user_user_agent_id, $user_referer_id, $ts)";
	$db->query($query);
} else {
?>
     PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
     "DTD/xhtml1-transitional.dtd">

	
		
		
	
	
	$count = array_pop($db->query("SELECT ljview_id FROM ljview ORDER BY ljview_id DESC LIMIT 1")->fetch_row());	
	$limit = isset($_REQUEST['limit']) ? abs($_REQUEST['limit']) : 20;
	$offset = isset($_REQUEST['offset']) ? abs($_REQUEST['offset']) : 0;
	$place = $count - $offset; 
	$query = "SELECT SQL_CACHE * FROM ljview WHERE ljview_id <= $place ORDER BY ljview_ts DESC LIMIT $limit";
	$res = $db->query($query);
	$end = $offset + $limit;
	$back = $offset - $limit;
	print("
Viewing $offset - $end out of $count
");
	print("
");
	if( $offset > 0 ) {
		print("Previous $limit");
	}
	if( $offset < $count ) {
		print("Next $limit
");
	}
?>
		








	while( $view = $res->fetch_object() ) {
		print ("");

		$ip_id = $view->ljview_ip_id;
		$res_ip = $db->query("SELECT SQL_CACHE * FROM ljview_ip WHERE ljview_ip_id = $ip_id");
		if( $res_ip ) {
			$ip = $res_ip->fetch_object();
			$res_ip->free();
			print ( "" );
		} else {
			print("");
		}

		$post_id = $view->ljview_post_id;
		print("");

		if( $view->ljview_ts ) {
			print ( "" );
		} else {
			print("");
		}

		$user_agent_id = $view->ljview_user_agent_id;
		$res_user_agent = $db->query("SELECT SQL_CACHE * FROM ljview_user_agent WHERE ljview_user_agent_id = $user_agent_id");
		if( $res_user_agent ) {
			$user_agent = $res_user_agent->fetch_object();
			$res_user_agent->free();
			$browser = get_browser($user_agent->ljview_user_agent_string);
			print ( "" );
			print ( "" );
		} else {
			print("");
		}

		$referer_id = $view->ljview_referer;
		$res_referer = $db->query("SELECT SQL_CACHE * FROM ljview_referer WHERE ljview_referer_id = $referer_id");
		if( $res_referer ) {
			$referer = $res_referer->fetch_object();
			$res_referer->free();
			$referer_stack = split('[-./]', $referer->ljview_referer_string);
			if( strcasecmp($referer_stack[2], 'livejournal') == 0 ) {
				$lj_user = $referer_stack[5];
			} else if( strcasecmp($referer_stack[3], 'livejournal') == 0 ) {
				$lj_user = $referer_stack[6];
			}

			print("");
		} else {
			print("");
		}

		print ("");
	}
?>
			
				
IP
				
Post ID
				
Timestamp
				
Browser
				
Platform
				
LJ User
			
		
" . long2ip($ip->ljview_ip_ip) . "
&nbsp;
		
$post_id
		
" . date("m/d/Y, g:i:s a", $view->ljview_ts) . "
&nbsp;
		
" . $browser->parent . "
" . $browser->platform . "
&nbsp;
						
$lj_user
&nbsp;
		
		


		

			
			
		

	


}
$db->close();
?>




  `ljview_post_id` int(10) unsigned NOT NULL default '0',
  `ljview_user_agent_id` int(10) unsigned NOT NULL default '0',
  `ljview_referer` int(10) unsigned NOT NULL default '0',
  `ljview_ts` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_ip`
-- 

DROP TABLE IF EXISTS `ljview_ip`;
CREATE TABLE IF NOT EXISTS `ljview_ip` (
  `ljview_ip_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_ip_ip` int(32) NOT NULL default '0',
  `ljview_ip_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_ip_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_post`
-- 

DROP TABLE IF EXISTS `ljview_post`;
CREATE TABLE IF NOT EXISTS `ljview_post` (
  `ljview_post_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_post_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_post_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_referer`
-- 

DROP TABLE IF EXISTS `ljview_referer`;
CREATE TABLE IF NOT EXISTS `ljview_referer` (
  `ljview_referer_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_referer_string` text collate utf8_bin,
  `ljview_referer_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_referer_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

-- --------------------------------------------------------

-- 
-- Table structure for table `ljview_user_agent`
-- 

DROP TABLE IF EXISTS `ljview_user_agent`;
CREATE TABLE IF NOT EXISTS `ljview_user_agent` (
  `ljview_user_agent_id` int(10) unsigned NOT NULL auto_increment,
  `ljview_user_agent_string` text collate utf8_bin,
  `ljview_user_agent_hits` int(10) unsigned NOT NULL default '0',
  PRIMARY KEY  (`ljview_user_agent_id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;


Source: http://www.livejournal.com/community/mysql/47917.html