SQL / PHP problem?

Question

suppose that i have 2 tables

ip_masks
1.1.1
2.2.2
3.3.3

and

full_ip
1.1.1.1
2.2.2.2
4.4.4.4

How can i select from full_ip only those rows which's begining is in ip_mask ?

In our example, it will return only the rows 1.1.1.1 and 2.2.2.2

Answer 1

Well...you could kind of fudge it a little using something like:

select *
from full_ip
where exists
(select 1
from ip_masks
where substr(full_ip.ip, 1, 5) = ip_masks.mask)

But this won't work when the IPs are not single digit. Also IP masks are not always only 3 parts....read up here: http://en.wikipedia.org/wiki/IP_Subnet_Mask

There are two better ways to do what you're trying to do.

Method number 1:

Store the IP masks and the IP numbers as a single 32-bit integer.

So 128.6.4.8 would become 128 × 32³ + 6 × 32² + 4 × 32¹ + 8 = 4,200,584.

And an IP mask of 128.0.0.0 would become 128 × 32³ = 4,194,304

Then you could find matching records by using:

select full_ip.ip
from full_ip
join ip_mask on
(full_ip.ip & 0xFFFFFF00) = (ip_mask.mask & 0xFFFFFF00)

Advantage: you store everything in only one column, saving space.

Disadvantage: the addresses are not human-readable.

Method number 2: (Better, IMHO)

Store each IP address and mask as 4 different columns and do the same kind of thing.

select ip_1 + '.' + ip_2 + '.' + ip_3 + '.' + ip_4
from full_ip
join ip_mask on
ip_1 = mask_1 and
ip_2 = mask_2 and
ip_3 = mask_3

It's also easier to see the IP addresses in the database if you break them up like this.

If you don't understand any of this, please feel free to email me.

Answer 2

a third selection is to do it like this. INSERT INTO cms_access_levels (access_level, access_name) VALUES (2,'Moderator'), (3,'Administrator'), (ninety 9,'surprising get entry to'); the place you may specify diverse value instruments etc.