IP address is stored as 0 in SQL using PHP

Question

I am locally designing a website and I am testing a function within PHP that captures and stored the ip address of the client however I am simply getting a value of 0. The code is:

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'];
}

The ip is then added to the mysql and stored as an integer using the code:

   $query = "INSERT INTO users (id, email, salt, passhash, regdate, last_login_date,      regip, last_login_ip) VALUES ( NULL, ?, ?, ?, NOW(), NOW(), ?, ?)";

    $stmt = mysqli_prepare($dbc, $query);


    //i Interger
    //d Doubles         
    //s Everything Else


    mysqli_stmt_bind_param($stmt, "sssii", $email, $salt, $passhash, $ip, $ip);

Would the reason that the value is only 0 because I am running the website locally, or am I missing a step?

Thanks :)

You might consider storing IPs as strings to enable future compatibility with IPv6 addresses. See [this question](http://stackoverflow.com/questions/166132/maximum-length-of-the-textual-representation-of-an-ipv6-address) for more info about storing IPv6 addresses. — Glen, Dec 12 '14 at 21:03

score 1 · Accepted Answer · answered Dec 12 '14 at 20:48

1

You shouldn't store your ip address in an unsigned integer field. Store it in an varchar format and it should fix your problem. Integer is expecting a normal number, not something with multiple . in it.

answered Dec 12 '14 at 20:48

Grice

1,345
11
24

I read in a tutorial: All IP addresses should be stored in integer form. So instead of, say, "194.247.44.146", you would be storing 194*256^3 + 247*256^2 + 44 * 256^1 + 146, which is 3270978706. PHP has native functions that convert between human-readable-IPs and integer IPs, don't worry :) They're ip2long() and long2ip. As it saves space. The highest possible IP address is 255.255.255.255. Convert that and you get 4294967295, which is the maximum value for an unsigned int. Hence, ip fields are unsigned ints. – dominicansell Dec 12 '14 at 20:49
@dominicansell I see, usually when people indicate there storing IP addresses, it means there just storing it in the original format. I didn't realize you are altering it before storing. You should probably update the question with that information. – Grice Dec 12 '14 at 20:52
@dominicansell What database are you using? Also, is the value of `$ip` 0 before attempting to store in the db? – Grice Dec 12 '14 at 20:55
I am using SQL, I don't mind how I store the IP, just as long as the IP is stored in the database :) I don't set a original value to ip as I have set the column to be NULL to begin with. I have altered the columns to be varchar(45) and I am am attempting to input the ip as a string but I get the result ::1 in the table. – dominicansell Dec 12 '14 at 21:01
@dominicansell `::1` is the [localhost](http://en.wikipedia.org/wiki/Localhost) address for IPv6. It's the equivalent of `127.0.0.1` in IPv4. Both formats are valid IP addresses. Note: `ip2long` and `long2ip` both won't work with IPv6 addresses as they expect them in IPv4 format. – Grice Dec 12 '14 at 21:03
So my code is working properly and when I launch the website online I will get actual IP adresses? – dominicansell Dec 12 '14 at 21:09

IP address is stored as 0 in SQL using PHP

1 Answers1