Sorting values with php/mysql

landysaccount

Hello,

I have a table with ip values:

172.16.100.10
172.16.100.11
.....
172.16.100.50
172.16.100.51
....
172.16.100.100
172.16.100.102
172.16.100.103

and so on....

I would like to get a list in ascending order:

The ip_address field is a varchar(15);

select ip_address from ip where ip_assigned='N' order by ip_address limit 5;

but I get:

172.16.100.10
172.16.100.100
172.16.100.101
172.16.100.102
172.16.100.103

I've tried :
select ip_address from ip where ip_assigned='N' order by ip_address + 0 ;

but, it doesnt work.

How can I get a real sort in mysql?

Thanks in advaced.

johanafm

This is a "real sort". It's called Lexicographical order and is exactly what you find in a dictionary.

You would not expect "Aa" to come before "A", and since you store the IP as text, you should not expect 100 to come before 10. But since you shouldn't reasonably expect that if you compared numbers either, I fail to see your point.

Moreover, since '.' comes before '0', '17.' would come before '170.', so everything still seems fine to me.

Apart from that, + is not a string concatenation operator in MySQL. For that, you'd need to use CONCAT(start, end). Casting an ip address string to a long integer is possible (e.g. ip2long in php), but even if this is done, adding 0 to it won't make the result any different. 1+0 is always less than 10+0, for the same reason that 1 is less than 10. And I doubt MySQL automatically performs an ip2long casting, but I may be wrong.

So, what do you consider a "real sort"? How are you not happy with the result you showed above?

landysaccount

Well, great theory.

You know exactly what results I'm trying to get here. I bet if I would know how to get the results I need I wouldn't be asking for help. So, please don't try to make me look/sound stupid asking for help on this topic.

All I asked for help on something I need to get working and not a theory that's explained in books. I actually knew why I am getting that result, that's why I mentioned the type of field it is.

Thanks for your help.

johanafm

And I'm trying to help you. The part where I ask you a followup question is generally referred to as communication, or more specifically two way communication. If you want to get help, I suggest you play along.

Let's have a look at what you wrote

I would like to get a list in ascending order:

(...)

but I get:

172.16.100.10
172.16.100.100
172.16.100.101
172.16.100.102
172.16.100.103

Since this IS a list in ascending order, exactly matching what you claim you want, yet also claim this is not what you want, I hardly find it strange that I ask what result you are looking for. Get it?

Even if you don't, just show the order you do want to get your results in, and perhaps you will get the answer you claim to be looking for. At least, it will increase your chances of getting said answer far more than describing how you perceive my intentions.

landysaccount

Ok.

I guess I wasn't clear enough I have a table with ip numbers from 172.16.100.10 to 172.16.105.254

I was trying to get these sorted in asc order. I notice by your post (thanks again) that there is a function in php called ip2long(). I worked around to acclomplish what I needed:

$ip_query = "select ip_address as ip from ip where ip_assigned='N'";
$ip_result = dba_connect( $ip_query, 1 ) or 
                      die( "Query: '$ip_query', failed with error message: -- " . mysql_error() . " --" );

//$cust = mysql_fetch_array( $customer_result );
$i = 0;
while( $ip = mysql_fetch_array( $ip_result ) ){
     ///echo "IP: " . $ip['ip'] . " <br />";
     $val[$i++] = ip2long( $ip['ip'] );
}
//sort values
sort( $val );

for( $i = 0; $i < 5; $i++ ){
    //echo "printing: <br>";
    echo long2ip( $val[$i] ) . "<br>";
}

This now works. I tried doing it in MySQL but, couldn't so I just did it this way.

Thanks a lot for your help, I appreciate it and sorry for not understanding you the first time.

landysaccount

Here's another solution I just found:

ORDER BY INET_ATON(ip)