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
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:
#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 (
#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.
#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.
#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...
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.
#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.
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:
#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.
#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.
Copyright © 2017 GeeksEngine.com. All Rights Reserved.
This website is hosted by HostGator.
No portion may be reproduced without my written permission. Software and hardware names mentioned on this site are registered trademarks of their respective companies. Should any right be infringed, it is totally unintentional. Drop me an email and I will promptly and gladly rectify it.