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

  • Music:

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_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;" />
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>
Subscribe
  • Post a new comment

    Error

    default userpic

    Your IP address will be recorded 

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