Monday, March 14, 2011

How to find IP locations using MySQL

Introduction
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`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC;

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 ip_location.sql.zip (165KB)

getIpCountry() function
Now you need to create the function to extract country name:
DELIMITER $$
CREATE FUNCTION getIpCountry(ip varchar(15)) RETURNS varchar(64)
BEGIN
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;
END$$
DELIMITER ;
And done! You just need to use select command in MySQL cli:
mysql> SELECT getIpCountry('79.175.165.171');
+--------------------------------+
| getIpCountry('79.175.165.171') |
+--------------------------------+
| IRAN (ISLAMIC REPUBLIC OF)     |
+--------------------------------+
1 row in set (0.03 sec)

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

No comments:

Post a Comment