views:

137

answers:

1

I have a MySQL table setup as follows:

+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| ipaddress_s   | varchar(15) | YES  | MUL | NULL    |                | 
| ipaddress_e   | varchar(16) | YES  |     | NULL    |                | 
+---------------+-------------+------+-----+---------+----------------+

where, ipaddress_s and ipaddress_e look something like: 4.100.159.0-4.100.159.255

Now is there a way I can actually get the row that contains a given IP address? For instance, given the IP address: "4.100.159.5", I want the above row to be returned. So I am trying for a query that looks something like this (but of course this is wrong because in the following I am considering IPs as strings):

SELECT * FROM ranges WHERE ipaddress_s<"4.100.159.5" AND ipaddress_e>"4.100.159.5"

Any suggestions?

+5  A: 

INET_ATON(expr) will change an ip address string into a numeric address that you can use for comparison.

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

SELECT * FROM ranges WHERE INET_ATON(ipaddress_s)<INET_ATON('4.100.159.0') AND INET_ATON(ipaddress_e)>INET_ATON('4.100.159.5')
WhirlWind
Thanks! Didn't even know those functions existed!
Legend
Yup... if performance is an issue, you might store the addresses in the database as integers in the first place.
WhirlWind
Yeah.. In the process of dropping this table and creating a new one :)
Legend
See INET_NTOA() for convenience when you get that far =)
WhirlWind
Using both the functions extensively :) You simplified my life essentially... Thanks once again...!
Legend