PHP MySQL find IP address (between) stored IP address ranges

dev09
dev09 used Ask the Experts™
on
Hi,

I am wondering how to code a PHP MySQL 'SQL' query to find the given IP address between 2 stored IP address ranges.

I am trying this:
$theIp  = 'xxx.xxx.xxx.xxx';
$result = mysqli_query($link,"SELECT * FROM `ipAddresses` WHERE `start` <= '$theIp' AND `end` >= '$theIp'") or die(mysql_error());

But it doesn't seem to be working correctly.  Should i store the IP data differently? Not in one field.

Thanks in advance!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Commented:
Check out the inet_aton MySQL function which will convert the IP address to an integer (https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton)

You should be able to do something like 
$theIp  = 'xxx.xxx.xxx.xxx';
$result = mysqli_query($link,"SELECT * FROM `ipAddresses` WHERE inet_aton(`start`) <= inet_aton('$theIp') AND inet_aton(`end`) >= inet_aton('$theIp')") or die(mysql_error());

Open in new window

The syntax on that may be off as I didn't test it.
Ryan ChongSoftware Team Lead

Commented:
one of the methods is to convert the IP Address to "IP Numbers" and then do the respective comparison.

in php you can use ip2long function:

ip2long
http://php.net/manual/en/function.ip2long.php

in MySQL, use INET_ATON:
https://dev.mysql.com/doc/refman/5.0/en/miscellaneous-functions.html#function_inet-aton
Ryan ChongSoftware Team Lead

Commented:
ah, ok, think using INET_ATON is enough here, so no need to use php function ip2long.
Ray Paseur
Most Valuable Expert 2011
Top Expert 2016

Commented:
This article shows how I've used IP numbers in PHP.  I used the MaxMind database to create a RESTful web service that could be used to identify the client country from its IP address.  Working code examples are included.
http://www.experts-exchange.com/articles/3437/IP-Address-to-Country-in-PHP.html

You might also want to look into the WordPress plugin called Akismet.  It's an anti-spam tool with IP awareness.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial