CODEWITHSUNDEEP
phpmysql
likith sai
likith sai

Reputation: 547

Ip address compare in database

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 i have a ip of 25.195.32.0 and i want to compare this to the from and to column and return the country name.

Upvotes: 8

Views: 1936

Answers (8)

Mark Miller
Mark Miller

Reputation: 7447

You can use INET_ATON to get a numeric value of the IPs to compare.

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

Upvotes: 13

Xing Fei
Xing Fei

Reputation: 287

Transform IP into integer, then compare integer is simple. MySQL offers the inet_aton function to do so.

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

For performance, you should transform column from, to to integer manully. eg. add to columns from_n, to_n. then your SQL will be like this:

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

If you are not using MySQL, you should transform $ip into integer $ip_n first (using something like Python's socket.inet_aton), then replace inet_aton($ip) with $ip_n.

Upvotes: 1

victorlopezsanchez
victorlopezsanchez

Reputation: 67

Select a range:

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

If you can use a script (eg php) you could select the entire table and make a mask to the IP. 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 us this script is 192.168.128.0, which is the network address to which the IP belongs 192.168.129.3/18.

Then, you can do a 'SELECT' of your network with "from" and "to" fields.

Upvotes: 1

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

INET_ATON gives desired solution on IPV4 addresses.

Apart from that, you can also try HEX version of the IP to compare in between.

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

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 |
+------------------------+---------------------+------------------------------+------------+

Upvotes: 1

Nikhil Agrawal
Nikhil Agrawal

Reputation: 285

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

Refer this link http://www.subnet-calculator.com/

Upvotes: 1

ioseph
ioseph

Reputation: 1917

select * from db.table

Then in php (assuming you've run the quest and stored 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.

Upvotes: 1

Rakesh Sharma
Rakesh Sharma

Reputation: 13728

You can find using IN() try condition according you use AND or OR

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

Upvotes: 1

ashishmaurya
ashishmaurya

Reputation: 1196

use this query 

select Country from table_name where from=to;

Upvotes: 0

Related Questions