在MySQL数据库的不同字段中查找数字是否在两个数字之间

时间:2021-11-30 22:44:05

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?

如何通过PHP对MySQL数据库的查询进行格式化,以查找两个不同字段之间的IP地址是否介于两个数字之间?

Numerical representation of IP address to find:

IP地址的数字表示查找:

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"

3 个解决方案

#1


31  

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.

将返回所提供的编号位于从IP到IP之间的行。

#2


3  

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

如果您已经有了相当于IP地址的DWORD,那么来自Jordan的响应将起到作用。

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.

如果需要,在执行查询之前,计算相当于ip地址的DWORD,可以执行以下操作。

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

在第1部分第2部分第3部分中给出一个ip地址a。第4部分则DWORD当量可由公式计算:

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.

然后可以在SQL查询中使用dword(a)结果。

Start with an IP address like 206.191.158.55.

从一个IP地址开始,比如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.

结果将是与IP地址等价的dword。在这种情况下3468664375。

See more about DWORD equivalent of ip addresses here.

有关DWORD的ip地址的更多信息,请参见这里。

#3


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 ?

例如,获取ip_from和ip_to之间的“1265631252”的所有行?

#1


31  

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.

将返回所提供的编号位于从IP到IP之间的行。

#2


3  

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

如果您已经有了相当于IP地址的DWORD,那么来自Jordan的响应将起到作用。

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.

如果需要,在执行查询之前,计算相当于ip地址的DWORD,可以执行以下操作。

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

在第1部分第2部分第3部分中给出一个ip地址a。第4部分则DWORD当量可由公式计算:

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.

然后可以在SQL查询中使用dword(a)结果。

Start with an IP address like 206.191.158.55.

从一个IP地址开始,比如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.

结果将是与IP地址等价的dword。在这种情况下3468664375。

See more about DWORD equivalent of ip addresses here.

有关DWORD的ip地址的更多信息,请参见这里。

#3


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 ?

例如,获取ip_from和ip_to之间的“1265631252”的所有行?