Comparing Ip addresses in the database

Question

I am trying to solve a problem that could compare 2 columns in a table. the table is as follows

------------------------------------------ | from | to | Country | ------------------------------------------ | 25.0.0.1 | 25.255.255.255 | denmark | ------------------------------------------ | 68.0.0.1 | 68.255.255.255 | USA | 

---

My problem is that I have ip 25.195.32.0 and I want to compare this with the from and to columns and return the country name .

Answer 1

You can use INET_ATON to get the numerical value of IP addresses for comparison.

 SELECT country FROM table WHERE INET_ATON('25.195.32.0') BETWEEN INET_ATON(from) AND INET_ATON(to) 

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

Answer 2

You can find with the IN() try condition according to your use of AND or OR

 WHERE from IN('your_ip') OR to IN('your_ip') 

Answer 3

Converting IP to an integer, and then comparing the integer prime. MySQL offers the inet_aton function for this.

 select Country from table_name where inet_aton($ip) between inet_aton(`from`) and inet_aton(`ip`); 

For performance, you must convert the from , to column to an integer. eg. add from_n , to_n . then your SQL will look like this:

 select Country from table_name where inet_aton($ip) between `from_n` and `to_n` 

If you are not using MySQL, you must first convert $ip to integer $ ip_n (using something like Python socket.inet_aton ) and then replace inet_aton($ip) with $ip_n .

Answer 4

 select * from db.table 

Then in php (if you started the quest and saved the results):

 $lookupIP; for($i=0;$i<$db->getRowCount();$i++) { $partsFROM = explode(".", $FROM); $partsTO = explode(".", $TO); $partsLOOKUP = explode(".",$lookupIP); if( $partsLOOKUP[0] >= $partsFROM[0] && $partsLOOKUP[0] <= $partsTO[0] && $partsLOOKUP[1] >= $partsFROM[1] && $partsLOOKUP[1] <= $partsTO[1] && etc.. ) return $Country } 

Not particularly efficient or elegant, but you get the idea.

Answer 5

As I understand from the question, you can use some mask and get the base IP address, and then compare it. To use the mask and get the base ip, you need to learn something about the different classes (A, B, C, D) used in the call.

Send link http://www.subnet-calculator.com/

Answer 6

INET_ATON gives the desired solution for IPV4 addresses.

In addition, you can also try the HEX version of IP to compare between them.

 SELECT HEX( input_ip_value ) BETWEEN HEX( from_ip_column ) AND HEX( to_ip_column ) 

An example :

 mysql> select @i:=hex('25.195.32.0'), -> @f:=hex('25.0.0.1'), -> @t:=hex('25.255.255.255'), -> @i between @f and @t is_between; +------------------------+---------------------+------------------------------+------------+ | @i:=hex('25.195.32.0') | @f:=hex('25.0.0.1') | @t:=hex('25.255.255.255') | is_between | +------------------------+---------------------+------------------------------+------------+ | 32352E3139352E33322E30 | 32352E302E302E31 | 32352E3235352E3235352E323535 | 1 | +------------------------+---------------------+------------------------------+------------+ 

Answer 7

Select range:

 SELECT Country FROM table_name WHERE ip_address >= from AND to <= ip_address 

If you can use a script (e.g. php), you can select the whole table and make an IP mask. Something like:

We can know what belongs to the network address 192.168.129.3/18 ip as follows:

 ip_en_binario = decbin (ip2long ("192.168.129.3")); mascara_en_binario = decbin (ip2long ("255.255.192.0")); resultado_en_binario $ = $ & $ mascara_en_binario ip_en_binario; miss long2ip (bindec ($ resultado_en_binario)); 

The result returned to us this script - 192.168.128.0, which is the network address to which the IP address 192.168.129.3/18 belongs.

Then you can “SELECT” your network with the “from” and “to” fields.

Answer 8

use this query

 select Country from table_name where from=to;