Best way to store IP addresses in MySQL

By David Pratt / Tags: , / 24 Comments / Published: 10-08-10

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

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 ip2long 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:

//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:

$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 function INET_NTOA like so:

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.

Category: Tech

Tags: ,

Posted: on August 10th, 2010 at 10:53 am.

Feeds: RSS 2.0

24 Responses to “Best way to store IP addresses in MySQL”

Mike August 25th, 2010 at 2:59 am

Thanks for this, found it really helpful :)

Mike October 9th, 2010 at 9:07 pm

Thanks! I, like the other Mike, found this to be very helpful.

Molozonide October 31st, 2010 at 8:44 pm

Thanks! I found this helpful as well.

Mike November 18th, 2010 at 11:40 am

Keep in mind that HTTP_X_FORWARDED_FOR can return multiple IP addresses.

Dave December 1st, 2010 at 11:14 pm

But say you wanted to store the ip in the original format, reasoning’you are only expecting about 10 visitors, you want to make sure they are all genuine’

I am trying to tie ip collection up with a form purely for evidence that I have not falsified anything, I have used the following

$ip = $_SERVER['REMOTE_ADDR'];
$hostaddress = gethostbyaddr($ip);

print “Display IP address:\n”;
print “$ip\n”;

But I cannot seem to get it to write to the Varchar field in my dbase, could you advise?

Thanks in advance.

Dave December 1st, 2010 at 11:16 pm

My complete script is

<?php
// connection to MySQL
require ("connect.php"); // if this script is unavailable then the rest of the code is pointless as need a connection to the database.

//include ("ipcollection.php");
$ip = $_SERVER['REMOTE_ADDR'];
$hostaddress = gethostbyaddr($ip);

print "Display IP address:\n”;
print “$ip\n”;

// Setting variables

$ip = $_POST['ip'];
$haveemail = $_POST['haveemail'];
$emailaddress = $_POST['emailaddress'];
$browser = $_POST['browser'];
$otherbrowser = $_POST['otherbrowser'];
$resolution = $_POST['resolution'];
$otherresolution = $_POST['otherresolution'];

// insert data to dbase
$query=”INSERT INTO datacollection1 (id, ip, haveemail, emailaddress, browser, otherbrowser, resolution, otherresolution) VALUES (‘Null’, ‘$ip’, ‘$haveemail’, ‘$emailaddress’, ‘$browser’, ‘$otherbrowser’, ‘$resolution’, ‘$otherresolution’)”;

// Null is in the id field as his is added automatically by the database.

// message to say if database has been updated

mysql_query($query) or die (mysql_error());

echo “The database has just been updated with the following information: “;

?>

Thanks in advance for any advice

mua sam vui April 7th, 2011 at 6:42 pm

so cool ! i am finding a solution to slove the problem that the ip too long and can not save to database using INT

Herb July 30th, 2011 at 12:47 am

Thank you muchly. I was having trouble getting the right format. THis helps heaps.

Julia August 8th, 2011 at 8:31 pm

Great thanks David for such good and useful article! Can you suggest tip for converting field from varchar(15) to integer by SQL query?

Muhammed Ali September 4th, 2011 at 11:16 am

Thanks… it is very help full for me…….

Melanie September 8th, 2011 at 10:22 pm

Julia I would check the following link:
http://lists.mysql.com/cluster/2784

If you scroll to the bottom I believe they show an example of how to convert from int to the standard ip format in a mysql query.

Craig Schultz February 13th, 2012 at 9:22 pm

It’s probably easier just to use MySQL’s built in functions to handle IP addresses, INET_ATON() to convert an IP address to an unsigned int and INET_NTOA to convert back again.

Also using these functions, one can use bit wise operations to apply network masks as well as compare against ranges of IP addresses.

jcho36 February 16th, 2012 at 9:53 pm

it’s interesting, but I think that using a function like INET_NTOA, you lose the index, so the query could be slower, am I right?

David Chipman February 25th, 2012 at 4:00 pm

Craig Schultz is quite right. I saw the reference to INET_NTOA, and did some googling to see if the reverse operation was implemented by MySQL and it is! Save yourself some work, people!

Ramkannan April 20th, 2012 at 9:39 am

That was really a clean code, thanks indeed mate !

Pavan April 22nd, 2012 at 7:58 am

Dude this is awesome – thank you. I was trying to do this for my site and this blog post was instrumental in making that happen.

Melanie June 8th, 2012 at 11:26 pm

Some of my equipment here have two IP addresses, thus if I need to store them both in one field I have to use varchar.

shashi June 19th, 2012 at 5:48 am

nice1.. very helpful

Ado1 September 25th, 2012 at 5:13 pm

What about IPv6 IP addresses?

vipul October 26th, 2012 at 10:23 am

what method is use for the sepretion of the id address of different user

Vlad December 8th, 2012 at 9:39 am

Yep. The best way :D .
Thanks!

Tommy December 20th, 2012 at 2:43 pm

..So how do I actually print it?

SELECT INET_NTOA(ip) FROM ‘db_table’ WHERE 1

Where 1 … = what? I don’t get it.. how does it actually PRINT the IP address..

Nicky January 7th, 2013 at 5:46 pm

Why you use unsigned in your mysql table field? This will make it unable to store a -value who can be returned by ip2long as well.

Will March 25th, 2014 at 10:44 pm

This will cut off all addresses > 127.255.255.255 to become 127.255.255.255 if you use the normal mysql INT column. You need to make the column INT UNSIGNED to work with all ips.

Leave a reply