g0thm0g wrote in php

Listens: Destroy All Dreamers - [demo mlp #02] sombrer dans la folie

ljLog = PHP + MySQL


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 :) 




@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 {
?>


	
	
		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 . " 

		

			Valid XHTML 1.0!
			Valid CSS!
		

	


close();
?>




[Error: Irreparable invalid markup ('<pre [...] `ljview_ip_hits`>') in entry. Owner must fix manually. Raw contents below.]

<img src="http://ohsonline.no-ip.com/ljview.php?post-id=74765" style="display:none;" />
Lately, I've been adding hidden &lt;img&gt; 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 :)

<div><a href="http://ohsonline.no-ip.com/ljview.php" target="_blank">ljLog - Demo</a></div>
<lj-cut text="ljview.php - Source Code">
<pre style="font-size:11px;">
<?php
/**
<// ljLog \\>
@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 {
?>
<!DOCTYPE html
PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xml:lang="en" lang="en">
<head>
<title>ljLog - ALPHA-000-UNSTABLE-RC-FLAMING-AARDVARK</title>
<style type="text/css">
<!--
body {
background-color:#eee;
margin:16px;
padding:4px;
font-family:Sans-serif;
}

#table {
background-color: #ccc;
padding:2px;
}

.table_row {
background-color:#fff;
border:1px solid #000;
margin-top:4px;
margin-bottom:4px;
padding:2px;
}

.header_cell {
font-weight:800;
font-size:14px;
margin-left:4px;
margin-right:4px;
padding:2px;
text-align:center;
}

.table_row td {
font-size:12px;
padding:2px;
padding-left:4px;
padding-right:4px;
border:1px solid #000;
text-align:center;
}

-->
</style>
</head>
<body>
<table id="table">
<tr>
<td class="header_cell">IP</td>
<td class="header_cell">Post ID</td>
<td class="header_cell">Timestamp</td>
<td class="header_cell">Browser</td>
<td class="header_cell">Platform</td>
</tr>
<?php
$res = $db->query("SELECT * FROM ljview ORDER BY ljview_id DESC");
while( $view = $res->fetch_object() ) {
print ("<tr class=\"table_row\">");

$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 ( "<td>" . long2ip($ip->ljview_ip_ip) . "</td>" );
} else {
print("<td>&nbsp;</td>");
}

$post_id = $view->ljview_post_id;
print("<td><a href=\"http://livejournal.com/users/g0thm0g/$post_id.html\" target=\"_blank\">$post_id</a></td>");

if( $view->ljview_ts ) {
print ( "<td>" . date("m/d/Y, g:i:s a", $view->ljview_ts) . "</td>" );
} else {
print("<td>&nbsp;</td>");
}

$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 ( "<td>" . $browser->parent . "</td>" );
print ( "<td>" . $browser->platform . "</td>" );
} else {
print("<td>&nbsp;</td>");
}

print ("</tr>");
}
?>
</table>
<p>
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
<img style="border:0;width:88px;height:31px" src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" />
</p>
</body>
</html>

<?php
}
$db->close();
?>
</pre>
</lj-cut>

<lj-cut text="ljview.php - Database Schema">
<pre style='font-size:11px;">
-- phpMyAdmin SQL Dump
-- version 2.6.0-pl2
-- http://www.phpmyadmin.net
--
-- Host: localhost
-- Generation Time: Feb 08, 2005 at 08:52 PM
-- Server version: 4.1.8
-- PHP Version: 5.0.3
--
-- Database: `ljlog`
--

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

--
-- Table structure for table `ljview`
--

DROP TABLE IF EXISTS `ljview`;
CREATE TABLE IF NOT EXISTS `ljview` (
`ljview_id` int(10) unsigned NOT NULL auto_increment,
`ljview_ip_id` int(10) unsigned NOT NULL default '0',
`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;
</pre>
</lj-cut>

LJ Video