Find if number is between two numbers in different fields in a MySQL database

Question

How would you format a query on a MySQL database via PHP to find if an IP address falls between two numbers in two different fields?

Numerical representation of IP address to find:

1265631252

Database format:

 IP FROM      IP TO       REGISTRY  ASSIGNED   CTRY CNTRY COUNTRY
"1265631232","1265893375","arin","1152835200","US","USA","United States"
"1265893376","1265958911","arin","1149120000","US","USA","United States"
"1265958912","1266024447","arin","1149120000","US","USA","United States"
"1266024448","1266089983","arin","1162425600","US","USA","United States"

Answer 1

A query like this:

SELECT * FROM your_table WHERE 1265631252 BETWEEN `IP FROM` AND `IP TO`;

Will return the row(s) for which the supplied number is between IP FROM and IP TO.

Answer 2

What about something like this :

select *
from your_table
where ip_from <= '1265631252'
  and ip_to >= '1265631252'

i.e. get all the lines for which '1265631252' is between ip_from and ip_to ?

Answer 3

If you already have the DWORD equivalent of the IP address then the response from Jordan will do the trick.

SELECT * FROM your_table WHERE 1265631252 BETWEEN `IP FROM` AND `IP TO`;

If you need to, before executing the query, calculate the DWORD equivalent of the ip address you can do the following.

Given an ip address a in the form part1.part2.part3.part4 then the DWORD equivalent can be calculated with the formula:

dword(a) = ((a.part1 * 256 + a.part2) * 256 + a.part3) * 256 + a.part4

and then you can use dword(a) result in your SQL query.

Start with an IP address like 206.191.158.55.

((206 * 256 + 191) * 256 + 158) * 256 + 55

The dword equivalent of the IP address will be the result. In this case 3468664375.

See more about DWORD equivalent of ip addresses here.