Date: 02/09/05 (PHP Community)    Keywords: php, mysql, browser, database, sql

[Error: Irreparable invalid markup ('

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


Lately, I've been adding hidden <img> tags to my posts.  The source attribute points to non-existent URLs on my server, each tagged to the post ID - aka: http://ohsonline.no-ip.com/lj74252.  Simple little way to see whenever someone reads your journal, or whenever somebody checks out a friends page you're listed on.  Not the best solution, but hey, it works.

Last night, I got bored, and still a little out of it from being sick, decided to work on a little PHP/MySQL script to actually collect all of that data coming in and provide a little analysis.  So far it's basic, and only really provides the IP, post ID, 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 20050208-1
@license LGPL
Keep track of who views your LJ, complete with analysis by post, 
browser, operating system, and referring URL.  Or it will.  Some day.
*/

$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 * 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 * 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 * 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 * 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">

	
		
		
	
	
		







	$res = $db->query("SELECT * FROM ljview ORDER BY ljview_id DESC");
	while( $view = $res->fetch_object() ) {
		print ("");

		$ip_id = $view->ljview_ip_id;
		$res_ip = $db->query("SELECT * 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 * 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("");
		}

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

		

			
			
		

	


}
$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/php/257133.html