IP2Location Database IPv6 conversion to IP Number Decimal

[brentman]

I just downloaded and installed IP2Location database and everything is smooth for IPv4 addresses. (If you want to get location from peoples IP the free version is here: http://lite.ip2location.com/database-ip-country-region-city-latitude-longitude-zipcode-timezone) You convert the IPv4 address to an IP number and then do a database query. See here:

$ip_split = split ("\.", "$ip");
$ip_num = $ip_split[3] + ($ip_split[2] * 256) + ($ip_split[1] * 256 * 256) + ($ip_split[0] * 256 * 256 * 256);

$result = mysql_query("SELECT * FROM ip2location_db11 WHERE '$ip_num' BETWEEN ip_from AND ip_to");

However I now need to do this for an IPv6 address but there is no documentation or anything I can find Googleing about how to do this for IPv6. Obviously the same method will not work as it has letters in the address as well as more numbers. There is obviously some difference to it. Anyone know what to do?

 

Thanks!

[Barand]

You may find it useful to look at MySQLs INET_* and INET6_* functions

 

http://dev.mysql.com/doc/refman/5.6/en/miscellaneous-functions.html#function_inet-aton

[brentman]

I don't think those will help unless I am just not understanding them that well.

[Barand]

mysql> SELECT INET_ATON('199.119.180.52') as ip_num;
+------------+
| ip_num     |
+------------+
| 3346510900 |
+------------+

Therefore you can

$ip_add = '199.119.180.52';

$sql = "SELECT * 
        FROM ip2location_db11 
        WHERE INET_ATON('$ip_add') BETWEEN ip_from AND ip_to";

[requinix]

Note that it seems there is a separate database/table for IPv4 and IPv6 addresses. Same query, different table.

[brentman]

I tried this and it returned empty.

$ip_test = '2607:f0d0:1002:51::4';
$result = mysql_query("SELECT * FROM ip2location_db11_ipv6 WHERE INET_ATON('$ip_test') BETWEEN ip_from AND ip_to");
$location_record = mysql_fetch_array($result);

[brentman]

Found the answer but new issue now. Here is the code:

$ipv6 = '2404:6800:4001:805::1006';
$int = inet_pton($ipv6);
$bits = 15;

$ipv6long = 0;

while($bits >= 0){
	$bin = sprintf("%08b", (ord($int[$bits])));

	if($ipv6long){
		$ipv6long = $bin . $ipv6long;
	}
	else{
		$ipv6long = $bin;
	}
	$bits--;
}

$ipv6long = gmp_strval(gmp_init($ipv6long, 2), 10);

Now the only problem is I do not have math functions installed. I would prefer not to install them. Is there an alternate for the two gmp functions?

[Barand]

 

I tried this and it returned empty.

$ip_test = '2607:f0d0:1002:51::4';
$result = mysql_query("SELECT * FROM ip2location_db11_ipv6 WHERE INET_ATON('$ip_test') BETWEEN ip_from AND ip_to");
$location_record = mysql_fetch_array($result);

 

  If you are working with ip6 then you need INET6_ATON() . This function requires MySQL 5.6+

[junejune]

The latest CSV now comes with both IPv4 and IPv6 and the IPv4 is embedded as IPv4-mapped IPv6 addresses. So querying IPv4, you need to convert the IP to IPv4-mapped IPv6.

[brentman]

Barand, thanks. I was able to update to MySQL 5.6 and then get the code to work as you posted in your last response. Perfect timing for the new release!