[SOLVED]Convert IPv4 to number and back for storage in MySQL
Posted: Thu Nov 12, 2009 8:19 am
Hi everyone
Up until now I've been storing IP addresses in my database as a string (VARCHAR) just in the format 192.168.78.49 or whatever
However after a bit of reading it seems it's better to be storing them as integers
So I've done a bit of digging and came up with the following functions to convert to/from a dotted quad
1) Are those functions going to be correct for all IP address ranges?
I've tried manually with values of 0.0.0.0 and 255.255.255.255 and the conversion seems to work.
2) My conversion of 255.255.255.255 returns 18446744073709551615
How would I go about storing a number like this in MySQL? Guessing INT won't work since its out of the range... so will UNSIGNED BIGINT (http://dev.mysql.com/doc/refman/5.0/en/ ... types.html) do the job?
3) I'm guessing its no co-incidence that 255.255.255.255 returns 18446744073709551615 and the max value of UNSIGNED BIGINT is also 18446744073709551615
4) I was alerted to what Nick Critten says in the comments on this page http://www.justin-cook.com/wp/2006/11/2 ... and-vbnet/
I tried this
And the conversion wasn't lossless. Should I just use my original functions above?
I just want to make sure I get this right before I start ploughing loads of IP addresses into a DB
Cheers, B
Up until now I've been storing IP addresses in my database as a string (VARCHAR) just in the format 192.168.78.49 or whatever
However after a bit of reading it seems it's better to be storing them as integers
So I've done a bit of digging and came up with the following functions to convert to/from a dotted quad
Code: Select all
// GET REAL IP ADDRESS
function get_real_ip() {
if( !empty($_SERVER['HTTP_CLIENT_IP'])) {
$ip = $_SERVER['HTTP_CLIENT_IP']; // share internet
} elseif( !empty($_SERVER['HTTP_X_FORWARDED_FOR'])) {
$ip = $_SERVER['HTTP_X_FORWARDED_FOR']; // pass from proxy
} else {
$ip = $_SERVER['REMOTE_ADDR'];
}
return $ip;
}
$ip = get_real_ip();
echo $ip_numeric = sprintf('%u', ip2long($ip)).'<br />';
echo $ip_string = long2ip($ip_numeric);I've tried manually with values of 0.0.0.0 and 255.255.255.255 and the conversion seems to work.
2) My conversion of 255.255.255.255 returns 18446744073709551615
How would I go about storing a number like this in MySQL? Guessing INT won't work since its out of the range... so will UNSIGNED BIGINT (http://dev.mysql.com/doc/refman/5.0/en/ ... types.html) do the job?
3) I'm guessing its no co-incidence that 255.255.255.255 returns 18446744073709551615 and the max value of UNSIGNED BIGINT is also 18446744073709551615
4) I was alerted to what Nick Critten says in the comments on this page http://www.justin-cook.com/wp/2006/11/2 ... and-vbnet/
I tried this
Code: Select all
echo $ip_numeric = floatval(sprintf("%u",ip2long('$ip')));
echo $ip_string = long2ip($ip_numeric);I just want to make sure I get this right before I start ploughing loads of IP addresses into a DB
Cheers, B