Track visitors PHP Tutorial

0
46
Track visitors PHP Tutorial

Tracking our website’s visitors is a very important step if you’re serious enough about analyzing your traffic and optimizing your pages to get the most of your visitors. There are many reasons why you should think of implementic tracking scripts (it’s crucial to know where your traffic is coming from and where it goes, what people search, how long they stay etc.), you could increase sales, you could optimize your pages to increase page hits, you could make lots of changes to increase your Adsense profits and the list goes on and on. You name it. In this tutorial we will create a MySql based tracking script written in PHP which will do a simple task – store each visitor that browsed our page with IP, hours, dates, browser types, referring url and visited page. We will start by creating a database that will be used by our php script to store the required data. Let’s call it “visitor_tracker”. Now that our database is created all we need at this moment is a table that will store our data. We will call it “visitors_table”. Our table will have 9 rows as following:

CREATE TABLE `visitors_table` (
`ID` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`visitor_ip` VARCHAR( 32 ) NULL ,
`visitor_browser` VARCHAR( 255 ) NULL ,
`visitor_hour` SMALLINT( 2 ) NOT NULL DEFAULT '00',
`visitor_minute` SMALLINT( 2 ) NOT NULL DEFAULT '00',
`visitor_date` TIMESTAMP( 32 ) NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`visitor_day` SMALLINT( 2 ) NOT NULL ,
`visitor_month` SMALLINT( 2 ) NOT NULL ,
`visitor_year` SMALLINT( 4 ) NOT NULL ,
`visitor_refferer` VARCHAR( 255 ) NULL ,
`visitor_page` VARCHAR( 255 ) NULL
) TYPE = MYISAM ;

we’ve set some values to “NOT NULL” so that our server can continue it’s job and still store a record if some values are missing (the most comon reason for a detail to be missed is a bad detection script used). Ok. We have our database ready for storing our visitors info so what’s next? We will need to setup a script which will store the visitor’s info in our database so let’s start writing it. We need the ip address of our visitor so we will get it using the following method.

$visitor_ip = GetHostByName($REMOTE_ADDR);

Next we need the browser type of our visitor and we will use this function to get it:

