Geo IP - install Maxmind GeoIP using PHP + MySQL

Geo IP Introduction
Maxmind GeoIP addresses database is an excellent reference for learning where your website visitors are located. Once installed simply pass it an IP address or number query and it can return information about Country, Region, City, Longitude and Latitude co-ordinate locations. You can then manipulate this data to your advantage for statistical analysis, targeted regional advertisements, default language, currency and delivery selections the possibilities are really endless.

About Installing GeoIP
Maxmind are kind enough company to offer two solutions that query IP to country all for free, providing you quote ( "This product includes GeoLite data created by MaxMind, available from http://www.maxmind.com/." ) wherever you use their data. This article is focused on installing the CSV format for use with PHP + MySQL.

Personally I struggling to install the Geo IP database using phpMyAdmin on my remote production server and I couldn't find a tutorial for my circumstance, eventually I managed to do it but it was a very time consuming process which I wish never to go through again, you can read the article I wrote on this method here to judge for yourself Maxmind geoip setup tutorial using phpMyAdmin. It was clear that an easier solution was needed especially when Maxmind release new CSV updates at the start of every month, I didn't want to be going through that process everytime. For my benefit and others I took it upon myself to develop a re-usable script that would make maintaining a GeoIP database a simple task. This article explains how to install the re-usable PHP and MySQL script created by Bartomedia to manage your own Maxmind Geo IP database on your web server quickly and easily.

GeoIP Installation Requirements
Before you proceed you should know that this article assumes you have PHP and MySQL already installed on your web server, you should also have permission to create, edit and delete tables in MySQL. FTP access is also required so you can upload a copy of the Maxmind GeoLite Country CSV file and the PHP script to manage the GeoIP database.

PHP + MySQL GeoIP Manager
Step 1
Create a new file and name it something simple like "GeoIPManager.php" then copy the following code from the grey box below and paste it into the page.


<?php

/*==============================================================================

Application: PHP + MySQL GeoIP Manager
Author: Bartomedia - http://bartomedia.blogspot.com/
Date: 14th December 2007
Description: GeoIP Manager for PHP + MySQL easy install script
Version: V1.0

------------------------------------------------------------------------------*/

// DATABASE CONNECTION AND SELECTION
$host = "localhost";
$username = "root";//
$password = "root";//
$database = "geoipdb";//

// DEFINE THE PATH AND NAME TO THE MAXMIND CSV FILE ON YOUR SERVER
$filename = "GeoIPCountryWhois.csv";
$filepath = $_SERVER["DOCUMENT_ROOT"];


// DO NOT EDIT BELOW THIS LINE
//////////////////////////////////////////////////////////////////////////////////
$error_msg = "";
$message = "";
$dependent = 1;

if ( ! ereg( '/$', $filepath ) )
{ $filepath = $filepath."/"; }

// the @ symbol is warning suppression so a warning will not be thrown back
// to the user, be careful not to over-rely on warning suppression, every
// warning suppression should be modified with an if else to catch the
// warning

if ( ! $Config["maindb"] = @mysql_connect($host, $username, $password) )
{
$error_msg.= "There is a problem with the <b>mysql_connect</b>, please check the username and password !<br />";
$dependent = 0;
}
else
{
if ( ! mysql_select_db($database, $Config["maindb"]) )
{
$error_msg.= "There is a problem with the <b>mysql_select_db</b>, please check that a valid database is selected to install the GeoIP database to !<br />";
$dependent = 0;
}
else
{
// CHECK FOR SAFE MODE
if( ini_get('safe_mode') )
{
// Do it the safe mode way
$error_msg.= "Warning Safe Mode is ON, please turn Safe Mode OFF to avoid the script from timing out before fully executing and installing the GeoIP database.<br />";
$dependent = 0;
}
else
{
// MAX EXECUTION TIME OF THIS SCRIPT IN SEC
set_time_limit(0);
// CHECK FOR MAXMIND CSV FILE

if ( ! file_exists($filepath.$filename) )
{
$error_msg.= "The Maxmind GeoLite Countries CSV file could not be found !<br />";
$error_msg.= "Please check the file is located at ".$filepath." of your server and the filename is \"".$filename."\".<br />";
$dependent = 0;
}
else
{
$lines = count(file($filepath.$filename));
$filesize = filesize($filepath.$filename);
$filectime = filectime($filepath.$filename);
$filemtime = filemtime($filepath.$filename);
$fileatime = fileatime($filepath.$filename);
}
}
}
}




