Logging visitor IP and referrer by PHP and MySQL using ip2long or inet

When visitors come to our website we can collect visitors IP address, referrer, browser details and others details and store them in a MySQL table. Note that this is one of the examples of storing some minimum details of the visitors and not a full script to collect all details. We will try to log minimum important details of the visitors like ip address, referrer, browser details, time of page access and a unique page name. We will keep one unique page name for each page as we will be using same table for all the pages of the site. So the variable to collect the unique page name of the page is assign different values in different pages.

Storing as decimal number

To store (IPv4) IP address in a field we have to use one varchar field with length 16 as a typical address of internet standard dotted format will be like this. 128.128.55.36 . Note that we can convert this address to decimal equivalent value and store them if we want to do some further processing of the address. The four blocks of octal numbers of an IP address has to be converted to its decimal equivalent by using each block of four blocks of octal numbers. To do this PHP has a built in function ip2long which will take the IP address and return the decimal equivalent of this.
Here is an example.

$ip='59.93.102.188';
echo ip2long($ip);
// output of this will be 995976892

We can restore the original octal values by using long2ip function. Here is an example.

$var=995976892;
echo long2ip($var);
// Output of this will be 59.93.102.188

Using MySQL inet_aton function

Finally if we are using MySQL to store IP address then we can use MySQL built in function inet_aton to convert IP address to binary value and store them.

select inet_aton('59.93.102.188')

The output will be 995976892
We can restore our IPv4 address by using inet_ntoa function in MySQL query like this

select inet_ntoa('995976892')

The output will be 59.93.102.188

Table Structure & Script

In this example we will store standard dotted format by using a varchar field but you can use integer field after converting the IP address to decimal number as we discussed above.

Field Size: To store IPV4 address we can keep the field length as 15 ( 4x3 plus 3 separators ). To store IPV6 address we have to keep 39 as field length ( 4x8 plus 7 separators ). To map IPV4 to IPV6 address you have to keep 45 as field length. At present we can go with IPV4 address length

Storing visitor details in MySQL table We will store details in a database table after collecting the variable. The table structure you can download from here. Here are the details.

Getting referrer

Collecting browser details

$agent=@$_SERVER[HTTP_USER_AGENT];

Getting IP address

Tracking page

We can store tracking page name by using server variable. If you are using any unique code for each page then that also you can store

$tracking_page_name2 = $_SERVER["SCRIPT_NAME"];
$tracking_page_name = 'P_Page_name';

Storing time of visit

There is a field dt which stores the date and time of visit. This is done by using automating adding date and time while adding the record.

Query Part

$strSQL = "INSERT INTO track( ref, agent, ip,tracking_page_code, tracking_page_name) VALUES (:ref,:agent,:ip,:tracking_page_code,:tracking_page_name)";

You can read how to insert data to mysql table here. This code is kept inside a footer file or a common file which is used in all pages. So if you are not connected already to database then you can use database connection string to establish connection.

require "config.php";

We have used one if condition checking to insert record if referrer of the visitor is present. We will also check that referrer is not internal pages of the site.

if(strlen($ref) > 2 and !(stristr($ref,"plus2net.com"))){  // exclude referrer from your own site.

Here is the complete code


$ref=@$_SERVER[HTTP_REFERER];
$agent=@$_SERVER[HTTP_USER_AGENT];
$ip=@$_SERVER['REMOTE_ADDR'];
$tracking_page_name2 = $_SERVER["SCRIPT_NAME"];
if(strlen($ref) > 2 and !(stristr($ref,"plus2net.com"))){  // exclude referrer from your own site. 
$strSQL = "INSERT INTO track( ref, agent, ip,tracking_page_code, tracking_page_name) VALUES (:ref,:agent,:ip,:tracking_page_code,:tracking_page_name)";

$sql=$dbo->prepare($strSQL);

$sql->bindParam(':ref',$ref,PDO::PARAM_STR, 250);
$sql->bindParam(':agent',$agent,PDO::PARAM_STR, 250);
$sql->bindParam(':ip',$ip,PDO::PARAM_STR, 20);
$sql->bindParam(':tracking_page_name',$tracking_page_name2,PDO::PARAM_STR, 100);
$sql->bindParam(':tracking_page_code',$tracking_page_name,PDO::PARAM_STR, 100);

if($sql->execute()){
// Part of the code to execute after successful execution of query

//echo " Success ";
}
else{ 
// Part of the code to execute if query fails ///
//print_r($sql->errorInfo()); 
}
///////////// inserted details 
}

The above code will insert the visitor details to the table name track in mysql database.

You can read the article on reading data from table to display the data of the visitors in a page.

Storing visitor details using CSV file.

Details can be stored in Comma Separated Value ( .csv ) file. Instead of storing in a MySQL database we can store them in a csv file. We will use fputcsv() PHP function to store data in a csv file.

Here is the code.

<?Php
$tm=time();
$ref=@$_SERVER[HTTP_REFERER];
$agent=@$_SERVER[HTTP_USER_AGENT];
$ip=@$_SERVER['REMOTE_ADDR'];
// opening the file with file pointer at end of the file
$my_file = fopen("visitors.csv","a");

fputcsv($my_file,array($tm,$ref,$agent,$ip));
fclose($my_file);
?>

By using fopen function we are opening a file, if the file is not already created then it will create the file. The mode of opening the file is a, so the pointer will remain at the end of the file. So each time a new visitor comes to the page , a new line will be added at the end.

Storing data in day wise

You can create one csv file for each day. For this change the file name like this.

$file_name="visitors_".date("m-d-y").".csv";
// opening the file with file pointer at end of the file
$my_file = fopen($file_name,"a");

Now you will get one file for each day storing your visitors details.

To display or read csv file you can use PHP function fgetcsv()

Here is the dump of the table track


CREATE TABLE IF NOT EXISTS `track` (
  `id` int(6) NOT NULL AUTO_INCREMENT,
  `ref` varchar(250) NOT NULL DEFAULT '',
  `agent` varchar(250) DEFAULT '',
  `ip` varchar(20) NOT NULL DEFAULT '',
  `tracking_page_code` varchar(100) DEFAULT '',
  `tracking_page_name` varchar(100) DEFAULT '',
  `dt` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=0 ;

How to insert visitor IP address and referrer in a table