PHP - find break in records

[scuffer]

Hi there

I don't plead for help very often but I'm a bit stuck with this one (my MySQL skills aren't the best!).

I've got a table with one of the fields being an IP address. What I want to do is flag/find the first unnasigned/unused IP address within a range. I'm really struggling with a way to achieve this, anyone got any ideas?

eg. IP_field in table:

record1: 10.128.142.1
record2: 10.128.142.2
record3: 10.128.142.4
record4: 10.128.142.5

The IP's would be in the range of 10.128.142.1 to 10.128.142.255 . In the above example I'd like to display the fact that 10.128.142.3 is the first unused IP address.

I'd really appreciate any help or a pointer in the right direction,

Cheers,
Dylan.

 

[Una]

I don't do SQL, but I presume you could just loop/recurse through each record and test it see if it follows on from the previous ip (presuming its sorted) and if it is not return the ip address of the previous + 1.

 

[Inquisitor]

Does it have to be done within the query, or can you use PHP as well?

If you can use PHP, then all you should have to do is select the IP addresses and iterate over them in PHP with a while loop and detect any gaps from there.

 

[arty]

Using a Numbers table with just one INT column, NumberID, consisting of the values 1 to 255, this might work:

SELECT
	'10.128.142.' + n.NumberID
FROM
	IP_table ipt
	RIGHT OUTER JOIN Numbers n ON RIGHT(ipt.IP_Field, INSTR('.', REVERSE(ipt.IP_Field)) - 1) = n.NumberID
WHERE ipt.IP_Field IS NULL

Not got MySQL on here so untested

arty

 

[scuffer]

Thanks for the pointers! I'll dig out my php book and look at at the interrupting a loop method. I really need to get stuck in more with the php coding

Arty, thanks for that sql code, I'll check it out at work tomorrow.

I'll get back to lurking and learning mode

Dylan