I'm having bit of a trouble selecting correct values from my mysql sql server.
The ip can be ipv6 and v4.
Table: User{
...
ip binary(16)
}
$ip = '192.168.10.115'; $ip = bin2hex(inet_pton($ip)); // Returns c0a80a73 $result = $this->db->select("SELECT * FROM User WHERE HEX(ip) = $ip"); // $result empty because in db its stored as: // HEX(ip) = C0A80A73000000000000000000000000 How can I get a viable match to the * 00000 * ?
If input was a ipv6 match this would be ok, but ip v4 not.
Can you use VARBINARY instead of just BINARY?
From the MySQL Manual on Binary/Varbinary:
If the value retrieved must be the same as the value specified for storage with no padding, it might be preferable to use VARBINARY or one of the BLOB data types instead.
UPDATE:
The MySQL 5.6.3 and higher have support for IPv6 addresses - see the following: "INET6_ATON(expr)"
The data type is VARBINARY(16) instead of BINARY(16) as was suggested by earlier comments here. The only reason for this is that the MySQL functions work for both IPv6 and IPv4 addresses. BINARY(16) is fine for storing only IPv6 addresses and saves one byte. VARBINARY(16) should be used when handling both IPv6 and IPv4 addresses.
ip VARBINARY(16)?HEX()will work on a numeric input.