Auto Increment IP Address in Mysql DB

[recsx]

Hi everyone,

Hate to be a beggar, but

I've been searching around and calculating ideas but have come up
with nothing.

Has anyone got any ideas on how you would auto increment an IP address in a MySQL database, so that the user will never make a mistake and not have to look though a large list to see what IP is next in line to be used?

Thanks

[ergophobe]

Please, be a beggar. If people don't ask questions, it's not much of a forum.

Some questions first. How complex is this supposed to be? Do you have a list of valid IPs from which your users should be selecting? In other words, what follows 167.0.31.45? Is it the next in the numerical series (167.0.31.46) or the next available IP in your block?

Tom

[digitalv]

You should be storing your IP addresses as LONG in the database instead of dotted. Aside from the advantage of being able to query the data faster (numbers always query faster than text), you can accomplish what you're trying to do just by adding +1 to the highest number in the database.

Assume A.B.C.D then to get the IP Number you would use this formula: A x (256*256*256) + B x (256*256) + C x 256 + D

So if your IP address was: 123.123.123.123 then the IP number would be 2071690107.

123.123.123.123 = 2071690107
123.123.123.124 = 2071690108
123.123.123.125 = 2071690109
etc...

Then use the PHP function Long2ip to convert it back to dotted (if necessary) when you need to read it that way. [us3.php.net...]

[coopster]

Also remember that in every IP subnet there will be a network address and a broadcast address. Using digitalv's example, the network address would be 123.123.123.0 and the broadcast would be 123.123.123.255. You can't use those two addresses for hosts. Therefore you are left with 123.123.123.1 through 123.123.123.254 to use for hosts.

This may be where ergophobe was headed. It doesn't matter how you subnet your network you'll have to avoid using the network address or the broadcast address for any hosts. I think you are going to have to perform a quick check within your PHP incremental loop to tell you if you are on target or not, and handle accordingly. Pseudocode...

if ($octet4 == 255) { 
 $octet3++; 
 $octet4=1; 
} 
if ($octet3 == 255) { 
etc.

[recsx]

Thanks a mil guy's and gals
This was alot of help.
Here is what i made up based on all your suggestions.

// Recombine IP Address fields as a full IP Address e.g. x.x.x.x
$ip_address = $_POST["ip_octet1"].".".$_POST["ip_octet2"].".".$_POST["ip_octet3"].".".$_POST["ip_octet4"];
// Recombine IP Subnet fields as a full Subnet Address e.g. x.x.x.x
$subnet_address = $_POST["subnet_octet1"].".".$_POST["subnet_octet2"].".".$_POST["subnet_octet3"].".".$_POST["subnet_octet4"];
// Convert IP Address to a LONG proper Address
$ip_long = ip2long($ip_address);
// Convert Subnet Address to a LONG proper Address
$subnet_long = ip2long($subnet_address);
// Query for Used IP's
$ip_query = mysql_query("SELECT ip FROM radios", $mysql_ID) or die (mysql_error());
// Search through query for used IP's
for($i = 0; $i < mysql_num_rows($ip_query); $i++){
$ipArray=mysql_fetch_array($ip_query);
$my_ip[$i] = $ipArray[0];
if($my_ip[$i] == $ip_long){
print "IP: <B>$ip_address</B> is In Use<BR>";
}}
// Check for Mac Address Format
$macID_num = "$macID";
if(ereg("^[a-f0-9]{12,12}$", $macID_num)) {
}else{
$mac_error = "<B>$macID_num</B> is invalid<BR>";
}
// Check for Invalid IP Address format
if ($ip_long === -1) {
$ip_error = "<B>$ip_address</B> is an Invalid IP, please try again<BR>";
}
// Check for Invalid Subnet Address format
if ($subnet_long === -1) {
$subnet_error = "<B>$subnet_address</B> is an Invalid Subnet, please try again<BR>";
}
// Check if any Errors on IP, Subnet and Used IP, if not insert new radio
if(($mac_error)¦¦($ip_long === -1)¦¦($subnet_long === -1)¦¦($ip_in_use_error)){
echo $mac_error.$ip_error.$ip_in_use_error.$subnet_error;
}else{
mysql_query("INSERT INTO radios(
 clientID,
 macID,
 vendor,
 model,
 ip,
 subnet,
 notes
 )
VALUES (
 'x',
 '$macID',
 '$vendor',
 '$model',
 '$ip_long',
 '$subnet_long',
 '$notes'
 )",$mysql_ID) or die (mysql_error());
 print "Thank you Radio Added";
 echo "<META http-equiv=\"refresh\" content=\"5;URL=radio_management.php\">";
}}