How to Store an IP Address in MySQL VARBINARY Column Using PHP?

You can use the PHP inet_pton() function to store an IP address in a MySQL database that has VARBINARY column for storing IP addresses, for example, like so:

$ipBin = inet_pton('127.0.0.1');

$sth = $dbhandle->prepare("INSERT INTO user_ip (ip) VALUES (?)");
$sth->execute([$ipBin]);

This works for both, IPv4 and IPv6 addresses; it converts an IP address string to 32-bit or 128-bit binary string. Therefore, you would need to set your IP address column as VARBINARY(16) to accommodate both, IPv4 and IPv6 addresses (because 128-bits equal to 16-bytes). However, if you wish to only store IPv4 addresses, then you could use VARBINARY(4) instead (because 32-bits equal to 4-bytes).

Please note that FALSE is returned if an invalid IP address is supplied as an argument the inet_pton() function.