[RESOLVED] Determine CIDR block that IP belongs to, amongst MANY possibilities

scrupul0us

Imagine you have 4 clients, each owns part(s) of an entire /24.

Due to the way the owner data is stored, when queried from the DB the results are:

id | cidr

where the cidr field is a varchar that holds csv in the format: a.b.c.d/cidr

So sample dataset:

24 | 192.168.0.0/29,192.168.0.8/29
78 | 192.168.0.16/28,192.168.0.32/28,192.168.0.48/28
94 | 192.168.0.64/26
103 | 192.168.0.128/25

So, what I need to do is find out what 'id' owns lets say 192.168.0.38

I've already written the complex query that returns the possible owners based on a.b.c, it's just refining those results that I'm kind of stumped on...

My brain almost thinks a matrix of some sort is needed, which makes me wish I paid more attention in my advanced math classes 😉

scrupul0us

http://snipplr.com/view/15557/cidr-class-for-ipv4/

Nuff said =)

bradgrafelman

Sounds like it would be a lot easier to change the IP ranges into integers (e.g. see [man]ip2long/man) and have one row for each range (which you should do anyway - DB Normalization 101 :p).

The, you'd simply do:

$sql = 'SELECT id FROM ranges WHERE ' . ip2long("192.168.0.38") . ' BETWEEN range_min AND range_max';