sorting by ip addresses

[volatileboy]

I can't think of a logical way of doing this so I appreciate any help!

 

I am trying to sort some information from the database by ip address but for obvious reasons it does not sort the ips in a propper order for example the ips get listed like this:

 

200.113.178.11

208.38.116.185

208.38.116.185

208.38.116.185

208.38.116.185

208.38.116.185

217.136.72.105

217.136.72.105

24.163.230.170

24.174.109.231

24.192.226.76

24.218.120.66

62.195.222.41

64.231.213.222

 

I am just using a straight forward:

ORDER BY ip ASC

 

query for this, I really dont know how I could get this to work, thanks in advance!

[benjaminbeazy]

try natsort...

 

$sql = "SELECT ip FROM wherever";
$result = mysql_query($sql);

$ips = array();

while ( $row = mysql_fetch_assoc($result) ) {
    $ips[] .= $row['ip'];
}

$ips = natsort($ips);

[volatileboy]

The problem is that im not selecting the ip address in the query, natsort() seems like the perfect function to use but I have no idea how I can envoke it into making this work.

 

To be a little clearer lets say my initial query is this:

 

"SELECT name, ip, log FROM table ORDER BY ip ASC"

 

because im not only outputting the ip i think it needs to be done a different way, I might be wrong.

 

I do appreciate your help though

[trq]

because im not only outputting the ip i think it needs to be done a different way

 

If your not outputting the ips why do you need them sorted. What are you going to do with them?

[volatileboy]

I am outputting the ip addresses, but im also outputting other data as well

 

and for the record im doing nothing illegal with the ip's, im writing a script that parses server log files for a game and I need to sort log entries into an ordered list

[trq]

Ah.. now I see. Try...

 

SELECT name, ip, log, INET_ATON(ip) AS sortedip FROM tbl ORDER BY sortedip ASC;

[volatileboy]

$sql = "SELECT ip, alias, INET_ATON(ip)AS sortedip FROM log WHERE normal='1' AND cheat='0' ORDER BY sortedip ASC";
$result = mysql_query($sql);
$num = mysql_num_rows($result);
$i = 0;
while($i < $num) {
$ip = mysql_result($result,$i,"ip");
$alias = mysql_result($result,$i,"alias");

echo '' . $ip . ' - ' . $alias . '<br />';
$i++;
}#

 

that is outputting like this:

 

75.137.119.226 - alias

24.222.124.140 - alias

74.139.130.140 - alias

86.94.134.62 - alias

24.200.202.244 - alias

84.105.63.207 - alias

84.105.63.207 - alias

74.97.3.24 - alias

 

I replaced the aliases with alias just incase you think thats an error, INET_ATOM doesnt appear to be working for that, I had previously been using SELECT * FROM table actually but I dunno its late.

 

Any other ideas?

[volatileboy]

ignore the # on the end of the code too, thats a typo =)

[benjaminbeazy]

u could use a multidimensional array with each subarray's key being th ip, then use natsort on the main array

[socratesone]

ORDER BY INET_ATON(ip);

[trq]

What field type is the ip field?

[volatileboy]

ORDER BY INET_ATOM(ip) cause an error.

 

The ip field is varchar, might inet_atom work better with another type?

[trq]

I don't think it matters what field type it is really. The method I posted above should work.

[trq]

Matter of fact works fine here...

 

mysql> SELECT ip FROM tbl;
+----------------+
| ip             |
+----------------+
| 75.137.119.226 | 
| 24.222.124.140 | 
| 74.139.130.140 | 
| 86.94.134.62   | 
| 24.200.202.244 | 
+----------------+
5 rows in set (0.00 sec)

mysql> SELECT ip, INET_ATON(ip) AS sip FROM tbl ORDER BY sip ASC;
+----------------+------------+
| ip             | sip        |
+----------------+------------+
| 24.200.202.244 |  415812340 | 
| 24.222.124.140 |  417234060 | 
| 74.139.130.140 | 1250656908 | 
| 75.137.119.226 | 1267300322 | 
| 86.94.134.62   | 1449035326 | 
+----------------+------------+
5 rows in set (0.01 sec)

mysql> 

 

tbl is VARCHAR(16)

[volatileboy]

hmmm its not working for me, i slimmed the query down to test it against yours and its still not working:

 

$sql = "SELECT ip, INET_ATON(ip) AS sip FROM log ORDER BY sip ASC";
$result = mysql_query($sql);
$num = mysql_num_rows($result);
$i = 0;
while($i < $num) {
$ip = mysql_result($result,$i,"ip");
echo "$ip<br />";
$i++;
}

 

Any ideas as to why its working for you and not for me?

[socratesone]

INET_ATON(ip), not INET_ATOM(ip)

[socratesone]

INET_ATON(ip), not INET_ATOM(ip)

(That's an "N", not an "M")

 

Also, this code:

"SELECT ip, INET_ATON(ip) AS sip FROM log ORDER BY sip ASC";

...is a little redundant, unless you really want the results from INET_ATON(ip) other than for sorting, which I don't think you want.

[wsantos]

why not try this...i don't believe its from the query, possibly on handling the values

 

$sql = "SELECT *, INET_ATON(ip) AS sip FROM log ORDER BY sip ASC";
$result = mysql_query($sql);
while($resultdata=mysql_fetch_array($result,MYSQL_ASSOC))
  $dataarr[]=$resultdata;  

// Your post assignment codes here...

 

just check on my parse...typed it manually...