store ip address in mysql

Best way to store IP addresses in MySQL

It’s tempting to store IP addresses in a database as a VARCHAR(15) in the absence of a dedicated IP address field type in mysql, but that isn’t the most efficient way of doing so. The best way that I’ve come across to store an IPv4 address is to store it as an unsigned integer. In phpmyadmin you might set up an ip address field so that it looks something like:

Screenshot of the PHPMyAdmin field setup for an IP address field

You’ll realise I’m sure that you can’t just add the dotted IP address straight into an INT field without first converting it into a valid format. For that you’ll need a PHP function called which will convert a string containing an IP dotted address into a integer that can be stored in the INT field.
Here is a quick example of how you might go about getting the real IP address of a client and then storing and retrieving its value from the mysql DB:

1 2 3 4 5 6 7 8 9 10 11 12

//Test if it is a shared client if (!empty($_SERVER['HTTP_CLIENT_IP'])){   $ip=$_SERVER['HTTP_CLIENT_IP']; //Is it a proxy address }elseif (!empty($_SERVER['HTTP_X_FORWARDED_FOR'])){   $ip=$_SERVER['HTTP_X_FORWARDED_FOR']; }else{   $ip=$_SERVER['REMOTE_ADDR']; } //The value of $ip at this point would look something like: "192.0.34.166" $ip = ip2long($ip); //The $ip would now look something like: 1073732954

Now that you have the real IP address of the client converted to the INT format, you can write it into the DB as you normally would:

?

1 2	$sql = "INSERT INTO user(ip) VALUES('$ip')"; $dbQuery = mysql_query($sql,$dbLink);

To retrieve the original IP address from the database you can use the mysql

?

1	SELECT INET_NTOA(ip) FROM 'user' WHERE 1

Alternately you could use the PHP function longtoip to convert the returned INT value into the dotted IPv4 address in the PHP code instead, and you could even add the dotted IP address to the INT field in the db using the mysql funtion INET_ATON.
Storing IP addresses in this manner is beneficial because it takes less space than storing it as a string. The other benefit is that lookups are faster because integer comparisons are quicker than string comparisons.