// SCRIPT FUNCTIONS
function check_GeoIP_status()
{
global $Config;
global $lines;
$result = mysql_query("SHOW TABLE STATUS LIKE 'ip'", $Config["maindb"]);
if($ip = mysql_fetch_array($result))
{
// Check within 3 rows difference for new CSV
// updates usually feature many more lines of code
if ( $ip["Rows"] > ($lines - 3 ) )
{return "OK";}
else
{return "UPDATE";}
}
else
{return "CREATE";}
}

function load_new_GeoIP_data($filename)
{
global $Config;
global $message;

$query = "DROP TABLE IF EXISTS `csv`"; // EMPTY
if ( ! $result = mysql_query( $query, $Config["maindb"] ) )
{
$message.= "Failed to delete the `csv` table, Please check you have permission to drop tables.<br />";
return false;
}

$query = "CREATE TABLE IF NOT EXISTS `csv` (
`start_ip` char(15)NOT NULL,
`end_ip` char(15)NOT NULL,
`start` int(10) unsigned NOT NULL,
`end` int(10) unsigned NOT NULL,
`cc` char(2) NOT NULL,
`cn` varchar(50) NOT NULL
) TYPE=MyISAM;";
if ( ! $result = mysql_query( $query, $Config["maindb"] ) )
{
$message.= "Failed to create the `csv` table, Please check you have permission to create tables.<br />";
return false;
}

$query = "LOAD DATA LOCAL INFILE \"".$filename."\"

INTO TABLE `csv`
FIELDS
TERMINATED BY \",\"
ENCLOSED BY \"\\\"\"
LINES
TERMINATED BY \"\\n\"
(
start_ip, end_ip, start, end, cc, cn
)";
if ( ! $result = mysql_query( $query, $Config["maindb"] ) )
{
$message.= "Failed to load the Maxmind CSV file into the `csv` table.<br />";
return false;
}

return true;
}


function build_GeoIP_data()
{
global $Config;
global $message;

$query = "DROP TABLE IF EXISTS `cc`"; // DELETE
if ( ! $result = mysql_query( $query, $Config["maindb"] ) )
{
$message.= "Failed to delete the `cc` table, Please check you have permission to drop tables.<br />";
return false;
}

$query = "CREATE TABLE IF NOT EXISTS `cc` (
`ci` tinyint(3) unsigned NOT NULL auto_increment,
`cc` char(2) NOT NULL,
`cn` varchar(50) NOT NULL,
PRIMARY KEY (`ci`)
) TYPE=MyISAM AUTO_INCREMENT=1 ;";
if ( ! $result = mysql_query( $query, $Config["maindb"] ) )
{
$message.= "Failed to create the `cc` table, Please check you have permission to create tables.<br />";
return false;
}

$query = "DROP TABLE IF EXISTS `ip`"; // DELETE
if ( ! $result = mysql_query( $query, $Config["maindb"] ) )
{
$message.= "Failed to delete the `csv` table, Please check you have permission to drop tables.<br />";
return false;
}

$query = "CREATE TABLE IF NOT EXISTS `ip` (
`start` int(10) unsigned NOT NULL,
`end` int(10) unsigned NOT NULL,
`ci` tinyint(3) unsigned NOT NULL,
KEY `start` (`start`),
KEY `end` (`end`)
) TYPE=MyISAM;";
if ( ! $result = mysql_query( $query, $Config["maindb"] ) )
{
$message.= "Failed to create the `ip` table, Please check you have permission to create tables.<br />";
return false;
}

// EXTRACT DATA FROM CSV FILE AND INSERT INTO MYSQL
$query = "INSERT INTO `cc` SELECT DISTINCT NULL, `cc`, `cn` FROM `csv`;";
if ( ! $result = mysql_query( $query, $Config["maindb"] ) )
{
$message.= "Failed to insert data into the `cc` table from the `csv` table, Please check you have permission to insert in tables.<br />";
return false;
}

// OPTIMIZE MYSQL
$query = "INSERT INTO `ip` SELECT `start`, `end`, `ci` FROM `csv` NATURAL JOIN `cc`;";
if ( ! $result = mysql_query( $query, $Config["maindb"] ) )
{
$message.= "Failed to insert data into the `ip` table from the `csv` table, Please check you have permission to insert in tables.<br />";
return false;
}

