[SOLVED] Storing a complete ip

[PJ droopy pants]

I am having issues with storing a new users complet ip address, it seems to be only storing the host ip, for instance when it should be storing an ip of 63.12.176.44 as 631217644, it is only storing 6312.

 

This is what I am using to capture the ip and i am storing it with BIGINT 50 in the database table.

 

    $result= mysql_query("INSERT INTO `users` (ip, username, password, email, signuptime, lastactive)". 
"VALUES ('".$_SERVER['REMOTE_ADDR']."', 
'$username', '$password', '$email', '$signuptime', '$signuptime')")

[Maq]

1) Why not store it as a string?

2) If you store it without the periods, how are you going to know where they go?

3) If you are going to use a BigInt, why don't you parse it for one first? (take out the periods)

[PFMaBiSmAd]

 

 

An IP address is not a number. It is a formatted string containing numeric characters and dots. You are attempting to treat it as a number and the number parsing stops at the second dot because it could parse everything up to that point as a number but the second dot served as a stop character for the number parsing.

 

Store an IP address in a character/string type.

[Maq]

I can't see the benefit of storing an IP as a integer...  Like I said before, it would be difficult (maybe impossible) to determine where the dots should be placed. 

 

for instance when it should be storing an ip of 63.12.176.44 as 631217644

 

What is the point?

[PFMaBiSmAd]

Also see -

http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_inet-aton

and

http://dev.mysql.com/doc/refman/5.1/en/miscellaneous-functions.html#function_inet-ntoa

[Mark Baker]

I can't see the benefit of storing an IP as a integer...  Like I said before, it would be difficult (maybe impossible) to determine where the dots should be placed. 

It's actually pretty easy, albeit storing as a string as a much better option.

 

However:

$ipAddressString = '192.165.27.54';
echo $ipAddressString;

function IP2Numeric($ipAddressString) {
$ipValueSet = explode('.',$ipAddressString);
$ipValueSet = array_reverse($ipValueSet);

$ipNumber = 0;
foreach($ipValueSet as $ipValue) {
	$ipNumber = $ipNumber * 256 + $ipValue;
}
return $ipNumber;
}

$ipNumber = IP2Numeric($ipAddressString);
echo '<hr />';
echo $ipNumber;

function Numeric2IP($ipNumber) {
$ipValueSet = array();
while ($ipNumber > 0) {
	$ipValue = $ipNumber % 256;
	$ipNumber = ($ipNumber - $ipValue) / 256;
	$ipValueSet[] = $ipValue;
}
return implode('.',$ipValueSet);
}

$ipAddressString = Numeric2IP($ipNumber);
echo '<hr />';
echo $ipAddressString;

 

[PFMaBiSmAd]

 

 

Or, just use the php built in functions -

 

ip2long and long2ip available since php4.

[Maq]

 

 

Or, just use the php built in functions -

 

ip2long and long2ip available since php4.

 

Cool.

 

@Mark Baker:

 

That's a neat little function, but again, what's the point?  Do you want to add IP's together or something?

[Mark Baker]

@Mark Baker:

 

That's a neat little function, but again, what's the point?  Do you want to add IP's together or something?

Absolutely no point whatsoever... I'd never those functions myself, and only ever store IP addresses as string values.

I simply wanted to demonstrate that it is really extremely easy to "determine where the dots should be placed".

 

 

EDIT

Although theoretically, if you did store the numeric value on a db, you could execute a query like:

$query = 'SELECT * FROM IPTable WHERE IPAddress BETWEEN '.IP2Numeric('192.135.0.0').' AND '.IP2Numeric('192.135.255.255');

 

[Daniel0]

IP addresses should be stored as an unsigned integer, not as a dotted string.

 

To hold the largest dotted string IP you would need at least 15 chars. However, an 32-bit integer is only 4 bytes. Moreover, comparing integers is much faster than comparing strings. Say you wanted all users that are in the range 192.168.0.0-192.168.0.255, then you would simply do:

SELECT username, ip FROM users WHERE ip BETWEEN INET_ATON('192.168.0.0') AND INET_ATON('192.168.0.255');

[corbin]

I think Daniel was hinting at this, but also, indexes on numeric data types are much smaller, and I believe faster (not sure about faster, but I think so).  (And yeah, they could be the same size as a char or text or varchar or what ever index, but in this situation, they would by far be smaller.)