How to load IP addresses for countries into your database

How to load IP addresses for countries into your MySQL database

If you are a website owner or webmaster, often times you need to know which country a specific IP belongs to. For an individual IP, you can check it's details on the internet. There are many free services available on the net. But it's sometimes handy if you have all the IP country information hosted within your own database table. This article will talk about how to load IP-country data into your own MySQL database table on your website.

Problem to solve:

As IP country database normally has more than 100,000 records, it's a hassle to upload this large amount of data if your website uses a shared web hosting service. Using phpMyAdmin is not hassle-free when dealing with a SQL file that is a few Megabyte in size. In this article, I show you an easy way (if you are patient enough to complete all steps) to load large amount of data into your own MySQL database in your shared hosting account.

IP data can be purchased on the internet. One popular vendor is IP2Location.com which sells IP addresses data at various levels of details.

The zip file you downloaded from IP2Location.com has IP addresses in formats for different database systems, including MS Access, MySQL dump, generic SQL inserts that can be used for Oracle, SQL Server, etc. IPCountry.csv, etc.

All Country Codes in the IP2Location follows the ISO-3166 definition except using "UK" for Great Britain.

Files included in Distribution Package:

README.TXT                  - This ReadMe File
LICENSE.TXT                 - End-User License Agreement
_SPECIFICATION.PDF - Product Specification in PDF Format
README-.HTML       - Product FAQS in HTML Format
IPCountry.CSV               - Product Database in CSV Format
IPCountry.MDB               - Product Database in Access Format
IPCountry.mysql.dump        - Product Database in MySQL Dump Format
Satellite.csv               - List of IP Address Range for Satellite
                              ISP Providers
AOL.csv                     - List of IP Address Range for AOL

How to load IPs to the database on your website

Here we are going to look at how to load IP-Country data into your own MySQL table - ipcountry.

#1. Purchase your IP-COUNTRY database from IP2Location.com

After you download IP-COUNTRY database, unzip it and you will see there are a few files in it. Each one targets different platform. By default, you should use IPCountry.mysql.dump but we won't because this file doesn't use bulk insert statements for fast insert so we decide to build our own.

Here is how the IP-COUNTRY table looks like:

Here is the table structure of IP-COUNTRY:

Field Number	Field Name	Field Size	Field Description
1	IP_FROM	numerical	First IP address in Netblock.
2	IP_TO	numerical	Last IP address in Netblock.
3	COUNTRY_CODE	char(2)	Two-character country code based on ISO 3166.
4	COUNTRY_NAME	vchar(64)	Country name based on ISO 3166.

#2. Create ipcountry table in your local MySQL database.

Open SQLyog. Either create a new database or use an existing database. Create a table by using the following structure:

CREATE TABLE ipcountry (
ipFROM INT(10) UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
ipTO INT(10) UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
countrySHORT char(2) NOT NULL default ' ',
countryLONG varchar(255) NOT NULL default ' ',
PRIMARY KEY (ipFROM,ipTO)
) TYPE=MyISAM;

#3. Import country IPs to ipcountry table.

Within the unzipped files, find IPCountry.csv and use it as the source for import.

Right click on the ipcountry table name in SQLyog and then in pop-up menu, go to Import and click Import CSV Data Using LOAD LOCAL.

Import IP-Country by using CSV file

#4. Specify the delimiters.

We need to change the default delimiters according to the delimiters used for fields and lines in IPCountry.csv file.

See screenshot below for the delimiters. After done, click Import button to import it.

Specify IP-Country delimiters

#5. Verify the data imported.

Check how many records have been imported and compare it with the number of records in IPCountry.csv. You can open IPCountry.csv in Microsoft Excel to see how many records in it.

#6. Export ipcountry table as SQL of bulk insert statement.

Bulk insert allows for fastest insert of multiple rows in one SQL statement in a format like below:

INSERT INTO x(a,b) VALUES('1', 'one'),('2', 'two'),('3', 'three')

Right click on the ipcountry table name in SQLyog and then in pop-up menu, go to Backup/Export and then click Backup Table(s) As SQL Dump...

Export ipcountry table as SQL Dump

When the SQL Dump window opens, select Data only and tick Create bulk INSERT statements checkbox. Then specify a location to save the exported file (ip.sql) and then click Export button.

Export ipcountry table as bulk insert statements

#7. Clean up ip.sql file.

