getting browser information and using it in a mysql db
Posted: Tue Oct 26, 2010 5:54 pm
Here is a code snippet of how you can use external requests from http://www.netip.de query data you can then use a regular expression to sort out the info.
you can lean reg exp and how to use php to send an ajax response all in one
you will need php 5.x, apache 2.2.x, and mysql 5.1.x running. sometimes it is nice to unzip phpmyadmin into a folder in your htdocs as htdocs\phpmyadmin\ so you can access it locally on your server easily for mysql testing etc
http://www.supercala.net/tuts/php/sitet ... 6-2010.zip
_info.php, hold your variables to login to your mysql server and such
geocheck_ip.php the file that gives back an html response you can use to show formatted info from
unique_ip.php the interface to show the unique information from, not repeated, and where they came from (ajax to geocheck_ip.php)
init_db.php initialization of your database, user, table, etc and a view of it all
_info.php
here is the php file geocheck_ip.php i wrote
and then the calling portion in javascript with ajax
init_db.php
you can lean reg exp and how to use php to send an ajax response all in one
you will need php 5.x, apache 2.2.x, and mysql 5.1.x running. sometimes it is nice to unzip phpmyadmin into a folder in your htdocs as htdocs\phpmyadmin\ so you can access it locally on your server easily for mysql testing etc
http://www.supercala.net/tuts/php/sitet ... 6-2010.zip
_info.php, hold your variables to login to your mysql server and such
geocheck_ip.php the file that gives back an html response you can use to show formatted info from
unique_ip.php the interface to show the unique information from, not repeated, and where they came from (ajax to geocheck_ip.php)
init_db.php initialization of your database, user, table, etc and a view of it all
_info.php
Code: Select all
<?php
//when you include or require a file that has code, it has to have the php code beg and end parts or it will basically echo as text
$companyTitle="xxx inc.";
$company="xxx";
//-----localhost only
$server="localhost";
$admin="root";
$adminPass="Admin123";
$user="Joe";
$pass="Food!234";//once a local user is made, dont change this for testing, or just delete the user, this will panic all other sites in your server that have this user
//-----localhost only
$db=$company;
$email="joe@fudge.net";
?>Code: Select all
<?php
/*
geocheck_ip.php
http://css-tricks.com/snippets/php/get-geo-ip-information/
07-09-2010 modified by kristoffe brodeur.
*/
if(isset($_GET['ip']))
{$ip=$_GET['ip'];}
else
{$ip='72.94.171.34';}
$arrStr=geoCheckIP($ip);
//echo $arrStr;
$dataArr=explode(",",$arrStr);
$len=count($dataArr);
$table="
<table class='info'>
<tr><td width='150px'>Node</td><td>Data</td></tr>
";
//
for($a=0;$a<$len;$a++)
{
$subArr=explode("^",$dataArr[$a]);
$table.="
<tr><td>".$subArr[0]."</td><td>".$subArr[1]."</td></tr>";
}
$table.="</table>";
echo $table;
//Array ( [domain] => dslb-094-219-040-096.pools.arcor-ip.net [country] => DE - Germany [state] => Hessen [town] => Erzhausen )
//Get an array with geoip-infodata
function geoCheckIP($ip)
{
//check, if the provided ip is valid
if(!filter_var($ip, FILTER_VALIDATE_IP))
{
throw new InvalidArgumentException("IP is not valid");
}
//contact ip-server
$response=file_get_contents('http://www.netip.de/search?query='.$ip);
//$response=curl_get_contents('http://www.netip.de/search?query='.$ip);
if (empty($response))
{
throw new InvalidArgumentException("Error contacting Geo-IP-Server");
}
//Array containing all regex-patterns necessary to extract ip-geoinfo from page
$patterns=array();
$patterns["domain"] = '#Domain: (.*?) #i';
$patterns["country"] = '#Country: (.*?) #i';
$patterns["state"] = '#State/Region: (.*?)<br#i';
$patterns["town"] = '#City: (.*?)<br#i';
//Array where results will be stored
$ipInfo=array();
$dataStr="";
//check response from ipserver for above patterns
foreach ($patterns as $key => $pattern)
{
//store the result in array
$ipInfo[$key] = preg_match($pattern,$response,$value) && !empty($value[1]) ? $value[1] : 'not found';
$dataStr.=$key."^".$ipInfo[$key].",";
}
//return $ipInfo;
// *^**,*^**,... (key)[^](value)[,]...
$dataArr=explode(",",$dataStr);
$finishedStr="";
$len=count($dataArr);
//
for($a=0;$a<$len-2;$a++)
{
$finishedStr.=$dataArr[$a].",";
}
$finishedStr.=$dataArr[$a];
return $finishedStr;
}
?>Code: Select all
<?php
/*
unique_ip.php v1.0 by Kristoffe Brodeur. ©2010 All Rights Reserved.
07-09-2010
*/
$to_root="../../../";
require '../_info.php';
//data scouring
$tableName="sitetrack";
$connect=mysql_connect($server,$user,$pass)or die(mysql_error());
$selDB=mysql_select_db($db,$connect)or die("Could not select database $db: <span class='error'>".mysql_error()."</span>");
//
$unique=mysql_query("SELECT DISTINCT ip FROM $tableName")or die(mysql_error());
$uLen=mysql_num_rows($unique);
$uStr="
<table class='ip'>
<tr><td>IP Address</td><td>Page Views</td></tr>
";
//
for($u=0;$u<$uLen;$u++)
{
$row=mysql_fetch_assoc($unique);
//
$queryC="SELECT SUM(count) AS total_visits FROM $tableName WHERE ip='".$row['ip']."'";
$linkC=mysql_query($queryC)or die(mysql_error());
$rowC=mysql_fetch_assoc($linkC);
$total=$rowC['total_visits'];
//
$visits="-";
$uStr.="
<tr><td><a href='#' onclick=load_info('".$row['ip']."')>".$row['ip']."</a></td><td>".$total."</td></tr>
";
}
$uStr.="</table>";
mysql_close($connect);
?>
<html>
<head>
<link type="text/css" rel="stylesheet" href="<?php echo $to_root;?>css/unique_ip.css" />
</head>
<body>
<div class="unique">
<h3>Unique IP Address List</h3>
<?php echo $uStr;?>
</div>
<div class="info" id="info">
<h3>ip info here (ajax)</h3>
</div>
</body>
</html>
<SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript">
//-----
function XML_httpObj()
{
//ie7+,ff,etc...
if(window.XMLHttpRequest)
{return new XMLHttpRequest();}
//ie6,5
else
{return new ActiveXObject("Microsoft.XMLHTTP");}
}
//-----
function load_info(sIP)
{
var xmlhttp=XML_httpObj();
//
if(xmlhttp==null)
{alert("Your browser is old and does not support HTTP Request! Update it!");}
var url="geocheck_ip.php?ip="+sIP;
xmlhttp.onreadystatechange=function()
{
if(this.readyState==4)
{
info.innerHTML=this.responseText;
}
}
xmlhttp.open("GET",url,true);
xmlhttp.send(null);
}
</SCRIPT>init_db.php
Code: Select all
<?php
//site tracking software v1.0 by Kristoffe Brodeur. ©2010 All Right Reserved.
$to_root="../../../";
require "../_info.php";
require $to_root.'php/mysql_functions.php';
$connect=mysql_connect($server,$user,$pass)or die(mysql_error());
require $to_root.'php/menu.php';
//draw menu
$menuItems=readMenuArray($to_root."menus/sitetrack.txt");
$len=count($menuItems);//defined in menu.php
$menu='<div class="menu">|';
//looping through the text file with ^ are the delimiter for folder^file^title^*(submenu-not used)
for($a=0;$a<$len-1;$a++)
{
$parts=explode("^",$menuItems[$a]);//can't use ^ with split, have to use explode, unless you use \^ to escape the special character
$addy=$parts[0].$parts[1];
$menu.=' <a href="'.$to_root.$addy.'" />'.$parts[2].'</a> |';
}
$menu.="</div>";
$menu.="<h4>Init DB<span class='ok'>$companyTitle</span></h4>
<h4>Server <span class='ok'>$server</span></h4>";
//-----
?>
<html>
<head>
<link type="text/css" rel="stylesheet" href="<?php echo $to_root;?>css/init_db.css" />
</head>
<body>
<div class="wrap">
<?php echo $menu;?>
<?php
//create user 'test'
if($server=="localhost")
{
echo '<h5>Create new database user <span class="ok">'.$user.'</span></h5><hr />';
$connect=mysql_pconnect($server,$admin,$adminPass)or die('Error, login db failed <span class="error">'.mysql_error().'</span>');
echo 'login successful!<br />';
//create new user
$newuser="CREATE USER '$user'@'localhost' IDENTIFIED BY '$pass'";
//
if(mysql_query($newuser))
{
echo "User [$user] pass [$pass] created!<br />";
}
else
{
echo "I'm sorry but there was an error. Does the user already exist? <span class='error'>".mysql_error()."</span><br />";
}
$userRights="GRANT ALL PRIVILEGES ON *.* TO '$user'@'$server' WITH GRANT OPTION";
//
if(mysql_query($userRights))
{
echo "User <span class='ok'>$user</span> privileges *.* granted!<br />";
}
else
{
echo "I'm sorry but there was an error.<span class='error'>".mysql_error()."</span>";
}
}
//create main database
$link=mysql_connect($server,$user,$pass);
//
if(!$link)
{
die('Cannot connect to database, sorry:<span class="error">'.mysql_error().'</span>');
}
else
{
echo'<h5>Create new database <span class="ok">'.$db.'</span></h5><hr />';
//
if(mysql_query("CREATE DATABASE $db"))
{
echo "Database $db created!<br />";
}
else
{
echo "<div >Error creating database </b><span class='error'>".mysql_error()."</span><br />";
}
}
//-----tables
$create_sitetrack="
CREATE TABLE sitetrack
(
id INT(2) ZEROFILL NOT NULL AUTO_INCREMENT,
ip CHAR(16) NOT NULL,
referer CHAR(255) NOT NULL,
count INT(3) ZEROFILL NOT NULL,
page CHAR(255) NOT NULL,
PRIMARY KEY (id)
)";
//create tables
$tables=array($create_sitetrack);
$tableName=array("sitetrack");
$len=count($tables);
mysql_select_db($db,$link) or die('Unable to select database!');
//
for($a=0;$a<$len;$a++)
{
echo"<img src='".$to_root."iconz/table.jpg' />";
//
if(table_exists($tableName[$a]))
{
echo "table <span class='ok'>".$tableName[$a]."</span> ok<br />";
}
else
{
echo "creating table <span class='ok'>".$tableName[$a]."</span> | ";
mysql_query($tables[$a])or die("<span class='error'>".mysql_error()."</span>");
echo "sucessful creation.<br />";
}
}
mysql_close($connect);
?>
<hr />
<h3><span class='error'><img src="<?php echo $to_root;?>iconz/del.png" /> <a onclick="confirm_delete()" href="#">[DELETE ALL TABLES]</a></span></h3>
<h3><span class='ok'><img src="<?php echo $to_root;?>iconz/magnify_32px.jpg" /> <a href="<?php echo $to_root;?>mS/admin/show_tables.php">[SHOW ALL TABLES]</a></span></h3>
<h3><span class='ok'><img src="<?php echo $to_root;?>iconz/key_32px.png" /> <a href="<?php echo $to_root;?>mS/admin/alter_table.php?id=0">[ALTER FIELDS PER TABLE]</a></span></h3>
</div>
</body>
</html>
<SCRIPT LANGUAGE=JavaScript" type="text/javascript">
//-----
function confirm_delete()
{
var answerDel=confirm("Are you sure you want to delete the ENTIRE database?")
{
if(answerDel)
{
location.href="delete_tables.php?returnPage=init_db.php";
//alert("Let me know, and I'll delete it for you by modifying this button. I still won't do it, sorry!");
}
else
{
alert("Ok, I just wanted to be sure. I'm glad you didn't do it!");
}
}
}
</SCRIPT>