Find ip from ip2location_db11 database

debugcn Published at Dev

MaxDome

i use ip2location_db11.CSV database to find ips detailed. its create a table like this:

CREATE TABLE `ip2location_db11`(
`ip_from` INT(10) UNSIGNED,
`ip_to` INT(10) UNSIGNED,
`country_code` CHAR(2),
`country_name` VARCHAR(64),
`region_name` VARCHAR(128),
`city_name` VARCHAR(128),
`latitude` DOUBLE,
`longitude` DOUBLE,
`zip_code` VARCHAR(30),
`time_zone` VARCHAR(8),
INDEX `idx_ip_from` (`ip_from`),
INDEX `idx_ip_to` (`ip_to`),
INDEX `idx_ip_from_to` (`ip_from`, `ip_to`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin;

and with this details:

"16777216","16777471","AU","Australia","Queensland","Brisbane","-27.467940","153.028090","4000","+10:00"

cuz large list of ips all ip located with ip range in 2 columns (ip_from , ip_to)

how to find my ip from this table? i know this:

    $result = mysql_query("SELECT *
                      FROM ip2location_db11
                      WHERE ip = {$_SERVER['REMOTE_ADDR']}
                      LIMIT 1") or die(mysql_error());

    $row = mysql_fetch_assoc($result);

But how to find my ips from this ip range in columns (ip_from , ip_to)

Professor Abronsius

Looking at the example data and judging by the column type for ip I would say that the ip has been transformed to an int using ip2long() ~ fine for ipv4 addresses, not so good for ipv6 I believe. So, your sql will need to account for the ip2long conversion and there is a little gotcha with large numbers in php so I'd suggest using sprintf also.

$ip = sprintf( '%u', ip2long( $_SERVER['REMOTE_ADDR'] ) );
$sql= 'select * from `ip2location_db11` where `ip_from` >= '.$ip.' and `ip_to` <= '.$ip.' limit 1;';

Collected from the Internet

Please contact [email protected] to delete if infringement.