IP addresses get stored as 0 when converting with

问题:

The newsletter_ip field in MySQL is set as an UNSIGNED INT (10). I've also tried INET_ATON to format the data, but my results always look like this.

Here is part of my processing code:

//Retrieve data from user and create variables

$ip_orig = $_SERVER['REMOTE_ADDR'];

$ip = ip2long($ip_orig);

//Place into database

$sql = "INSERT INTO newsletter(newsletter_email, newsletter_ip, newsletter_date, newsletter_time) VALUES('".$email."', '".$ip."', '".$date."', '".$time."')";

I've also tried this snippet prior to the ip2long formatting, to no avail:

if (!empty($_SERVER['HTTP_CLIENT_IP'])){
    $ip=$_SERVER['HTTP_CLIENT_IP'];
}elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){
    $ip=$_SERVER['HTTP_X_FORWARDED_FOR'];
}else{
    $ip=$_SERVER['REMOTE_ADDR'];
}

Any help would be much appreciated, thanks!

回答1:

for the root cause of your problem, ip2long gives a signed int as the PHP manual states:

Note:

Because PHP's integer type is signed, and many IP addresses will result in negative integers on 32-bit architectures, you need to use the "%u" formatter of sprintf() or printf() to get the string representation of the unsigned IP address.

And you store it as an unsigned int, thats the reason why you see ony zeros. For a standard and clean solution handling IPv6 others have already given the solution.

回答2:

Since an IP address has several dots in it, or it may be a IPv6 address, I would suggest you set the newsletter_ip field as a VARCHAR

Right now your IP addresses may be showing up as 0 because they are not actually integers.

回答3:

Does printing out the ip before storing give you the correct value? If not, check if a proxy exists which removes this information from the request or renames it.

Anyways, there is no gain in storing the ip as long. There should not be a notable loss in performance if you use a string for this field which is much more straight forward and easier to handle. And while you're at it, leave enough space for IPv6 ;-)

回答4:

How about letting MySQL translate the IP using INET_ATON on the server side ?

//Retrieve data from user and create variables

$ip_orig = $_SERVER['REMOTE_ADDR'];

//Place into database

$sql = "INSERT INTO newsletter(newsletter_email, newsletter_ip, newsletter_date, newsletter_time) VALUES('".$email."', INET_ATON('".$ip."'), '".$date."', '".$time."')";

回答5:

Use varchar(15) as min... varchar(45) to support newer IPv6 addresses, also here is my IP function:

function ip()
{
    if (!empty($_SERVER['HTTP_CLIENT_IP']))
    {
        $ip = $_SERVER['HTTP_CLIENT_IP'];
    } elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR']))
    {
        $ip = $_SERVER['HTTP_X_FORWARDED_FOR'];
    }
    else
    {
        $ip = $_SERVER['REMOTE_ADDR'];
    }
    return $ip;
}

回答6:

You are storing the long as a string, not as a long.

You are also better off making the conversion in MySQL itself:

$sql = "INSERT INTO newsletter(newsletter_email, newsletter_ip, newsletter_date, newsletter_time) VALUES ('".$email."', INET_ATON('".$ip."'), '".$date."', '".$time."')";

To retreive the IP address, do:

SELECT INET_NTOA(newsletter_ip) as newsletter_ip FROM newsletter;

INET_ATON = Address to number

INET_NTOA = Number to Address