Internet standard format (dotted string)

At first it doesn’t seem that dificult, most people simply store an IP-number as a string in their database tables. It simple and when you have a rather limited amount of addresses a simple VARCHAR 11 wil suit you rather well.
We all understand `192.168.1.10` and we use it everywhere, in our firewalls, routers and information about the internet.

Why not as long integer format?

But since an ip-address is a number, it can be stored much more effecient. In PHP there is a function ip2long that will convert an IPv4 Internet network address from its Internet standard format (dotted string) representation. The resulting integer requires only a third of the original space and furthermore look-ups and indexing will be faster.

$ip='192.168.1.10';
 echo ip2long($ip);

will output -1062731510

Note: Because PHP’s integer type is signed, and many IP addresses will result in negative integers, you need to use the “%u” formatter of sprintf() or printf() to get the string representation of the unsigned IP address.

So

$ip='192.168.1.10';
 echo ip2long($ip);
printf("%u\n", ip2long($ip));

results 3232235786 and this can be stored in an INT(11) field in MySQL.

To my surprise I did not get any negative number on my Linux server in the former example, this in contrast to my windows server. It seems that Linux 64 systems automatically converts the ip long address to the needed unsigned version, or more precisely integers on a 64 bit system are 64 bit instead of 32 bit so integers are not longer limited to 2147483647. I can’t confirm this on a Windows 64 bit system.

This means that we can cut the printf command, but for compatibility reasons I will leave it there, it will do no harm.

Converting to dotted string format

To function long2ip() will do the opposite and can be used to format a stored ip number in long format into a string in Internet standard dotted format for output on the screen. It doesn’t matter if the variable is an unsigned or signed integer:

echo long2ip(ip2long($ip));
echo long2ip(sprintf("%u\n", ip2long($ip)));

Will both print 192.168.1.10;

Converting with MySQL.

An alternative way and probably the most efficient is to let MySQL do the converting:

INET_ATON(expr) 

Given the dotted-quad representation of a network address as a string, returns an integer that represents the numeric value of the address. Addresses may be 4- or 8-byte addresses.

INSERT INTO `ip_addresses` INET_ATON('192.168.1.10');
 will store 3232235786

The generated number is always in network byte order. For the example just shown, the number is calculated as 192*256*256*256 + 168*256*256 + 1*256 + 10.

Retrieving the IP-number

The function to retrieve the ip-number in dotted string format is INET_NTOA()

SELECT INET_NTOA(3232235786);
will result '192.168.1.10'

The two functions were added in MySQL 3.23.15, so it can be used on nearly all systems.

This entry was posted on Sunday, May 20th, 2007 at 1:40 pm and is filed under MySQL, PHP. You can follow any responses to this entry through the RSS 2.0 feed. Both comments and pings are currently closed.