Monday, March 14, 2011

How to find IP locations using MySQL

Here we learn how to use MySQL for locating an IP address. At first we create a table that contains the name of the country and its IP address range. Then define a function to find country name of IP Address.

What's Ip Address?

Quoting from the "IP address" page in Wikipedia:

An Internet Protocol address (IP address) is a usually numerical label assigned to each device (e.g., computer, printer) participating in a computer network that uses the Internet Protocol for communication. An IP address serves two principal functions: host or network interface identification and location addressing. Its role has been characterized as follows: "A name indicates what we seek. An address indicates where it is. A route indicates how to get there."

Table (database)
I created the ip_location table to save countries and IP Address ranges. Enter the following command:
CREATE TABLE `ip_location` (
`from_ip` int(15) DEFAULT NULL,
`to_ip` int(15) DEFAULT NULL,
`country` varchar(32) DEFAULT NULL,
KEY `from_ip` (`from_ip`,`country`),
KEY `to_ip` (`to_ip`,`country`)

Then you need to import ip_location data. I exported my table with data via mysqldump. You should download it and use mysql command to restore it.
Download (165KB)

getIpCountry() function
Now you need to create the function to extract country name:
CREATE FUNCTION getIpCountry(ip varchar(15)) RETURNS varchar(64)
declare a tinyint unsigned;
declare b tinyint unsigned;
declare c tinyint unsigned;
declare d tinyint unsigned;
declare total bigint;
declare result varchar(64);
select substring_index(ip, '.', 1 ) into a;
select substring_index(substring_index(ip , '.', 2 ),'.',-1) into b;
select substring_index(substring_index(ip , '.', -2 ),'.',1) into c;
select substring_index(ip, '.', -1 ) into d;
set total := (a*256*256*256) + (b*256*256) + (c*256) + d;
select SQL_CACHE country into result from ip_location where total between from_ip and to_ip limit 1;
if (result is null) or (result = '') then
set result := 'unknown';
end if; 
return result;
And done! You just need to use select command in MySQL cli:
mysql> SELECT getIpCountry('');
| getIpCountry('') |
1 row in set (0.03 sec)

mysql> SELECT getIpCountry('');
| getIpCountry('') |
| UNITED STATES           |
1 row in set (0.00 sec)
Let me know if you have other way :)

No comments:

Post a Comment