Michael Bommarito (g0thm0g) wrote in mysql,
Michael Bommarito
g0thm0g
mysql

  • Music:

PHP + MySQL = ljLog


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




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


	
	
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
"); } ?> 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) . " $post_id" . date("m/d/Y, g:i:s a", $view->ljview_ts) . " " . $browser->parent . "" . $browser->platform . " $lj_user 

Valid XHTML 1.0! Valid CSS!

close(); ?>




[Error: Irreparable invalid markup ('<pre [...] `ljview_user_agent_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;" />
I've written little PHP/MySQL script to parse HTTP requests from hidden &lt;img&gt; tags in LJ posts (e.g. &lt;img src="http://ohsonline.no-ip.com/ljview.php?post-id=74765" style="display:none;" /&gt;), 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 :)

<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 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 {
?>
<!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;
}

.header {
font-size:12px;
font-weight:800;
margin-left:16px;
margin-bottom:16px;
text-decoration:underline;
}

.header span {
margin-left:8px;
margin-right:8px;
}

-->
</style>
</head>
<body>
<?php
$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("<div class=\"header\">Viewing $offset - $end out of $count</div>");
print("<div class=\"header\">");
if( $offset > 0 ) {
print("<span><a href=\"ljview.php?offset=$back&limit=$limit\">Previous $limit</a></span>");
}
if( $offset < $count ) {
print("<span><a href=\"ljview.php?offset=$end&limit=$limit\">Next $limit</a></span></div>");
}
?>
<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>
<td class="header_cell">LJ User</td>
</tr>
<?php
while( $view = $res->fetch_object() ) {
print ("<tr class=\"table_row\">");

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

$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("<td><a href=\"http://livejournal.com/users/$lj_user\" target=\"_blank\">$lj_user</a></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>
Subscribe
  • Post a new comment

    Error

    default userpic
    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 3 comments