Categories
MySQL PHP

Converting IP addresses to sortable form in MySQL & PHP

IP addresses can't readily be sorted numerically because they are strings and the periods in the dotted-quad format common to IP addresses confuses things.  For instance, in a sorted list, 143.16.20.11 will sort before 23.45.1.78 even though what you really want is to sort by the first octet (23 & 143), then by the second octed (16 & 45) and so on.

PHP and MySQL provide functions to convert IP addresses to sortable format.

MySQL

Two functions are provided for use in MySQL query.  When your IP addresses are stored in a database, you can pull them ready to sort.

INET_ATON() Returns the numeric value of an IP address
INET_NTOA() Returns the IP address converted from a numeric value

Remember that if you need both representations you can pull the value twice in the same query, i.e.

SELECT ip AS ip_dotted_quat,INET_ATON(ip) as ip_sortable FROM mytable

This provides a clean set of data for your PHP or other scripting code to work with.

PHP

You have similar options in PHP.

ip2long("127.0.0.1") converts the IP to a long integer.

long2ip() converts the long integer back to a dotted-quad IP address.

The online PHP manual suggests using the two in combination to validate an IP as shown here

<?php
// make sure IPs are valid. also converts a non-complete IP into
// a proper dotted quad as explained below.
$ip = long2ip(ip2long("127.0.0.1")); // "127.0.0.1"
$ip = long2ip(ip2long("10.0.0")); // "10.0.0.0"
$ip = long2ip(ip2long("10.0.256")); // "10.0.1.0"
?>