Hello!
I have a db field to store IP addresses. I want to be able to sort them so they appear in numerical order. But when I do the "ORDER BY ip" it sorts only by the first number of the entire IP address, so a 125.x.x.x would be before an 88.x.x.x ip. Is there a way around this so that it puts them in total correct order?
Maybe using a postgreSQL database... I dont think mysql has much support for ips.
What field type are you storing these ips in?
badgoat wrote:Hello!
I have a db field to store IP addresses. I want to be able to sort them so they appear in numerical order. But when I do the "ORDER BY ip" it sorts only by the first number of the entire IP address, so a 125.x.x.x would be before an 88.x.x.x ip. Is there a way around this so that it puts them in total correct order?
If you store IP-addresses in another form, as an INTEGER, not a string
then I imagine they can be sorted easily, AND CORRECTLY.
PHP has got functions for transform to and from a normal IP expression string:
see
http://php.net/ip2long
http://php.net/long2ip
As always, and
in both those function pages, you might find some nice small scripts
in Comments contributed
.
thorpe: I was using a varchar field datatype. I've since changed that to integer per halojoy's suggestion. Using ip2long and long2ip I am able to submit to the db and to pull it and display in a correct IP fashion using long2ip. Sorting still seems to be an issue though. As an example, an IP of 203.x.x.x appears higher on the list than a 24.x.x.x IP address. I've tried removing the ORDER BY which changed up the order a slight bit, but still not in correct numerical order. Am I missing someting still?
Are you sure you're sorting on the long form when you do your sql query? Can you look in the database and find the long form of each the 24.x.x.x and the 203.x.x.x addresses? There's no way that the long form of a 24. address should be higher than the long form of a 203. address.
Maybe you're storing the long form as a text string? You need to store the long form of the address as an int. When they are text strings, 99 is higher than 1000 but when they are stored as integers, 1000 is higher than 99.
Sure! I just checked, the IP is stored as an INT datatype.
Here are three long forms:
-873366528 203.x.x.x
402653184 24.x.x.x
1459617792 88.x.x.x
-669908992 216.x.x.x
So when I query, the IPs are displayed in this order:
88.x.x.x
216.x.x.x
203.x.x.x
24.x.x.x
I'm not an expert in how MySQL stores 4 byte (32 bit) numbers. I think that INT will record numbers from -2 billion to +2 billion even though IP addresses run from 0 - 4 billion.
Someone might have a better solution than this but you might try making the datatype an unsigned INT in MySQL. ( see http://www.htmlite.com/mysql003.php ) or you might try BIGINT but that might be overkill. I've never quite understood that whole negative number notation that MySQL does for large numbers - specifically how/when they get coverted back to their original positive number.
After closer observation, it seems that when i do the "ORDER BY sip" it sorts the integer correctly, which does not put it in correct IP order but does put it in correct numerical order of the integers in the db. So it makes me think that the MySQL query should be something like "ORDER BY '".long2ip($sip)."'" but that's not correct either, it shuffles the order but still is not correct.
Ok. I got it to work.
First I tried this:
$query="insert into ips (ipstring,ipint) values ('$ipaddress',".ip2long($ipaddress).")";And that didn't work. It gave the same bad results that you were seeing.
Then I tried this:
$x = split("\.",$ipaddress);
$temp = (256*256*256*$x[0]) + (256*256*$x[1]) + (256*$x[2]) + ($x[3]);
$query = "insert into ips (ipstring,ipint) values ('$ipaddress',$temp)";And that worked perfectly when I run this query:
select ipstring,ipint from ips order by ipint
So the problem appears to be the ip2long function. I'm not sure what that function does but it doesn't do what we think (wanted) it to do.
By the way, the datatype on the ipint field was just INT, not bigint or anything like that.
Excellent! I should keep the datatype as an integer for this? Also the two variables is kinda throwing me off. Can you explain that more? I appreciate that you stuck with it to give me a hand!
I put two columns in the database.
create table ips (ipstring char(20),ipint int);
ipstring is the human readable 1.2.3.4 format. ipint is the decimal conversion of the ip address.
(Technically, that's wasteful of hard drive space and it's inefficient because I could have simply converted the decimal version back to standard ip address notation but for the purpose of this test, it made troubleshooting easier).
So after I inserted all the IP addresses, I had a table like this:
mysql> select * from ips;
+-----------------+------------+
| ipstring | ipint |
+-----------------+------------+
| 1.2.3.4 | 16909060 |
| 2.3.4.5 | 33752069 |
| 10.1.1.1 | 167837953 |
| 204.1.1.1 | 2147483647 |
| 24.1.2.3.4 | 402719235 |
| 240.240.240.240 | 2147483647 |
| 5.255.255.255 | 100663295 |
+-----------------+------------+
7 rows in set (0.03 sec)
To insert the IP addresses, first I broke the ip address (1.2.3.4) into an array called $x with split so that
$x[0] = 1 (first number in the ip address)
$x[1] = 2
$x[2] = 3
$x[3] = 4 (last number in the ip address)
Then I made a variable called $temp which is the decimal conversion of the ip address:
$temp = (256256256$x[0]) + (256256$x[1]) + (256$x[2]) + ($x[3]);
So when I insert $temp as the integer instead of ip2long($ipaddress) I know that I'm putting in a value that is really the decimal conversion of the ip address.
Thank you kindly for the explanation! I was able to duplicate your results locally, and even better I understand it all now, which eases putting it into the code where I need it 
You're welcome.
I'd be interested to hear from any of the experts in the group who can explain whether we were using the ip2long function incorrectly or whether the function is just broken.
Uh oh... On my system when I enter an IP of anything over 127.255.255.255 it defaults to 127.255.255.255... Not sure why?
The echo statement is your most powerful troubleshooting tool.
echo each part of the formula:
$temp = (256256256$x[0]) + (256256$x[1]) + (256$x[2]) + ($x[3]);
so that you can see how $temp arrives at its value. Once you know the value that $temp gets, compare that to what's getting written in MySQL. Maybe they're not the same?
If you're having problems with numbers in MySQL (though I did not), you can try bigint.
I went with the easier route, changing the datatype to bigint and that re-solved it 
Thank you (again!)
Np. We probably have different versions of MySQL and they behave differently - mine was happy to store the larger numbers in a regular int field. I'm glad that bigint solved it. Using an unsigned int probably would have solved it too.