Menu
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.
863
The easiest way to determine the range is to convert the IP into a decimal format and then do the ordinary comparing statements necessary. You can do this by assigning a weight for each part of the IP. Each part has a maximum value of 255. Thus, the best weight you can choose is 256.
We can store an IP address with the help of INT unsigned. While using INSERT, include INET_ATON() and with SELECT, include INET_NTOA(). IP address is in dotted format.
As with MS SQL Server you can store IP address, both IPv4 and IPv6, either as varchar or as numeric.
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
151
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
