April 3rd, 2011

planet

How to encode & store an IP address (php/mysql)

I'm always needing to do this and when I google it to jog my memory I always find the STUPID WAYS instead of the right way.

To store an IP address in a mysql table using a php script, this is the statement:

$sql = "INSERT INTO table (ip) VALUES INET_ATON('".$_SERVER['REMOTE_ADDR']."')";

Do not write some seven line function to take the decimals out of an ip address. Do not store the ip address, unencoded, in a varchar field... it takes up tons of memory, as my friend jason (@jasnk) tipped me off on years ago. INET_ATON is a useful function and the only way to do this. Refer to mysql doc: miscellaneous functions for more info. There is a decode function, but I never use it, if I need to search for an IP I still use the encode:

$sql = "SELECT column FROM table WHERE ip = INET_ATON('".$_SERVER['REMOTE_ADDR']."')";

And final lesson I learned the hard way: make sure your ip column is an UNSIGNED integer. Signed will work for some IP addresses, but not all.