|
|
How to insert visitor IP address and referrer in a table
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.
Note that the script already have mysql connection and here is the code to be used for logging visitor details to mysql table. We have used one sql insert query to add record to the table on each opening of the page.
$tm=time();
$ref=@$HTTP_REFERER;
$agent=@$HTTP_USER_AGENT;
$ip=@$_SERVER['REMOTE_ADDR'];
$strSQL = "INSERT INTO track(tm, ref, agent, ip, tracking_page_name) VALUES ('$tm','$ref','$agent','$ip','$tracking_page_name')";
$test=mysql_query($strSQL);
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.
Here is the dump of the table track
CREATE TABLE `track` (
`id` int(6) NOT NULL auto_increment,
`tm` varchar(15) NOT NULL default '',
`ref` varchar(250) NOT NULL default '',
`agent` varchar(250) NOT NULL default '',
`ip` varchar(20) NOT NULL default '',
`ip_value` int(11) NOT NULL default '0',
`domain` varchar(20) NOT NULL default '',
`tracking_page_name` varchar(10) NOT NULL default '',
UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 ;
|
| |
| | steve | 30-11-2009 |
|---|
| I would like to log my visitors ip address into a flat file. | | laurence | 18-01-2010 |
|---|
| what is the point of Storing as decimal number? what do you gain from doing that as opposed to storing ip in the normal format? | | sayar ahmad kuchy | 27-01-2010 |
|---|
| i like this its very informative article.... | | Sam | 21-10-2011 |
|---|
| can you save as a csv? |
|
|
|
|
|
|