function getBrowserType () {
if (!empty($_SERVER['HTTP_USER_AGENT'])) 
{ 
   $HTTP_USER_AGENT = $_SERVER['HTTP_USER_AGENT']; 
} 
else if (!empty($HTTP_SERVER_VARS['HTTP_USER_AGENT'])) 
{ 
   $HTTP_USER_AGENT = $HTTP_SERVER_VARS['HTTP_USER_AGENT']; 
} 
else if (!isset($HTTP_USER_AGENT)) 
{ 
   $HTTP_USER_AGENT = ''; 
} 
if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[2]; 
   $browser_agent = 'opera'; 
} 
else if (ereg('MSIE ([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[1]; 
   $browser_agent = 'ie'; 
} 
else if (ereg('OmniWeb/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[1]; 
   $browser_agent = 'omniweb'; 
} 
else if (ereg('Netscape([0-9]{1})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[1]; 
   $browser_agent = 'netscape'; 
} 
else if (ereg('Mozilla/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[1]; 
   $browser_agent = 'mozilla'; 
} 
else if (ereg('Konqueror/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[1]; 
   $browser_agent = 'konqueror'; 
} 
else 
{ 
   $browser_version = 0; 
   $browser_agent = 'other'; 
}
return $browser_agent;
}

so there it is:

$visitor_browser = getBrowserType();

Now lets get the hour, minute, day, month and year of this visit:

$visitor_hour = date("h");
$visitor_minute = date("i");
$visitor_day = date("d");
$visitor_month = date("m");
$visitor_year = date("y");

next we need to find out who is sending us visitors so we can thank them.

$visitor_refferer = gethostbyname($HTTP_REFERER);

…to get the full url of our page we will use this function:

function selfURL() { 
$s = empty($_SERVER["HTTPS"]) ? '' : ($_SERVER["HTTPS"] == "on") ? "s" : "";
$protocol = strleft(strtolower($_SERVER["SERVER_PROTOCOL"]), "/").$s; 
$port = ($_SERVER["SERVER_PORT"] == "80") ? "" : (":".$_SERVER["SERVER_PORT"]); 
return $protocol."://".$_SERVER['SERVER_NAME'].$port.$_SERVER['REQUEST_URI']; 
}
function strleft($s1, $s2) { return substr($s1, 0, strpos($s1, $s2)); 
}

Ok,  so we have our page, we will store it on a variable:

$visited_page = selfURL();

We will create a new page which will be used to connect to the database and to store our functions. Let’s name it visitors_connections.php. we will paste this code and save it:

$hostname_visitors = "host";
$database_visitors = "database";
$username_visitors = "username";
$password_visitors = "password";

$visitors = mysql_connect($hostname_visitors, $username_visitors,
 $password_visitors) or rigger_error(mysql_error(),E_USER_ERROR);


function getBrowserType () {
if (!empty($_SERVER['HTTP_USER_AGENT'])) 
{ 
   $HTTP_USER_AGENT = $_SERVER['HTTP_USER_AGENT']; 
}
else if (!empty($HTTP_SERVER_VARS['HTTP_USER_AGENT'])) 
{ 
   $HTTP_USER_AGENT = $HTTP_SERVER_VARS['HTTP_USER_AGENT']; 
} 
else if (!isset($HTTP_USER_AGENT)) 
{ 
   $HTTP_USER_AGENT = ''; 
} 
if (ereg('Opera(/| )([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[2]; 
   $browser_agent = 'opera'; 
} 
else if (ereg('MSIE ([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[1]; 
   $browser_agent = 'ie'; 
} 
else if (ereg('OmniWeb/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[1]; 
   $browser_agent = 'omniweb'; 
} 
else if (ereg('Netscape([0-9]{1})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[1]; 
   $browser_agent = 'netscape';
} 
else if (ereg('Mozilla/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[1]; 
   $browser_agent = 'mozilla'; 
} 
else if (ereg('Konqueror/([0-9].[0-9]{1,2})', $HTTP_USER_AGENT, $log_version)) 
{ 
   $browser_version = $log_version[1]; 
   $browser_agent = 'konqueror'; 
} 
else 
{ 
   $browser_version = 0; 
   $browser_agent = 'other'; 
}
return $browser_agent;
}

function selfURL() { 
$s = empty($_SERVER["HTTPS"]) ? '' : ($_SERVER["HTTPS"] == "on") ? "s" : "";
$protocol = strleft(strtolower($_SERVER["SERVER_PROTOCOL"]), "/").$s; 
$port = ($_SERVER["SERVER_PORT"] == "80") ? "" : (":".$_SERVER["SERVER_PORT"]); 
return $protocol."://".$_SERVER['SERVER_NAME'].$port.$_SERVER['REQUEST_URI']; 
}

function strleft($s1, $s2) { return substr($s1, 0, strpos($s1, $s2)); }

function paginate($start,$limit,$total,$filePath,$otherParams) {
	global $lang;

	$allPages = ceil($total/$limit);

	$currentPage = floor($start/$limit) + 1;

	$pagination = "";
	if ($allPages>10) {
		$maxPages = ($allPages>9) ? 9 : $allPages;

		if ($allPages>9) {
			if ($currentPage>=1&&$currentPage<=$allPages) {
				$pagination .= ($currentPage>4) ? " ... " : " ";

				$minPages = ($currentPage>4) ? $currentPage : 5;
				$maxPages = ($currentPage<$allPages-4) ? $currentPage : $allPages - 4;

				for($i=$minPages-4; $i<$maxPages+5; $i++) {
					$pagination .= ($i == $currentPage) ? "<a href=\"#\" 
					class=\"current\">".$i."</a> " : "<a href=\"".$filePath."?
					start=".(($i-1)*$limit).$otherParams."\">".$i."</a> ";
				}
				$pagination .= ($currentPage<$allPages-4) ? " ... " : " ";
			} else {
				$pagination .= " ... ";
			}
		}
	} else {
		for($i=1; $i<$allPages+1; $i++) {
		$pagination .= ($i==$currentPage) ? "<a href=\"#\" class=\"current\">".$i."</a> "
		: "<a href=\"".$filePath."?start=".(($i-1)*$limit).$otherParams."\">".$i."</a> ";
		}
	}

	if ($currentPage>1) $pagination = "<a href=\"".$filePath."?
	start=0".$otherParams."\">FIRST</a> <a href=\"".$filePath."?
	start=".(($currentPage-2)*$limit).$otherParams."\"><</a> ".$pagination;
	if ($currentPage<$allPages) $pagination .= "<a href=\"".$filePath."?
	start=".($currentPage*$limit).$otherParams."\">></a> <a href=\"".$filePath."?
	start=".(($allPages-1)*$limit).$otherParams."\">LAST</a>";

	echo '<div class="pages">' . $pagination . '</div>';
}

Ok…we have all the details stored in variables so we need to write them into our database. We will create a new file called “visitor_tracking.php” and include it in every page that we want to track:

require_once('visitors_connections.php');//the file with connection code and functions
//get the required data

$visitor_ip = GetHostByName($REMOTE_ADDR);
$visitor_browser = getBrowserType();
$visitor_hour = date("h");
$visitor_minute = date("i");
$visitor_day = date("d");
$visitor_month = date("m");
$visitor_year = date("Y");
$visitor_refferer = GetHostByName($HTTP_REFERER);
$visited_page = selfURL();

//write the required data to database
mysql_select_db($database_visitors, $visitors);
$sql = "INSERT INTO visitors_table (visitor_ip, visitor_browser, visitor_hour,
 visitor_minute, visitor_date, visitor_day, visitor_month, visitor_year, 
 visitor_refferer, visitor_page) VALUES ('$visitor_ip', '$visitor_browser', 
 '$visitor_hour', '$visitor_minute', '$visitor_date', '$visitor_day', '$visitor_month', 
 '$visitor_year', '$visitor_refferer', '$visitor_page')";
$result = mysql_query($sql) or trigger_error(mysql_error(),E_USER_ERROR);

We have everything we need except for a page to display our data. We will start by creating a new page called display_visits.php where we paste the following code:

require_once('visitors_connections.php');//the file with connection code and functions

if ($_GET['start'] == "") $start = 0;
else $start = $_GET['start'];
$limit = 15;

$additionalQuery = "SQL_CALC_FOUND_ROWS ";

mysql_select_db($database_visitors, $visitors);
$query_visitors = "(SELECT ".$additionalQuery." * FROM visitors_table WHERE";

if ($_POST['day']!="") {
$query_visitors .= " visitor_day = '".$_POST['day']."'";
} else {
$query_visitors .= " visitor_day = ".date("d")."";

if ($_POST['month']!="") {
$query_visitors .= " AND visitor_month = '".$_POST['month']."'";
} else {
$query_visitors .= " AND visitor_month = ".date("m")."";
}

if ($_POST['year']!="") {
$query_visitors .= " AND visitor_year = '".$_POST['year']."'";
} else {
$query_visitors .= " AND visitor_year = ".date("Y")."";
}}
$query_visitors .= " LIMIT $start,$limit)";
$insert_visitors = mysql_query($query_visitors, $visitors) or die(mysql_error());
$row_visitors = mysql_fetch_assoc($insert_visitors);
$totalRows_visitors = mysql_num_rows($insert_visitors);

$nbItems = mysql_result(mysql_query("Select FOUND_ROWS() AS nbr"),0,"nbr");
if ($nbItems>($start+$limit)) $final = $start+$limit;
else $final = $nbItems;

echo '<table style="width:100%; border:1px dashed #CCC" cellpadding="3">
      <form id="form1" name="form1" method="post" action="display_visits.php">
       <tr>
        <td>day 
        <select name="day" id="day">
          <option value="" selected="selected"></option>
          <option value="01">01</option>
          <option value="02">02</option>
          <option value="03">03</option>
          <option value="04">04</option>
          <option value="05">05</option>
          <option value="06">06</option>
          <option value="07">07</option>
          <option value="08">08</option>
          <option value="09">09</option>
          <option value="10">10</option>
          <option value="11">11</option>
          <option value="12">12</option>
          <option value="13">13</option>
          <option value="14">14</option>
          <option value="15">15</option>
          <option value="16">16</option>
          <option value="17">17</option>
          <option value="18">18</option>
          <option value="19">19</option>
          <option value="20">20</option>
          <option value="21">21</option>
          <option value="22">22</option>
          <option value="23">23</option>
          <option value="24">24</option>
          <option value="25">25</option>
          <option value="26">26</option>
          <option value="27">27</option>
          <option value="28">28</option>
          <option value="29">29</option>
          <option value="30">30</option>
          <option value="31">31</option>
        </select></td>
        <td>Month 
        <select name="month" id="month">
          <option value="" selected="selected"></option>
          <option value="1">1</option>
          <option value="2">2</option>
          <option value="3">3</option>
          <option value="4">4</option>
          <option value="5">5</option>
          <option value="6">6</option>
          <option value="7">7</option>
          <option value="8">8</option>
          <option value="9">9</option>
          <option value="10">10</option>
          <option value="11">11</option>
          <option value="12">12</option>
        </select></td>
        <td>Year 
        <select name="year" id="year">
          <option value="" selected="selected"></option>
          <option value="2007">2007</option>
        </select></td>
        <td><input type="submit" name="Submit" value="Submit" /></td>
        <td></td>
       </tr>';

echo '<tr>
        <td style="width:15%;border-bottom:1px solid #CCC">IP</td>
        <td style="width:15%;border-bottom:1px solid #CCC">Browser</td>
        <td style="width:15%;border-bottom:1px solid #CCC">Time</td>
        <td style="width:30%;border-bottom:1px solid #CCC">Refferer</td>
        <td style="width:25%;border-bottom:1px solid #CCC">Page</td>
       </tr>';

do {

echo '<tr onmouseout="this.style.backgroundColor=\'\'" 
      onmouseover="this.style.backgroundColor=\'#EAFFEA\'">
        <td>'.$row_visitors['visitor_ip'].'</td>
        <td>'.$row_visitors['visitor_browser'].'</td>
        <td>'.$row_visitors['visitor_hour'].':'.$row_visitors['visitor_minute'].'</td>
        <td>'.$row_visitors['visitor_refferer'].'</td>
        <td>'.$row_visitors['visitor_page'].'</td>
       </tr>';
} while ($row_visitors = mysql_fetch_assoc($insert_visitors));
paginate($start,$limit,$nbItems,"display_visits.php","");

There’s only one small step to do and we’re ready to see some results. We need to include the following line in every page that we need to track results for:

include('visitor_tracking.php');

To see the results please navigate to “display_visits.php” in your browser and start to benefit from tracking your visitors. Ok now I’m testing it, it works fine, I hope you will find it useful somewhere in your website. In our next tutorials we will get a little bit more complicated and add some charts maybe or some calculations to show us some more information like hits vs unique, how many returning visitors etc.

LEAVE A REPLY

Please enter your comment!
Please enter your name here