Store IPv6 in database – Here in this article, we will share some of the most common and frequently asked about PHP problem in programming with detailed answers and code samples. There’s nothing quite so frustrating as being faced with PHP errors and being unable to figure out what is preventing your website from functioning as it should like php and mysql . If you have an existing PHP-based website or application that is experiencing performance issues, let’s get thinking about Store IPv6 in database.
What’s the best practise to store IP’s with PHP in MySQL database?
There’s a function called ip2long – but this is just for IPv4.
But what about IPv6?
I know a php function that is for IPv6 IP’s,
but it doesn’t work on Windows with PHP < Version 5.3
knittl was closer, instead of binary(16) use varbinary(16) as user196009 answered in a related question. It works for me. How?
$query = "insert into stats(vis_ip, id_stat) values('" . inet_pton('22.214.171.124') . "', '1')"; // google's IP address // using a PDO wrapper. http://www.phpclasses.org/package/5206-PHP-Execute-database-queries-from-parameters-using-PDO.html include_once 'db.php'; $c = new DB(); $visit = $c->getResults($query); // stored as binary
$query = "SELECT `vis_ip` FROM `stats` WHERE `id_stat`=1"; // PDO wrapper include_once 'db.php'; $c = new DB(); $stats = $c->getRow($query); echo inet_ntop($stats->vis_ip); // outputs 126.96.36.199
It should work with IPv6 addresses (I have an IPv4 connection).
I’m not an expert so I don’t know yet if varbinary length is correct, but how I said, it works for me.
In order to check if ‘IPv6 Support’ is enabled in your PHP version/host:
phpinfo(INFO_GENERAL); // http://php.net/manual/es/function.phpinfo.php
The dotted-decimal IPv4 address can be converted to an integer, with a maximum size of 32 bits. IPv6 addresses are 128 bits. Since 128 bits do not fit in a PHP int, this will be a pain to work with in PHP.
If you just want to connect and use IPv6 addresses, save yourself the trouble and save them as text. If you want to apply netmasks and calculate subnets, then you need to convert them.
there’s the php function
inet_pton, it will turn an ip address string into its binary representation (for both ipv4 and ipv6). you can then store it as
binary(16) in your mysql database.
to get a human readable address again, use
Note that MySQL (5.6) now support IPv6 addresses, see INET6_ATON().
On second comment at
ip2long function manual you have a function called
IPv6 (and there are more below).
You could just store it as a string in a
CHAR I suppose