Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 461
  • Last Modified:

mysql script to convert IP address to integer

Hi Team,

  I have a mysql database where in the NETWORK table, the IP_ADDRESS field is varchar and holds standard IP addresses in different lengths  like 1.2.3.4  or 64.55.66.566,  or 123.456.789.123  etc.  I  need another field (i.e. IP_ADDRESS_DECIMAL) to hold their decimal values.   I have seen ip2long in php but I am not sure how to use that......an ordinary bash, perl script with similar functionality will work for me.  I need to put my script in a cron job.    Some bash/perl script like this:

 
mysql -u root -pmypass  mydatabase -e "UPDATE NETWORK SET IP_ADDRESS_DECIMAL = Somefunction(IP_ADDRESS);

Open in new window

Thanks very much.
0
rleyba828
Asked:
rleyba828
  • 3
  • 2
  • 2
  • +2
4 Solutions
 
Neil RussellTechnical Development LeadCommented:
Question is, are you storing THOUSANDS of addresses?  Do you ONLY ever want to store and display never anything else? Never want to know about how many on a network? How many in a country... examples...

The only point of the conversion to a long would be to save space on a very large database.  For small databases it would make no sense to have the over head of conversion everytime you needed to do anything IPADDRESS related.
0
 
Fred MarshallPrincipalCommented:
Perhaps some illumination on why you want to do this would be helpful.
Do you want to do the inverse conversion as well?
Do you have rules for the conversion in mind?  The specification at first glance does not appear to offer a 1:1 mapping.
For example, if one uses a straight powers-of-two summation then the result is not unique:
100.000.000.000 and 020.000.000.000 yield the same result: 2048.
This suggests the trivial but useful conversion which would simply remove the decimal points.
etc.
0
 
rleyba828Author Commented:
Thanks guys... bur first of all, my apologies....  I didn't use realistic IP addresses in my example above, and that might have added to the confusion...   I was just typing away to illustrate that the field would contain variable length strings.

Hi Neilsr,

   I will be only storing at most....about 1000....and we will only be doing it for our own comany's IP addresses....particularly those in our branches....  I will be doing computations, sorting, comparisons, based on the decimal equivalent of the IPs.  So it is not going to be expensive for a fairly small database.

Hi fmarshall,
   I will only be converting one way....   IP   --> Decimal.  

I am looking for a bash or perl script  that does this   -->  http://www.ipaddressguide.com/ip

1.2.3.4  --> 16909060.
 64.55.66.223   --> 1077363423.

Thanks very much.
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
ozoCommented:
perl -le 'print unpack "N",pack"C4",split/\./ for @ARGV' 1.2.3.4   64.55.66.223
0
 
Fred MarshallPrincipalCommented:
Well, I can't write this out in perl but I can write it out in pseudo code of sorts:

Get the dotted IP address:
Split it into 4 parts.
Multiply the leftmost part by 16,777,216 or 256^3 or 2^(8*3)=2^24
Multiply the 2nd part by 65,536 or 256^2 or 2^(8*2)=2^16
Multiply the 3rd part by 256 or 256^1 or 2^(8*1)=2^8
Multiply the 4th part by 256^0 orr 2^(8*0) or 2^0.
Add the 4 results.

Now, if someone can explain how ozo's perl statement does this then I'll be pleased to learn.

This indeed is a 1:1 mapping but it's not the only 1:1 mapping.
For example, one could have just as readily used 257^n as the base.
So then, 1.2.3.4 would convert to 17,107,466
and 64.55.66.223 would convert to 1,090,023,832.
The mapping wouldn't be contiguous any longer but that wasn't a specification until the examples were given OR if what the website does were taken as the only possibility.
0
 
rleyba828Author Commented:
Hi Ozo,

Thanks very much.  Brilliant one liner.   This is what I need and the output matches  what is computed in  --> http://www.ipaddressguide.com/ip

* Not being a PERL expert, I just need your help converting that statement into a function so i can do this:

mysql -u root -pmypass  mydatabase -e "UPDATE NETWORK SET IP_ADDRESS_DECIMAL = Somefunction(IP_ADDRESS);

Open in new window


Where IP_ADDRESS_DECIMAL and IP_ADDRESS are fields in my mysql table called NETWORK.

Hi fmarshall,

   The first half or your reply above was I think the formula being used to compute decimals equivalents, but for the purpose of my objectives, it should be good enough.

Thanks and regards.
0
 
Bernard S.CTOCommented:
- depending on your final objective, keeping the IP addresses as usual IP might in fact be the best solution: on every computer you can know its IP address as an IP, so what would really be the use of the conversion?

- you did not mention how these addresses are collected. if you real need a complete decimal value, collection time is probably the best time to make the conversion

- the mysql function to convert IP to decimal exists as a standard: INET_ATON()
 cf http://dev.mysql.com/doc/refman/5.5/en/miscellaneous-functions.html#function_inet-aton
0
 
rleyba828Author Commented:
Thanks Fibo,

   That function was exactly what I was looking for.   My SQL statement, which works now is something like this:

 mysql -uroot -ppassword  mydatabase  -e "UPDATE IP_ADDRESS_TABLE SET IP_DECIMAL=INET_ATON(IP_ADDRESS);"       

Open in new window

0
 
Bernard S.CTOCommented:
B-) Glad we could help. Thx for the grade and points.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 3
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now