IP address sorting

[Anon]

Hi, I'm not sure if this should go in this section or the database, so I will start here.

I have my table setup, and the php code to display it. However I am running into a problem with properly sorting a field that stores IP address's. It starts out fine. Here is an example.

12.82.174.156
12.83.243.244

128.1.0.73
128.100.86.86
..Snipped for brevity
217.96.2.151
217.96.2.155
24.0.0.203
24.0.121.22
24.0.148.207
38.38.149.222
38.38.50.190
4.1.78.38
4.16.19.102
4.54.86.143
47.129.8.121
47.142.226.49
47.142.231.118
47.142.231.129
47.234.129.113
47.234.132.55
56.0.78.69
56.0.78.83
57.55.133.188
61.0.0.58

See how on the first octet it does not sort the way I want. I dont know of a way to fix this. Could it possibly be my field. Right now it is set to varchar, since it has . in it I cant use integer as a field. Any help would be apprciated.

[Anon]

I suggest you'd split the IP-address up into four parts (IPA, IPB, IPC and IPD) That way - when you query the database - you'd be able to ORDER BY IPA, IPB and soon on. I'm not quite sure it will work, but it's almost the best thing I can come up with.

Yours

Jacob "Techek" Kamp Hansen
- webmonkey at Portaltjenesten A/S

[Anon]

no. Should it be. I have not gotten to the point of indexing, and just started to read the importance of it.

[Anon]

you could always store the ip addresses in hex as well.

[Anon]

You need to normalize the IP address. basically, you just prepend 0's to fill up the three char octets.

Here's some code. can't remember if I wrote this, or cribbed it from someplace, so apologies where appropriate.

pass in a string of octets seperated by periods (dots) xx.xx.xxx.xxx

function normalizeip($tempip) {
$returnip = "";
$tempoctets = explode(".",$tempip);
$i=0;
if(strlen($tempoctets[$i]) == 1){
$returnip = $returnip . "00" . (string)$tempoctets[$i];
}
elseif(strlen($tempoctets[$i]) == 2){
$returnip = $returnip . "0" . (string)$tempoctets[$i];
} else {
$returnip = $returnip . (string)$tempoctets[$i];
}
for ($i=1; $i <= 3; $i++) {
if(strlen($tempoctets[$i]) == 1){
$returnip = $returnip . "." . "00" . (string)$tempoctets[$i];
}
elseif(strlen($tempoctets[$i]) == 2){
$returnip = $returnip . "." . "0" . (string)$tempoctets[$i];
} else {
$returnip = $returnip . "." . (string)$tempoctets[$i];
}
}
return ($returnip);
}

[Anon]

Ok I'm new to all of this, just starting out. But where do I define $tempip ? How do I define to tell it to work on my field that has the IP's already? Or is this a process for before it goes into the database?

[[deleted]]

The problem is that you store the IP number as a string.
When you ORDER BY it in SQL, it is ordered alphabetically.
And looking from an alphabetical angle, 4 is less than 12, simply because the string is shorter.

As the other repliers mentioned there are several ways to overcome this by making your method of ordering fit your data or making your data fit the ordering method.

If you want to continue storing the IP as a string, you will have to
prepend leading zeros to each part of the IP so it looks like this:
020.145.002.023
And why? because now all strings have the same length. The only thing left to order by is content, and even in strings 1 is less than 2.

You could do this with a complex SQL query (mysql has very nice lpad() and substring_index() functions that make this "simple")

Or you could do it in PHP by exploding the IP and prepending zeroes.
My recommended course of action would be to transform the already stored IP's to "normalized" IPs (with leading zeroes) and
from then on store all new IP's in the same way.
In php you can easily explode() an IP into 4 strings, which in MySQL you can lpad() and concat() back into one string for storage.

If you are free to change the layout of your database you
have a few other options to play with:
- Store the IP as one large number.
- Store each segment of the IP seperately.

The first is best because the seconds eats space like there's no tomorrow :-)

An IP is basically a number that is chopped into pieces, each piece having 256 possible values.
To get one big value just make a calculation like this:
IP=1.2.3.4
number=(1256256256)+(2256256)+(3256)+4

Getting the original IP back is a bit more difficult, but there lies your challenge.
You could ofcourse choose to store both the string and the value, so you can order by the value and still display the string.

[Anon]

$tempip is just the IP number you want to normalize. Presumably, you already have this.

Let me clarify a bit.

Since you're sorting IP's, I assume you have already stored them in your table, after pulling the IP from the http headers or some such.

As Vincent said in a follow up post, the problem you are experiencing, is that you are sorting a string, not a number - so the sorts are happening alphabetically - so you need to make sure your IP is alphabetically correct, and that means you have to pad leading zeros onto each octet (called normalizing the IP). The problem is, you must do this prior to storing the string. here's a little pseudo-code showing the basic flow:

function thingy {
global $REMOTE_ADDR;
// store the IP
$thequery=sprintf("INSERT INTO mytable (userip) VALUES ('%s')",normalizeip($REMOTE_ADDR));
db_query($thequery);
}

This stores the normalized IP (assuming you used the code I posted earlier) in the "userip" field of "mytable". You can now sort on that field, and it will work fine.

if you already have the IP's stored, and need to convert them, you're going to need to read record, normalize ip, perform update on record, for each record in your table.

There is, of course, an alternative to this - you can convert the IP number to a 32 bit number, and work with it that way. Just depends on whether the IP is more useful as a string or a number for you.

[kparker]

If you are free to change the layout of your database you

OK, as long as we're dreaming for him, let me add:

If you are free to change your database, just convert to PostgreSQL, which has a built-in 'inet' datatype that does all the heavy lifting for you. I myself have only ever used 'inet' columns for storing IP addresses, but there are also a series of 'inet' operators, including the very useful 'IS_CONTAINED_WITHIN':

&nbsp;&nbsp;&nbsp;'192.168.1.5'::inet << '192.168.1/24'::inet

This will return a true value only if the lhs is part of the subnet defined by the rhs.

[Anon]

Hi, i am looking for exactly the kind of software you are writing, can you PLEASE help me with this..

Thank you
Willem Pretorius
Web Developer
Northweb

[Anon]

Just use the mysql inbuilt functions

inet_ntoa and inet_aton.

Examples:

mysql> select inet_aton('192.126.3.3');
+--------------------------+
| inet_aton('192.126.3.3') |
+--------------------------+
| 3229483779 |
+--------------------------+
1 row in set (0.02 sec)

mysql> select inet_ntoa('3229483779');
+-------------------------+
| inet_ntoa('3229483779') |
+-------------------------+
| 192.126.3.3 |
+-------------------------+
1 row in set (0.01 sec)

So in your case, just do 'order by inet_aton(dotted IP address)'

--
Imran William Smith
Security Products Development
Mimos Bhd, Malaysia