Site traffic statistic counter
That article tells you how you can learn the traffic statistic with the help of PHP and MySQL. The main feature is that you will learn statistic about every user, you’ll learn what pages he visited, time he spent on your site etc.
At first we have to create the table in the database (MySQL), let’s name it “counter”. It will look as follows:
CREATE TABLE `counter` (
`user_id` varchar(64) NOT NULL default "",
`url` text NOT NULL,
`my_url` text NOT NULL,
`pages` text NOT NULL,
`date` datetime default NULL,
`IP_PORT` varchar(30) NOT NULL default "",
`brouser` varchar(100) NOT NULL default "",
KEY `user_id` (`user_id`)
) TYPE=MyISAM;
After that we have to write our script. At first we have to “register” the user, that’s why we’ll use sessions:
session_register("user"); // open session "user"
if (($_SESSION["user"]=="") or ($_SESSION["user"]==0)) // Check whether user registered or not
{
$_SESSION["user"]=rand(1,999999999); // Give user the ID
}
Now user is registered and we need to get information about him and pages he visited. PHP has enough opportunities for this:
function getip()
{
if(isset($HTTP_SERVER_VARS)) {
if(isset($HTTP_SERVER_VARS["HTTP_X_FORWARDED_FOR"])) {
$realip = $HTTP_SERVER_VARS["HTTP_X_FORWARDED_FOR"];
}elseif(isset($HTTP_SERVER_VARS["HTTP_CLIENT_IP"])) {
$realip = $HTTP_SERVER_VARS["HTTP_CLIENT_IP"];
}else{
$realip = $HTTP_SERVER_VARS["REMOTE_ADDR"];
}
}else{
if(getenv( "HTTP_X_FORWARDED_FOR" ) ) {
$realip = getenv( "HTTP_X_FORWARDED_FOR" );
}elseif ( getenv( "HTTP_CLIENT_IP" ) ) {
$realip = getenv( "HTTP_CLIENT_IP" );
}else {
$realip = getenv( "REMOTE_ADDR" );
}
}
if((getenv("REMOTE_PORT"))) {
$realip.=":".getenv("REMOTE_PORT");
}
return $realip;
}
$brow = getenv("HTTP_USER_AGENT"); // browser name
$ref = getenv("HTTP_REFERER"); // where user came from
$my_urls = getenv("QUERY_STRING"); // where he is now
$ip=getip(); // Getting ip
We have received the information about the user and now we have to add it to the table, but at first we should connect to the database and select our table:
$SQL=array(); // Create the array with main MySQL parameters and fill it
$SQL["host"]="localhost";
$SQL["user"]="user";
$SQL["db"]="db_name";
$SQL["pass"]="password";
$SQL["counter"]="counter";
$link = mysql_connect($SQL["host"], $SQL["user"], $SQL["pass"]) // Connecting to MySQL
or die ("Could not connect to MySQL");
mysql_select_db ($SQL["db"]) // Database select
or die ("Could not select database");
$result = mysql_query("SELECT * FROM `".$SQL["counter"]."`", $link);
$num_rows = mysql_num_rows($result); // get the amount of the strings in the table
We have connected to MySQL. Now let’s create several new functions for filing the table:
function searchID($id) // Searches the user’s id in the table,
and according to the result defines if the field for that user has been already created
{
global $SQL;
$result = mysql_query ("SELECT * FROM `".$SQL["counter"]."` WHERE `user_id` LIKE "".$id.""")
or die ("Query failed");
$num_rows = mysql_num_rows($result);
mysql_free_result($result);
if ($num_rows>0) return True;
else return False;
}
function Update($id,$pages) // If user visited that site we just refresh the information about him
{
global $SQL;
$c=count(explode("rn",$pages));
if ($c<150){
$result = mysql_query ("UPDATE `".$SQL["counter"]."` SET `pages` = "".$pages."" WHERE
`user_id` = "".$id.""")
or die ("Query failed");
return $result;
}
}
function IncCounter()
{
$brow = getenv("HTTP_USER_AGENT"); browser name
$ref = getenv("HTTP_REFERER"); // // where user came from
$my_urls = getenv("QUERY_STRING"); // where he is now
$ip=getip(); // Getting ip
global $SQL;
if (eregi("module=users",$my_urls)==false)
{
if (searchID($_SESSION["user"])==false)
{
$result = mysql_query ("INSERT INTO `counter` ( `user_id` , `url` , `my_url` , `pages` , `date` ,
`IP_PORT` , `brouser` )
VALUES ("".$_SESSION["user"]."" , "".$ref."" , "".$my_urls."", "".trim($my_urls)."" , NOW( ) ,
"".$ip."" , "".$brow."");");
}
else
{
$result = mysql_query ("SELECT * FROM `".$SQL["counter"]."` WHERE `user_id` LIKE "".$_SESSION["user"].
""") or die ("Query failed");
list ($user_id, $url, $my_url, $pages , $date, $IP_PORT, $brouser) = mysql_fetch_row
($result);
Update($user_id,trim($pages."rn".$my_urls));
}
@mysql_free_result($result);
}
}
After that we have to add several lines:
IncCounter(); // Fill the table
@mysql_close($link); Close the connection with MySQL
Here is the source code:
counter.php
<?php
$SQL=array();
$SQL["host"]="localhost";
$SQL["user"]="user";
$SQL["db"]="db_name";
$SQL["pass"]="password";
$SQL["counter"]="counter";
session_register("user"); // open session "user"
if (($_SESSION["user"]=="") or ($_SESSION["user"]==0))
{
$_SESSION["user"]=rand(1,999999999);
}
$link = mysql_connect($SQL["host"], $SQL["user"], $SQL["pass"])
or die ("Could not connect to MySQL");
mysql_select_db ($SQL["db"])
or die ("Could not select database");
$result = mysql_query("SELECT * FROM `".$SQL["counter"]."`", $link);
$num_rows = mysql_num_rows($result);
function searchID($id)
{
global $SQL;
$result = mysql_query ("SELECT * FROM `".$SQL["counter"]."` WHERE `user_id` LIKE "".$id.""")
or die ("Query failed");
$num_rows = mysql_num_rows($result);
mysql_free_result($result);
if ($num_rows>0) return True;
else return False;
}
function Update($id,$pages)
{
global $SQL;
$c=count(explode("rn",$pages));
if ($c<150){
$result = mysql_query ("UPDATE `".$SQL["counter"]."` SET `pages` = "".$pages.""
WHERE `user_id` = "".$id.""")
or die ("Query failed");
return $result;
}
}
function IncCounter()
{
$brow = brow_type(true);
$ref = getenv("HTTP_REFERER");
$my_urls = getenv("QUERY_STRING");
$ip=getip();
global $SQL;
if (eregi("module=users",$my_urls)==false)
{
if (searchID($_SESSION["user"])==false)
{
$result = mysql_query ("INSERT INTO `counter` ( `user_id` , `url` , `my_url` , `pages` ,
`date` , `IP_PORT` , `brouser` )
VALUES ("".$_SESSION["user"]."" , "".$ref."" , "".$my_urls."", "".trim($my_urls)."" , NOW( ) ,
"".$ip."" , "".$brow."");");
}
else
{
$result = mysql_query ("SELECT * FROM `".$SQL["counter"]."` WHERE `user_id` LIKE "".$_SESSION["user"]
.""") or die ("Query failed");
list ($user_id, $url, $my_url, $pages , $date, $IP_PORT, $brouser) = mysql_fetch_row
($result);
Update($user_id,trim($pages."rn".$my_urls));
}
@mysql_free_result($result);
}
}
IncCounter();
@mysql_close($link);
?>
For statistic output you can use one more script:
vizit_info.php
<?php
$link = mysql_connect($SQL["host"], $SQL["user"], $SQL["pass"])
or die ("Could not connect to MySQL");
mysql_select_db ($SQL["db"])
or die ("Could not select database");
$result = mysql_query("SELECT * FROM `".$SQL["counter"]."`", $link);
$num_rows = mysql_num_rows($result);
$result = mysql_query ("SELECT * FROM ".$SQL["counter"]." ORDER BY `date` DESC LIMIT 0 ,
".$num_rows." ")
or die ("Query failed");
//Øàáëîí
$shabl=Remote_file_read(“shabl.htm”); // template
while (list ($user_id, $url, $my_url, $pages , $date, $IP_PORT, $brouser) = mysql_fetch_row
($result))
{
$c=count(explode("rn",$pages));
if ($c>15)
{
$pages_s=substr($pages, 0, 256)."<hr><br>....<br><br><hr>";
$pages_e=substr($pages, -100, -1);
$pages=$pages_s.$pages_e;
}
$pages=str_replace("rn","<hr>",$pages);
$tmp=str_replace("%user_id%",$user_id,$shabl);
$tmp=str_replace("%user_ip%",$IP_PORT,$tmp);
$tmp=str_replace("%from%",$url,$tmp);
$tmp=str_replace("%my_pages%",$my_url,$tmp);
$tmp=str_replace("%vizit_pages%",$pages,$tmp);
$tmp=str_replace("%data%",$date,$tmp);
$tmp=str_replace("%brouser%",$brouser,$tmp);
$tmp=str_replace("%col_vo%",$c,$tmp);
echo $tmp;
}
mysql_close($link);
?>
shabl.htm
<table style="border-collapse:collapse; font-family:
verdana,tahoma,arial; font-size: 9pt; color: #404479;" align=center cellspacing=0 width=95%>
<tr>
<td width="99%" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#6D6D8F" colspan="2">
<p><font color="#F4F4FC" face="Arial"><i><b>User ID: </b></i>%user_id%
(<b>%user_ip%</b>)</font></p> </td>
</tr>
<tr>
<td width="50%" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#F4F4FF" valign=top>
<p align="center"><i><b>Wherefrom</b></i></p>
</td>
<td width="50%" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#F4F4FF" valign=top>
<p align="center"><i><b>Where</b></i></p>
</td>
</tr>
<tr>
<td width="50%" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#F7F7FF" valign=top>
<p>%from%</p>
</td>
<td width="50%" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#F7F7FF" valign=top>
<p>%my_pages%</p>
</td>
</tr>
<tr>
<td width="99%" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#E6E6FC" colspan="2">
<p align="center"><b><i>Visit</i></b></p>
</td>
</tr>
<tr>
<td width="99%" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#F4F4FF" colspan="2" valign="top">
<p>%vizit_pages%</p>
</td>
</tr>
<tr>
<td width="99%" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#E6E6FC" colspan="2" valign="top">
<p align="center"><b><i>Browser</i></b></p>
</td>
</tr>
<tr>
<td width="99%" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#F4F4FF" colspan="2" valign="top">
<p>%brouser%</p>
</td>
</tr>
<tr>
<td width="356" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#E6E6FC">
<p align=right>Amount of the pages:<b>%col_vo%</b></p> </td>
<td width="358" style="border-width:1; border-color:b_color; border-style:solid;" bgcolor="#E6E6FC">
<p align=right><font face="Arial" color="#339900"><span style="font-size:8pt;"><i>Date:
</i></span></font><font face="Arial" color="#336600"><span style="font-size:8pt;"><i>%data%</i></span></font></p> </td>
</tr>
</table>
<table align=center cellpadding=0 cellspacing=0>
<tr>
<td width=100 height=15>
</td>
</tr>
</table>
