Planning on storing IP addresses in MYSQL or any other similar database for your PHP application? If you are like most, you probably were thinking of storing the IP addresses as strings in a varchar(15) row. Well, there actually is a much faster and more efficient way of storing IP addresses. The secret lies in PHP’s ip2long() and long2ip() functions which convert a dotted IP address to and from it’s long integer representation. The IP address can then be stored in the database as an int(11) format. When you need to work with the IP address later, simply convert back to the dotted format using the long2ip() function.
The long format is much faster to query for in MYSQL because you are searching an integer index as opposed to a string index. Make sure you don’t use “like” in your query or you could lose this speed increase, and make sure you include an index on this column.
Example code in PHP:
$ip_address="212.253.52.1"; $converted_address=ip2long($ip_address); $sql="SELECT FROM ip_table WHERE ip = $converted_address"; $mysql_query($sql);
In addition to a query speed increase, storing IP addresses as int(11) also has a disk space savings. In my test with 50,000 unique IP addresses, storing them as int(11) used 40% less disk space for the combined data and index than the varchar(15) format. Take note!
This is interesting - I have IP Addresses stored as a varchar. I didn’t even realize you could store them a different way. Well done!
Nice. But, I need to be able to write a query that returns all ip addresses like 192.168. The function doesn’t behave well with incomplete ip addresses.
192.168 becomes -1073741656
-1073741656 returns to 192.0.0.168
Maybe a bug?
Not a bug - actually if you are searching for a range of IP addresses, you need to convert the range using ip2long() and then search for that in MySQL.
Example:
You want to find an IP address in the range 192.168.0.0 to 192.168.255.255.
So convert those bounds to long format using php:
$bound1=ip2long(’192.168.0.0); //this is -1062731776
$bound2=ip2long(’192.168.255.255′); //this is -1062666241
Then do a MySQL query:
$sql=”SELECT * FROM ip_table where ip BETWEEN $bound1 AND $bound2″;
Remember that the first argument of BETWEEN must be the smaller number.
Remember that you can convert those values in mysql as well:
INET_ATON() - Return the numeric value of an IP address
INET_NTOA() - Return the IP address from a numeric value
E.g.
SELECT INET_ATON(”255.255.255.0″);
will return “4294967040″ and
SELECT INET_NTOA(4294967040);
will return “255.255.255.0″.