Web Developer and Interaction Designer: Maxmind GeoIP Counter How To...kind of

For the last 2 days i was trying to setup a basic geoip localization counter system for my site, i looked at 2 options ip-to-country and maxmind I found maxmind packedges more friendly. So this is a how to based on maxmind PHP pure module.

First we head to maxmind developer page:
http://www.maxmind.com/app/api, we’ll see diferent modules for common programing languages the Apache module seem the right choice since it’s faster based on performance, but more hard to set up, and since we like things to be easy we’ll use the PHP pure module :P.

We need now to download the GeoIP.dat and the geoip.inc wich is a php file. the GeoIP.dat file is in gzip format so u can use comercial tools like winrar or a wonderful free tool called 7zip to extract the file.

Now we need to upload the files to the root directory in our web server or local server. From there is very simple to use, example


      include("geoip.inc");
      $gi = geoip_open("GeoIP.dat",GEOIP_STANDARD);
      $remote_addr = $_SERVER['REMOTE_ADDR'];
      echo geoip_country_code_by_addr($gi, $remote_addr) . "\t" .
      geoip_country_name_by_addr($gi, $remote_addr) . "\n";
      geoip_close($gi);

This will output the country code (MX) and the country name(México) of anyone visiting our page. You can see a working example here.
For the flags to work you need to download the flags file. And add these lines of code:


      $flag = strtolower(geoip_country_code_by_addr($gi,         $client_ip)); 
      echo "<img src=\"/flag/".$flag.".gif\" />";

So thats pretty straight forward, now what we need is to save the results in a mysql database, wich will make things more interesting in terms of website statistics.

Now we need to get the CSV wich means Comma Separated Values, from here
Once we finish downloading our file we can see the structure looks something like this.



"80.247.159.0","80.247.159.255","1358405376","1358405631","NG","Nigeria"

"80.247.160.0","80.247.175.255","1358405632","1358409727","IE","Ireland"

"80.247.192.0","80.247.203.255","1358413824","1358416895","NL","Netherlands"

"80.247.204.0","80.247.205.255","1358416896","1358417407","BE","Belgium"

"80.247.206.0","80.247.207.255","1358417408","1358417919","NL","Netherlands"

Each row describes a range of IP addresses and the country it is allocated to. The format of each line is:


     {start_ip},{end_ip},{start_long},{end_long},{cc},{country name}

Since we already have the module that takes care of the ip address we will only focus in the last two fields wich are {cc} and {country name}.Ok now were are ready to import our CSV into our mysql database.

We need to create a table to fit its contents the SQL query to do so will be:



create table csv (

cc char(2) not null,

cn varchar(50) not null

)

The table of the name is optional u can name it whatever u want, this table is just for importing our data from the CSV we will drop it or delete it when we are done.

At this point i stumble across a problem on how to import the data, beacuse i don’t really have access to the mysql import utility, and this is not my server actually, so what to do?, fire up a php script that will handle all of the import tasks.



//include our basic db file wich connects to the database
   include ('./com/mysql.php');
   connect ('user','pass','db','server');

//this function is to replace quotes that some fields have 
    function replace_quote ($string) {
$string = str_replace("'","",$string);
return str_replace("'","",$string);
}

here we type the name of our mysql table, is always good to have an array in case we want to add more tables to the mix

       
      $table = array ('geoip');

populate the tables array and empty them if we
have values inside our tables in this case when we
want to update


      foreach ($table as $tablename) {
       $delete_r = "truncate table `$tablename`";
       $result = mysql_query($delete_r);
      }

set up the variables that will take care of errors and the time it took the
query to execute


  $num_errors = 0;
  $rows_affec = 0;
  $time = microtime();
  $time = explode(" ",$time);
  $time = $time [0] + $time [1];
  $startime = $time;

open our csv file and read it


   $file_handle = fopen('geoip.csv','r');