return true;
}

function cleanup_GeoIP_data()
{
global $Config;
global $message;

$query = "DROP TABLE IF EXISTS `csv`"; // DELETE
if ( ! $result = mysql_query( $query, $Config["maindb"] ) )
{
$message.= "Failed to delete the `csv` table, Please check you have permission to drop tables.<br />";
return false;
}
return true;
}

////////////////////////////////////////////////////////

// FUNCTIONS TO SELECT DATA
function getALLfromIP($addr)
{
global $Config;
// this sprintf() wrapper is needed, because the PHP long is signed by default
$ipnum = sprintf("%u", ip2long($addr));
$query = "SELECT start, cc, cn FROM ip NATURAL JOIN cc WHERE end >= $ipnum ORDER BY end ASC LIMIT 1";
$result = mysql_query($query, $Config["maindb"]);
$data = mysql_fetch_array($result);

if((! $result) || mysql_numrows($result) < 1 || $data['start'] > $ipnum )
{
return false;
}
return $data;
}

function getCCfromIP($addr) {
$data = getALLfromIP($addr);
if($data) return $data['cc'];
return false;
}

function getCOUNTRYfromIP($addr) {
$data = getALLfromIP($addr);
if($data) return $data['cn'];
return false;
}

function getCCfromNAME($name) {
$addr = gethostbyname($name);
return getCCfromIP($addr);
}

function getCOUNTRYfromNAME($name) {
$addr = gethostbyname($name);
return getCOUNTRYfromIP($addr);
}

// EXECUTE SCRIPTING
//////////////////////////////////////////////////////////////