There are some comments in ip.sql file that we want to remove and only leave bulk insert statements in it.

Below is how the ip.sql file looks when we have comments in it. Note that SQLyog automatically splits the records into 8 bulk insert statements. This is a very nice feature because if you keep all records in one bulk insert statements, it will exceed the Max Allowed Packet size in MySQL.

ip.sql file with comments

Remove all comments within ip.sql and only leave bulk insert statements in it without line space in between each bulk insert statement. See below after the clean-up:

ip.sql file without comments

#8. Create a PHP file to load the data on your website.

Because we want to load the IP-COUNTRY data into your MySQL table for your website, we will create a PHP page to do that.

Assume you already created the ipcountry table in your website database.

I have created this PHP page and shown below. You need to change the database settings within the file.

Basically, the PHP page performs a few steps to load all the records in ip.sql into your ipcountry table.

Open ip.sql file
Read its content into a string variable.
Split the string variable by using a semi-colon into an array.
Loop through the array and process each element. Each element is a bulk insert statement.
For each element, execute the bulk insert statement by PHP.
Do a final audit to check if all records are inserted successfully.


<html>

<head>

</head>

<body>



<?

define('DB_HOST', 'localhost');

define('DB_NAME', 'your_db_name_here');

define('DB_USER', 'your_db_user_here');

define('DB_PASS', 'your_db_password_here');

    

$link = mysql_connect(DB_HOST, DB_USER, DB_PASS) 

    or die("Could not connect to database"); 



mysql_select_db(DB_NAME, $link) or die("Could not select database"); 



// Truncate ipcountry table

$result = mysql_query("truncate table ipcountry;");

$mysql_error = mysql_error();

            

if (!$result) // failed

{

    echo "Truncating ipcountry table failed.";

    echo "<p>Error occurred: ". $mysql_error;

}                

else

{

    echo "Successfully truncating ipcountry table.";

}

echo "<hr>";



// Open ip.sql file to read its content

$ipfile = "ip.sql";

$fh = fopen($ipfile, 'r');

$sql = fread($fh, filesize($ipfile));

fclose($fh);



// Split the sql into array

$arr = explode(";", $sql);



// Go thru each element to IPs to ipcountry table. 

// Note that each element in the array is a bulk insert statement.

for($i = 0; $i < count($arr); $i++)

{

    $sql = trim($arr[$i]);



    if ($sql != "")

    {

        $result = mysql_query($sql);

        $mysql_error = mysql_error();

        $inserted_rows = mysql_affected_rows();



        if (!$result) // failed

        {

            echo "Failed. sql line: ". ($i+1);

            echo "<p>Error occurred: ". $mysql_error;

        }                

        else

        {

            echo "Successful. sql line: ". ($i+1). ". Rows inserted: ". $inserted_rows;

        }

        $total_inserted_rows = $total_inserted_rows + $inserted_rows;

        echo "<hr>";    

    }    

}

 

// Do some auditing

$result = mysql_query("select count(*) from ipcountry;");

$total_rows_found = mysql_result($result, 0, 0);



if ($total_rows_found == $total_inserted_rows)

{

  echo "<span style='color:green;'>Overall it was a success for table ipcountry. ".

       "<br>total_inserted_rows: ". $total_inserted_rows. 

       "<br>total_rows_found: ". $total_rows_found. "<span>";

}

else

{

  echo "<span style='color:red;'>Overall it was failed for table ipcountry. ".

       "<br>total_inserted_rows: ". $total_inserted_rows. 

       "<br>total_rows_found: ". $total_rows_found. "<span>";

}

?>

</body>

</html>

#8. Upload both ip.sql and load_ip.php to a folder on your site.

Make sure you keep the two file within the same folder on your site. Then open up your web browser and open load_ip.php page. If all executed successfully, you will see success message in green text, or if failed, error message in red text.

All IP country records successfully inserted

Other Recent Articles from the Webmaster Help category:

1.	How to set up your website connection details in FileZilla
2.	The Difference Between Dynamic URLs and Static URLs
3.	How To Find Out Everything You Want To Know About A Website
4.	Robots Meta HTML Tag Syntax Explained
5.	What If You Don't Want Your Pages To Be Crawled and Cached by Search Engines
6.	What Robots.txt is And Search Engine Robots Explained
7.	How to Tweak HTML Table To Speed Up Page Load Time

	Custom Search