start the loop wich will insert the values of the csv into our table


  while (($row=fgetcsv($file_handle,50000)) != false) {

$num = count($row);

//since we only want to insert 2 fields we will make another loop

//this basicly tells exactly the fields we only want since we have

//5 fields in our csv we will start at number 3 remember the count starts at 0
  for ($c=3; $c < $num; $c++) {

//prepare to insert values
  $insert_r = "insert into `$tablename` values ('".$row [4]."','".replace_quote($row [5])."')";

//insert values
  $result = mysql_query($insert_r);

//error handiling
  if (mysql_error()) {
  $num_errors += 1;

//this vars are useful if we wanna know what kind of erros we got in the sql query
  $errors_n = mysql_errno();
  $error = mysql_error();
  }else {

//count how many rows are affected by the query wit no errors
  $rows_affec += 1;
  }
   }

//end time of the query executing
   $endtime = $mtime;

//$insert_r = "insert into `$tablename` values (".implode("","","","","","",$row).")";

//$result = mysql_query($insert_r);
  }

//calculate the time it took
   $exec_time = $startime-$endtime;

//print a report 

print ("the query took $exec_time seconds,".

"and affected $rows_affec rows with $num_errors".

"errors number of error $errors_n and query error $error");

//close the file

fclose($file_handle);

By now the data from our CSV file should be inserted in our mysql table. It is importan to mention once we finish with this script i recomend to delete it from our server!.

Now lets take a look into our table, wow that one ugly mess we have field values repeated all over our table, what a shame. Next step, delete each repeated field one by one that will take time but thats the only way..not!. In order to eliminate repeated fields we need to create another table in this case wit the same fieldnames example:



create table geoip (

cc char(2) not null,

cn varchar(50) not null,

counter int(10) not null default '0'

);

Notice how we added a new field called ‘counter’, this field will tell us how many visitors we had from that specific country.

With our table created by now, how the hell are we gonna pass all the data?, well heres where the power of SQL comes to the rescue with this stament:



insert into geoip select distinct cc,cn,0 from csv;

This will insert all of our data into our geoip table but only unique values so no field value is repeated. Well that was simple huh?.

So now is all about writing a PHP script that will take care of updating our counter example:



include('geoip.inc');

include ('com/mysql.php');

$gi = geoip_open('GeoIP.dat',GEOIP_STANDARD);

$client_ip = $_SERVER['REMOTE_ADDR'];

$img = strtolower(geoip_country_code_by_addr($gi, $client_ip)); 

$cc = geoip_country_code_by_addr($gi, $client_ip) ;

$cn = geoip_country_name_by_addr($gi, $client_ip);

if (!empty($client_ip) ) {

connect ('user','pass','db','server');

$query = "update geip set counter=counter+1 where cc='".$cc."' and cn='".$cn."'" ;

$result = mysql_query($query) || die ('cannot execute query'. mysql_error());

if ($result) {

geoip_close($gi);

}

}

This will basically update itself every time the visitor access this page.

With that in place all is left is to add another script that will display the result in a table like this:


 $query = 'SELECT `cn`,`counter` FROM `country` where cn like cn ORDER BY `counter` DESC limit 0,30 ';

$result = mysql_query($query);

$numrows = mysql_num_rows($result);

echo '

<table style="margin:0 auto;width: 350px;border: 1px dotted #E5E5E5;" cellpadding="5" cellspacing="1" > 

<tr>

<th aling="center">Visits by Country</th>

<tr>';
  while ($row = mysql_fetch_array($result) ) {
  $numrows++;
    if ($numrows % 2) {
     echo "<tr bgcolor=\"#333333\">";
   }else {
     echo "<tr bgcolor=\"#003366\">";
   }
  echo '
    <td>' . $row['cn'] . '</td>
    <td>' . $row['counter'] . '</td>
</tr>';
}
echo "</table>";

So thats pretty much it, after all this u can go to the store buy a case of beer and some neurotransmitters ;).

Guillermo Cruz

Web Development and Interaction Design that really cuts the cake

weblog

Maxmind GeoIP Counter How To...kind of

categories

archives

search