3 replies to this topic

[Fighter]

After writing a few good applications that require storing the IP in the database, I had always wondered what is the norm for this thing, what I had been doing (and had seen) is creating a 15 max length varchar column and storing it in human readable format.

Do you think there is a more efficient way or built in function for MySQL? What would be the disadvantages/advantages of using them?

[Alexander]

They are actually very similar to timestamps, in fact they are just four bytes separated by dots, four bytes can perfectly fit in to an integer (opposed to 15 bytes of charactes)

MySQL borrows the rfc defined functions for INET (ipv4) from C, and can be used as follows:

mysql> SELECT INET_ATON('11.22.33.44');
+--------------------------+
| INET_ATON('11.22.33.44') |
+--------------------------+
|                185999660 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT INET_NTOA(185999660);
+----------------------+
| INET_NTOA(185999660) |
+----------------------+
| 11.22.33.44          |
+----------------------+
1 row in set (0.00 sec)

I am not sure if there are any major disadvantages, you can always convert them on the fly back to quad dotted form.

[Fighter]

You made my hour of planning worth it, I was wondering, are there any functions that are equivalent in PHP to do all this? I suppose there will be something down the road that will require me to work in-script.

[Alexander]

ip2long and long2ip if I recall, the only odd thing is that PHP cannot represent unsigned integers, they will store as them but if you were to display you'd need to use printf("%u"..)