Auto fill a field with next available number
Posted 11 October 2018 - 01:31 PM
I am trying to get a field to auto-populate based on a couple of criteria:
I have two fields that make up IP Address ranges, IE: range A= 10.10.10.1 - 10.10.10.20, Range B=10.10.10.21 - 10.10.10.30, Range C=10.10.10.31 - 10.10.10.40 and so on. What I am looking to do in auto-populate the IP field with the next available address in a given range based on the selection of the range.
Use case: I am creating a new record for range A. When I click 'Add' I would like the next available unused address in range 'A' to auto-populate the appropriate field.
The way I have my table is I that have a column called 'first_ip' and another called 'last_ip' and the one to auto-populate called 'ip'
I know that I will need to use INET_ATON to convert the IP addresses to a whole number, but I'm stuck on how to query against the 'ip' column and determine what hasn't been used yet and populate in the field.
Posted 11 October 2018 - 07:04 PM
Posted 12 October 2018 - 12:58 PM
I see what you're saying, however I'm working with existing data and they are not all contiguous, so that will leave gaps.
I have a query that will breakdown the IP's by ranges that they belong to:
FROM `hosts`, ``
WHERE INET_ATON(ip) BETWEEN INET_ATON(first_ip) AND INET_ATON(last_ip);
This works well. I'm trying to figure out how to add to it to pick a random IP from the 'ip' column based on the select statement. I hope this makes sense.