[nflug] SQL to sort IP addresses - hey Eric

Tue Dec 20 09:59:35 EST 2005

Thank you Frank ...it worked well!

pirrone wrote:
> 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