Aurorapod - Tutorial on Simple Hit Counter Script Part 2


	\| Home \| Hotscript \| Downloads

Tutorial - PHP

Categories
	Blog	[2]
	Communication	[1]
	Site Stats	[3]
	Skin/Template	[2]
	Timer & Counter	[1]
	Tools	[3]

Tutorial - Photoshop

Categories
	Graphics	[5]
	Special Effects	[2]
	Text Effect	[2]

Download - PHP Scripts

	myBloggie - D/L Advanced Blog System
	myBloggie's Home
	myBloggie's Demo user: guest pwd: pass
	myEvent Dynamic Calendar with Events
	Bloggielite New Generation Blog

TNX?
The best prices in karaoke equipment we ship worldwide.
Online Phone Cards & Cheap Phone Cards & calling cards

PHP/MySQL Tutorial

Simple Hit Counter Script Part 2

Written by : Sean

Date : 2003-08-04

[Back] [Home]

Recapture what have been discussed in part 1
a) We have created 2 table to get ourselves prepare for the hit counter
b) We have wrote a few codes to capture the visitor's information
c) We get ourselves connected to database

Now let me go thru important part again - Managing the hit counter database

First, we need to check if the visitor ( thru IP Address ) has visited the page within the unique time specified, below is the SQL used for this.

Code:

$sql= "SELECT COUNT(*)as row FROM " . LOG_TBL . " WHERE (ip LIKE \"%" . $u_ip . "%\") AND (timestamp + " . (UNIQUE_TIME * 60) . " > " . $u_timestamp . ")";
$query = mysql_query($sql) or die("Line 31 Cannot query the database. " . mysql_error());
$numrows = mysql_fetch_array($query);

If the IP address not found within that specified unique time, that's when numbers of row return thru SQL query is ZERO. Hit of the visitor will be recorded.

Code:

If ($numrows['row'] <= 0)

In order to record the hit, we need to check what is the current hit thru the SQL Query below

Code:

{
$sql = "SELECT hits FROM " . HIT_COUNTER_TBL . " WHERE (id=1) LIMIT 1";
$query = mysql_query($sql) or die("Cannot query the database. " . mysql_error());
$row = mysql_fetch_array($query);
$hits = $row["hits"];

Once we capture the hit counter from $hits = $row["hits"], we have generate another SQL statement to update the hit counter by adding 1 to the existing counter.

Code:

$sql = "UPDATE " . HIT_COUNTER_TBL . " SET hits=(hits+1) WHERE id=1" ;
mysql_query($sql) or die("Cannot query the database. " . mysql_error());

At the same time , you may want to capture your visitor's information into the log table using the following statement
ie :
a) visitor's ip
b) referer site
c) visited time

These information are important for us to capture user online status later on.

Code:

$sql = "INSERT INTO " . LOG_TBL . " (ip,host,referrer,timestamp) VALUES (\"$u_ip\",\"$u_host\",\"$u_referrer\",\"$u_timestamp\")";
mysql_query($sql) or die(" Cannot query the database. " . mysql_error());
}

OK! We are done with managing the information of our visitors. Now we need to come out with a script to display the hit information
We are going to extract 2 information from the table
a) total hit
b) no of user online ( 5 min )

following are the SQL Query to gather those information

Code:

// SQL for User Online
$sql="SELECT COUNT(*) FROM " . LOG_TBL . " WHERE (timestamp + " . (USER_ONLINE_TIME * 60) . " >= " . time() . ")";
$query= mysql_query($sql) or die("Line 68 Cannot query the database. " . mysql_error());

//Assign results to $useronline
$user_online = @mysql_result($query, 0);

// SQL for User Online
$sql = "SELECT hits FROM " . HIT_COUNTER_TBL . " WHERE (id=1) LIMIT 1";
$query = mysql_query($sql) or die("Line 35 Cannot query the database. " . mysql_error());
$row = mysql_fetch_array($query);

//Assign results to $hit
$hits = $row["hits"];
?>

Now finish it up with some HTML formating & parse results.

Code:

<table width='100%' cellpadding='2' cellspacing='0'><tr>
<td colspan='2'>
<center> Site Statistics :</center></td></tr>
<td colspan='2' height=12></td></tr>
<tr><td>
Visits </td>
<td>
<? echo $hits; ?>

</td></tr>
<tr><td>
User Online</td>
<td>
<? echo $user_online ; ?>

</td></tr>
</table>

Please feedback in the comment section if you need any help or you can get the script working... or even question

You can improve this script to analysis your site traffic using information in the log table.

Full Script is as below

if you save this as hit.php, you need to include this script into the pages you want to track

eg:

include "hit.php";

Code:

<?
define("LOG_TBL", "log");
define("HIT_COUNTER_TBL", "hit_counter");
define("BLOCK_IP", "0.0.0.0");
define("UNIQUE_TIME", "20");
define("USER_ONLINE_TIME", "5");
define("NO_OF_DIGITS", "5");

$u_timestamp = time();

if (@getenv("HTTP_X_FORWARDED_FOR"))
{ $u_ip = @getenv("HTTP_X_FORWARDED_FOR"); }
else { $u_ip = @getenv("REMOTE_ADDR"); }

if ($u_ip == BLOCK_IP)
{ return 1;
exit; }

$u_host = @gethostbyaddr($u_ip);
$u_referrer = @getenv("HTTP_REFERER");

$db = mysql_connect($host,$login,$password);
mysql_select_db($base,$db);

$sql= "SELECT COUNT(*)as row FROM " . LOG_TBL . " WHERE (ip LIKE \"%" . $u_ip . "%\") AND (timestamp + " . (UNIQUE_TIME * 60) . " > " . $u_timestamp . ")";
$query = mysql_query($sql) or die("Line 31 Cannot query the database. " . mysql_error());
$numrows = mysql_fetch_array($query);

If ($numrows['row'] <= 0)
{
//get current hit
$sql = "SELECT hits FROM " . HIT_COUNTER_TBL . " WHERE (id=1) LIMIT 1";
$query = mysql_query($sql) or die("Line 35 Cannot query the database. " . mysql_error());
$row = mysql_fetch_array($query);
$hits = $row["hits"];

$sql = "UPDATE " . HIT_COUNTER_TBL . " SET hits=(hits+1) WHERE id=1" ;
mysql_query($sql) or die("Cannot query the database. " . mysql_error());

$sql = "INSERT INTO " . LOG_TBL . " (ip,host,referrer,timestamp) VALUES (\"$u_ip\",\"$u_host\",\"$u_referrer\",\"$u_timestamp\")";
mysql_query($sql) or die(" Line 52 Cannot query the database. " . mysql_error());
}

$sql="SELECT COUNT(*) FROM " . LOG_TBL . " WHERE (timestamp + " . (USER_ONLINE_TIME * 60) . " >= " . time() . ")";
$query= mysql_query($sql) or die("Line 68 Cannot query the database. " . mysql_error());
$user_online = @mysql_result($query, 0);

$sql = "SELECT hits FROM " . HIT_COUNTER_TBL . " WHERE (id=1) LIMIT 1";
$query = mysql_query($sql) or die("Line 35 Cannot query the database. " . mysql_error());
$row = mysql_fetch_array($query);
$hits = $row["hits"];

?>

Paste this codes into the pages you want to display the hits information

Code: