[nflug] SQL to sort IP addresses - hey Eric

Sat Dec 17 13:15:34 EST 2005

Eric,

I posted that SQL query solution to your IP sort question in haste and 
left off a function required to convert the quartet I selected to a 
numerical value so that it would sort in correct order.  The most 
logically clear function in MySQL in the absence of a number() function 
is probably abs().  So to sort just the fourth quartet:

SELECT * FROM whatever_table
ORDER BY abs(substring_index(ip_address,'.',-1))
ASC;

which takes the portion on the right of the IP address back to the last 
dot (that '.' and -1) and converts it into a numerical value.  If you 
also need to sort by the third quartet first just add an additional 
expression:

SELECT * FROM whatever_table
ORDER BYabs(substring_index(substring_index(ip_address,'.',3),'.',-1)),
abs(substring_index(ip_address,'.',-1))
ASC;

which first takes the right piece of the IP address portion consisting 
of the first three quartets (that 3 and -1).

The same sorted result could be achieved with a simple unordered SELECT 
query in a PHP document, loading the table values into an array and 
performing a bubble sort, but I assume this ordered query will execute 
faster, is easier to write, and clearer to read.  Putting the above into 
a simple document that uses an external authentication page auth.php 
would look like:

<? require_once("auth.php") ?>
<html>
<head><title>test ip sort</title></head>
<body>
<p>Sorted by IP address:</p>
<?
    $return = mysql_connect("localhost",$PHP_AUTH_USER,$PHP_AUTH_PW);
    $status = mysql_select_db("test",$return);

    $result = mysql_query("SELECT * FROM machines order by 
    abs(substring_index(substring_index(ip_address,'.',3),'.',-1)), 
    abs(substring_index(ip_address,'.',-1)) asc",$return);

    $num = mysql_numrows($result);
?>                             
<table border="1" cellpadding="2">
    <tr>                       
        <td>Computer</td><td>IP Address</td>    
    </tr>       
<?                             
        for ($i =0 ;$i < $num; $i++):
                echo "<tr>";   
                    echo "<td>";
                    echo mysql_result($result,$i,"computer");
                    echo "</td>";
                    echo "<td>";
                    echo mysql_result($result,$i,"ip_address");
                    echo "</td>";
                echo "</tr>";
        endfor;
    $status = mysql_close($return);
?>
</table>
</body>
</html>

And, there you have it.  Sorry for the omission.

Frank
_______________________________________________
nflug mailing list
nflug at nflug.org
http://www.nflug.org/mailman/listinfo/nflug