How to order a select 1 to 255?

Question

Personal I have a query of a table with Ips numbers from 1 to 255 of various ranges.

When I do SELECT IPS FROM ipvalidos WHERE REDE ='{$rede}' ORDER BY IPS

It selects from 10.0.0.100 before 10.0.0.11, for example.

I would like the selection to be 10.0.0.1, 10.0.0.2, 10.0.0.3 etc...

Answer 1

You can use the function INET_ATON(). The inet_aton converts the past address (including dots) to a valid address structure (binary).

Example:

• 10.0.0.100 = 10 256³ + 0 256² + 0 256 + 100 = 167772260
• 10.0.0.11 = 10 256³ + 0 256² + 0 256 + 11 = 167772171

So, your script would look this way:

SELECT IPS FROM ipvalidos WHERE REDE ='{$rede}' ORDER BY INET_ATON(IPS)

I put in the Github the file example_inet_aton.sql for future references and get registered. If you want to see working, access here in the paiza..

It is also possible to carry out the reverse operation of inet_aton, that would be using the inet_ntoa. From a binary value (structure) it returns the address in string format (including points).

To deepen and understand more about function, see here in the documentation of Mysql itself.

Answer 2

Since they’re Ips of the type IPV4 (numbers only), you can remove the point '.', do the CAST to number and sort normally around:

SELECT      IPS 
FROM        ipvalidos 
WHERE       REDE = '{$rede}' 
ORDER BY    CAST(REPLACE(IPS, '.', '') AS UNSIGNED)