How do you store an IP Address (v4 or v6) as an int in any database using php?

Question

I need to store IP address in the most compact way possible, searching is not a concern. This also needs to be compatible on Windows, Linux, and Mac using any DB (MySQL, SQLite, Postgre, etc).

Thanks to the ip2long() and long2ip() functions in PHP I can convert a IP4 address to a small int field rather than a varchar(15) or whatever. The problem is that we are in a transition stage to IP6 which means that most of the existing best-practices are failing - including those functions which don't work with IP6. Further more, DB specific functions like INET_ATON() and INET_NTOA() aren't options here.

So how do you store IP address as INT's (or any other compact format) in a way that will work on any system?

EDIT: The thread Wrang-wrang pointed mentions the use of inet_ntop() and inet_ptop() to pack and unpack either IP4 or IP6. The problem is that they only work on Linux/Mac with PHP +5.1 (which isn't a too big of a problem) and on windows with +5.3 (which is).

If that was not a problem are their replacements for the varbinary(16) field that was recommended in each database?

Answer 1

You just need to choose a way of storing 128 bits:

::/96 — This is a 96-bit zero-value prefix originally known as IPv4-compatible addresses. This class of addresses were used to represent IPv4 addresses within an IPv6 transition technology. Such an IPv6 address has its first 96 bits set to zero, while its last 32 bits are the IPv4 address that is represented. The Internet Engineering Task Force (IETF) has deprecated the use of IPv4-compatible addresses with publication RFC 4291. The only remaining use of this address format is to represent an IPv4 address in a table or database with fixed size members that must also be able to store an IPv6 address.

(from http://en.wikipedia.org/wiki/IPv6)

I think two BIGINT UNSIGNED would be a reasonable way to store them, but I guess you could use a binary (byte array).

This question appears to have been answered already at http://stackoverflow.com/questions/444966/working-with-ipv6-addresses-in-php and http://stackoverflow.com/questions/420680/how-to-store-ipv6-compatible-address-in-a-relational-database

Answer 2

You might want to have a look at this SO thread. The poster settled on using two BIGINT fields to store an IPv6 address, but there are other suggestions there as well which you may want to consider.