if ( isset ($_REQUEST["command"]) && $_REQUEST["command"] == "cancel" )
{
header( "Location: http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF'] );
exit;
}

if ( $dependent == 0 )
{
$error_msg.= "Please correct the script before continuing !<br />";
}
else
{
// continue with the rest of the script

// CREATE NEW GEOIP DATABASE
if ( ! isset ($_REQUEST["command"]) )
{
$message.= "Current Maxmind GeoIP CSV data<br />";
$message.= "Records = ".$lines." Rows<br />";
$message.= "filesize = ".$filesize." bytes<br />";
$message.= "created = ".date("D j M Y, \a\\t g.i:s a", $filectime)."<br />";
$message.= "modified = ".date("D j M Y, \a\\t g.i:s a", $filemtime)."<br /><br>";

switch (check_GeoIP_status())
{
case "OK":
$message.= "The GeoIP database is fully up to date !<br />";
break;
case "UPDATE":
$message.= "A newer version of the GeoIP country database has been detected !<br />";
$message.= "Would you like to update the GeoIP database ? ";
$message.= "<a href=\"http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?command=update\">yes</a><br />";
break;
case "CREATE":
$message.= "The script could not detect a GeoIP country database<br />";
$message.= "Would you like to create a new GeoIP database ? ";
$message.= "<a href=\"http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?command=create\">yes</a><br />";
break;
}
}


// CREATE NEW GEOIP DATABASE
if ( isset ($_REQUEST["command"]) && $_REQUEST["command"] == "create" && ! isset ($_REQUEST["confirm"]) )
{
$message.= "Note : Creating a GeoIP database can take as long as 5 minutes depending on you servers processor speed.<br />";
$message.= "After you click 'yes' please wait until the script has finished executing before performing any other action.<br />";
$message.= "Are you sure you would like to create a new GeoIP database ? ";
$message.= "<a href=\"http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?command=create&confirm=yes\">yes</a> / ";
$message.= "<a href=\"http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?command=cancel\">cancel</a><br />";
}


// UPDATE GEOIP DATABASE
if ( isset ($_REQUEST["command"]) && $_REQUEST["command"] == "update" && ! isset ($_REQUEST["confirm"]) )
{
$message.= "Note : Updating the GeoIP database can take as long as 5 minutes depending on you servers processor speed.<br />";
$message.= "After you click 'yes' please wait until the script has finished executing before performing any other action.<br />";
$message.= "Are you sure you would like to update the GeoIP database ? ";
$message.= "<a href=\"http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?command=update&confirm=yes\">yes</a> / ";
$message.= "<a href=\"http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF']."?command=cancel\">cancel</a><br />";
}

// CREATE NEW GEOIP DATABASE
if ( isset ($_REQUEST["command"]) && $_REQUEST["command"] == "create" && isset ( $_REQUEST["confirm"]) && $_REQUEST["confirm"] == "yes" )
{
if ( load_new_GeoIP_data($filepath.$filename) )
{
if ( build_GeoIP_data() )
{
if ( cleanup_GeoIP_data() )
{
header( "Location: http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF'] );
exit;
}
}
}
}

// UPDATE GEOIP DATABASE
if ( isset ($_REQUEST["command"]) && $_REQUEST["command"] == "update" && isset ( $_REQUEST["confirm"]) && $_REQUEST["confirm"] == "yes" )
{
if ( load_new_GeoIP_data($filename) )
{
if ( build_GeoIP_data() )
{
if ( cleanup_GeoIP_data() )
{
header( "Location: http://".$_SERVER['SERVER_NAME'].$_SERVER['PHP_SELF'] );
exit;
}
}
}
}
}




?><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title>INSTALL MAXMIND GEOIP COUNTRIES DATABASE</title>
</head>

<body>
<h1>Bartomedia - http://bartomedia.blogspot.com/</h1>

<h3>GEO IP Manager for PHP + MySQL</h3>
<?php

if ( $error_msg != "" )
{
print $error_msg;
}
else
{
print $message;
}

$result = @mysql_query("SELECT * FROM ip LIMIT 1", $Config["maindb"]);
$ip_num_rows = @mysql_num_rows( $result );
$result = @mysql_query("SELECT * FROM cc LIMIT 1", $Config["maindb"]);
$cc_num_rows = @mysql_num_rows( $result );

if ( $ip_num_rows > 0 && $cc_num_rows > 0 )
{
if ( isset( $_POST["ip"] ) && $_POST["ip"] != "" )
{
$ip = $_POST["ip"];
$cc = getCCfromIP($ip);
$cn = getCOUNTRYfromIP($ip);
if ( $cc != false && $cn != false )
{
print "<p>[".$cc."] ".$cn."</p>";
}
else
{
print "<p>IP not found !</p>";
}
}
else
{$ip = "";}
?>

<form action="<?= $_SERVER['PHP_SELF'] ?>" method="post">
<label>IP : <input name="ip" type="text" value="<?= $ip ?>" /></label>

<input name="submit" type="submit" value="submit" />
</form>
<?
}

?>
</body>

</html>


Step 2
When you have created the file adjust the MySQL database connection variables to your own username, password and the name of your own database. Upload the file to the root of your web server by FTP together with the latest copy of the Maxmind Geolite countries CSV file, if you decide to rename the CSV file or upload the two files to a different location you will have to adjust the filename or filepath variables in the script.

Step 3
Open your web browser and access the script you just created and uploaded to your webserver. Follow the simple onscreen instructions to install the database. Within a few minutes you should have your very own GeoIP database up and running. The script also features its own IP querying tool.

If you are having any troubles with this script please leave comments below I will reply as quickly and as best i can.




Querying GeoIP from your script
Create a new php include file, I named mine 'geofunctions.inc.php' appropriately for its purpose, copy and paste into the file the code from the grey box below.

<?php
// FUNCTIONS TO SELECT DATA
function getALLfromIP($addr)
{
global $Config;
// this sprintf() wrapper is needed, because the PHP long is signed by default
$ipnum = sprintf("%u", ip2long($addr));
$query = "SELECT start, cc, cn FROM ip NATURAL JOIN cc WHERE end >= $ipnum ORDER BY end ASC LIMIT 1";
$result = mysql_query($query, $Config["maindb"]);
$data = mysql_fetch_array($result);

if((! $result) || mysql_numrows($result) < 1 || $data['start'] > $ipnum )
{
return false;
}
return $data;
}

function getCCfromIP($addr) {
$data = getALLfromIP($addr);
if($data) return $data['cc'];
return false;
}

function getCOUNTRYfromIP($addr) {
$data = getALLfromIP($addr);
if($data) return $data['cn'];
return false;
}

function getCCfromNAME($name) {
$addr = gethostbyname($name);
return getCCfromIP($addr);
}

function getCOUNTRYfromNAME($name) {
$addr = gethostbyname($name);
return getCOUNTRYfromIP($addr);
}
?>

Upload the 'geofunctions.inc.php' file to the root of your server or to any location you like. You will include this file in all the pages that you require GeoIP querying functions.

From within the page that you require to query GeoIP data use the code in the grey box below as an example.

<?php


// GEO LOCATIONS DATABASE CONNECTION
$Config["geolocationdb"] = mysql_connect("host", "username", "password");
mysql_select_db("geolocationdb", $Config["geolocationdb"]);

include('geofunctions.inc.php');

$remote_address = $_SERVER['REMOTE_ADDR'];

print "<p>".getCCfromIP($remote_address)."</p>\n";
print "<p>".getCOUNTRYfromIP($remote_address)."</p>\n";
?>

Faster GeoIP MySQL Retrieval
Much of my geoip querying is taken from examples at http://vincent.delau.net/php/geoip.html however I have made modifications in my 'geolocations.inc.php' that search and retrieve data faster and more efficiently from comments made at J.Coles weblog following the excellent advice of Andy Skelton, Nikolay Bachiyski and also that of Mark Robson. Nikolay mentions that the GeoLite Country database actually contains lots of gaps so the script returns the country in the nearest range but it may actually be an unassigned gap, his solution was to create a script to fill the gaps with dummy rows and return "-" however this is not essential. Simply query the IP number against the returned start value if you are searching by the end value if it appears outside the range you can simply return "-" or false rather than filling in gaps.

Feel free to use the script wherever you like, if you do use it all I ask for in return is a link back to my Blog

Maxmind GeoIP install setup tutorial using PHPMyAdmin

After struggling to find a straight forward tutorial on how to install the Maxmind's GeoIP Country Database in PHP and MySQL using PHPMyAdmin I decided to write my own step by step guide with illustrations. My tutorial is based heavily upon the tutorial i found at http://vincent.delau.net/php/geoip.html and much of the credit should go to the author.

The main trouble I had was the CSV upload file limit size was 2.5mb where as the maxmind geolite countries database is 8+mb, transfering this much data is bound to cause problems for any server. Because I didnt want to mess around with the PHP.INI file and change the upload limit on the server and I had APACHE, PHP and MySQL installed on my local machine, I decide the best way would be to create the database locally then split it up if neccessary and upload it to my production server that way.

Creating the GeoIP database
Simply open up your PHPMyAdmin and click the SQL query window in the top left, dont worry if your PHPMyAdmin looks different the functions are essentially all the same. Below is an image where to find the SQL Query Window.



Click this button and a SQL Query Window will popup.

If a 'geoip' database doesnt already exist and you have permission copy and paste the following code into that SQL Query Windows text area else skip this bit

CREATE DATABASE geoip;
USE geoip;

now copy and paste the following code into that SQL Query Windows text area

CREATE TABLE cc (
ci tinyint(3) unsigned NOT NULL auto_increment,
cc char(2) NOT NULL,
cn varchar(50) NOT NULL,
PRIMARY KEY (ci)
) AUTO_INCREMENT=1 ;

CREATE TABLE csv (
start_ip char(15)NOT NULL,
end_ip char(15)NOT NULL,
start int(10) unsigned NOT NULL,
end int(10) unsigned NOT NULL,
cc char(2) NOT NULL,
cn varchar(50) NOT NULL
);

CREATE TABLE ip (
start int(10) unsigned NOT NULL,
end int(10) unsigned NOT NULL,
ci tinyint(3) unsigned NOT NULL
);

Dont worry about what is selected in the "Run SQL query/queries on database" Select menu or if the two checkbox options are selected or not then click the 'GO' button. When Your SQL query has been executed successfully close the MySQL Query Window we dont need that anymore. You should now see the following...



Importing GeoIP CSV data into MySQL database
Click the csv link on the left side under the geoip tables list, when the page loads scroll down to the bottom and click the "Insert data from a text file into the table" at the very bottom.



If you havn't done so already download the latest GeoLite Country CSV format this version is slightly less accurate than the full one but it is free and save it somewhere easy to find on your PC, the download can also be found here at http://www.maxmind.com/app/geoip_country.
It is essential to set up the text file importer or else the data will not import correctly, first lets find the Maxmind CSV file we are going to import that we have saved on our local machine. Click the browse button to locate the file, because of the specific way maxmind saves the data in the CSV file we have to adjust our text importer. The only options we should have to change are "Fields terminated by" should be change to a comma "," instead of the default colon ";" and "Lines terminated by" to linefeed "\n" instead of the default carriage return and line feed "\r\n". Then Click Submit, see image below for visual example.



When the import has completed successfully we can proceed to extract IP ranges and countries into two different tables. By default all this data is in one file, Maxmind simply adds new ip range and numbers to an associating country and appends the data to the end of the CSV file monthly. By keeping all the data in a single Comma Separated Values Format it is much easier to maintain and update however it is very slow to search and parse the data. We are going to optimize our database for speed and size, by separating the two data sources, IP range from countries we can greatly reduce the amount of data mysql has to search through. We later simply join the two tables back together again by an ID number when we perform a search or query on our geo database.

Optimize GeoIP database for size and speed
Ok lets extract all the different countries from the 'csv' table into our 'cc' table, open up the SQL Query Window again, Copy and Paste the following code into the text area and click the 'GO' button.

INSERT INTO cc SELECT DISTINCT NULL, cc, cn FROM csv;

When Your SQL query has been executed successfully we can extract the IP Range and Numbers data from the 'csv' table and insert it into the 'ip' table. Copy and Paste the following code into the SQL Query Window windows text area and click the 'GO' button.

INSERT INTO ip SELECT start, end, ci FROM csv NATURAL JOIN cc;

The last SQL statement simply tells MySQL to insert the unique start and end IP number range of a particular country into the 'ip' table, the 'ci' field in the 'ip' table indicates the id of the country in the 'cc' table, we use the two 'ci' fields later to join the two tables together in select query statment.

Our geoip database is now set up but first, since the 'csv' table is of no use to us any more go ahead and delete it.

During this install you may have noticed we no longer have any reference to IP address ranges only numbers this is because PHP has some inbuilt functions we can use to convert IP address to an IP number and vise versa ip2long() and long2ip(). These functions also save us a lot of space overheads in MySQL and speed up our searches.

Installing GeoIP database through PHPMyAdmin
Ok we have successfully installed the Geo IP database on our local machine lets go ahead and prepare our production server.

Update Maxmind GeoIP database
If your production server already contains an older version of the Maxmind GeoIP database it is recommended that you fully delete all the TABLES within that database but not the actual database.

Install New Maxmind GeoIP database
If you are installing the Maxmind GeoIP database from scratch then you will have to create a new database. If you have permission to create a new database through PHPMyAdmin copy and paste the following code into the SQL Query Windows text area and the click 'GO'.

CREATE DATABASE geoip;
USE geoip;

Some web hosts employ a control panel which directly controls the creation of databases so you may have to create a database through this tool first if this is the case name your database 'geoip'. Then open up PHPMyAdmin with this database selected.

First lets begin with the 'cc' table because it is the smaller of the two it only has 234 rows or there abouts we can probably export this from our local machine and install it on our production server in one go. Click the 'cc' table list from the geoip tables list on the left side in PHPMyAdmin then click the export tab at the top, by default the preselected settings are already correct see image below then just click 'GO'.

Now copy all the text in the text area including the create table and insert data and paste it into the SQL Query Window on your production servers PHPMyAdmin, don't worry about the comments MySQL filters them out.

Now the larger MySQL 'ip' table my row count is 96459 even our clipboard will struggle with this so it is best if we probably start by just creating the table structure by itself on the production server. Click the 'ip' table list from the geoip tables list on the left side in PHPMyAdmin then click the export tab at the top, this time we are going to modify the default preselected settings, leave everything the same as default but this time uncheck the 'Data' checkbox see image below then just click 'GO'.



Now copy all the text in the exported text area which tells mysql of the table structure and paste it into the SQL Query Window on your production servers PHPMyAdmin, again don't worry about the comments MySQL filters them out.

This time on the export page of the 'ip' table uncheck the structure checkbox but leave the data checkbox checked, where it says Dump (type number of rows here) row(s) starting at record # (type the starting row index here) we will be repeating this export process 4 times, each time we do this we will be changing the starting row index. Select 30000 rows starting from row 0 on my machine it took roughly 10-15 seconds to export, then copy the text area its probably easiest to right click the text area and select all then copy and paste it into the SQL Query Window on your production servers PHPMyAdmin, this insert roughly took 3 minutes and 30 seconds to complete and performance depends on your bandwidth and servers processor. See images below.





I completed the task in blocks of 30000 rows and I had to repeat the process 4 times, where it says Dump (type number of rows here) row(s) starting at record # (type the starting row index here).Repeat the above process 4 times and change the export data as follows

Dump (30000) row(s) starting at record # (0).
Dump (30000) row(s) starting at record # (30000).
Dump (30000) row(s) starting at record # (60000).
Dump (30000) row(s) starting at record # (90000).

You should now finally have the current version of Maxminds GeoIP country database installed on your production server. The database is now ready to be used.

How to use the GeoIP database
Create a new php include file with the following code, I named mine 'geofunctions.inc.php' appropriately for its purpose.

<?php
function getALLfromIP($addr,$db) {
global $Config;
// this sprintf() wrapper is needed, because the PHP long is signed by default
$ipnum = sprintf("%u", ip2long($addr));
$query = "SELECT cc, cn FROM ip NATURAL JOIN cc WHERE ${ipnum} BETWEEN start AND end";
$result = mysql_query($query, $Config["geolocationdb"]);

if((! $result) or mysql_numrows($result) < 1) {
//exit("mysql_query returned nothing: ".(mysql_error()?mysql_error():$query));
return false;
}
return mysql_fetch_array($result);
}

function getCCfromIP($addr) {
$data = getALLfromIP($addr);
if($data) return $data['cc'];
return false;
}

function getCOUNTRYfromIP($addr) {
$data = getALLfromIP($addr);
if($data) return $data['cn'];
return false;
}

function getCCfromNAME($name) {
$addr = gethostbyname($name);
return getCCfromIP($addr);
}

function getCOUNTRYfromNAME($name) {
$addr = gethostbyname($name);
return getCOUNTRYfromIP($addr);
}
?>

Within your PHP page where you wish to use geoip country functions you would simply make a call to which ever function you wish to use, see below for example.

<?php


// GEO LOCATIONS DATABASE CONNECTION
$Config["geolocationdb"] = mysql_connect("host", "username", "password");
mysql_select_db("geolocationdb", $Config["geolocationdb"]);

include('geofunctions.inc.php');

$remote_address = $_SERVER['REMOTE_ADDR'];

print "<p>".getCCfromIP($remote_address)."</p>\n";
print "<p>".getCOUNTRYfromIP($remote_address)."</p>\n";
?>

Further GeoIP Reading and External Links
If speed and efficiency are a concern it is helpul to understand the differences between tables InnoDB and MyISAM, where InnoDB is slower for searching but faster at updating than MyISAM.

About My Web Design

Lets think about the greatest artists of all time for example Pablo Picasso, Paul Cezzane, John Constable and Leonardo Da Vinci they where more than just great painters they where artists with passion and a strong sense of there own style. Painting to them wasn't about profit, they didn't wake up every morning and think "If I paint this by the end of today I will make twice as much money than if I finish it tomorrow". There intensions where about creating something unique, visual attractive and something more than just a wall filler.

When I design a website for someone I work with principles, I aim to please, I'm always thinking about producing a robust product that fulfills its purpose in every way. With my 7 year experience I have gained personal opinon about what I like and don't like, I have certain rules and criteria that each of my designs must follow, quite often it is a detail overlooked by the untrained eye and in many cases has taken me days longer to sort out than I first expected. If your a felow web designer you know already where I am going with this. At the advent of XHTML and CSS where separating design from content became a big thing for the better it wasn't without its problems and most web designers who use CSS will tell you its a real knightmare many have stuck with the old style due to the problems that occurs with the way different browsers render the content. CSS and XHTML altho simple languages, it is one of the hardest things to master and acheive the same visual output in all browsers. My design targets are always set to deliver the same results in all browsers while still producing something far more than just a website.

Web Site Design of Distinction was born today

Today is the birthday of Web Site Design of Distinction by Bartomedia.

I have official opened my own blogspot today to dedicate my commitment to producing Intuative and Advanced Website Designs and Systems that are fun to use with examples and tutorials on how to build and integrate them into your own site.

This blog has been a long time coming but like any other website designer will tell you there just arn't enough hours in the day.

My background experience, 7 years HTML, 5 years PHP and MySQL, 2 years Apache Mod-Rewriting, Javascript and CSS.

My development system of choice is base on LAMP Linux, Apache, MySQL and PHP.