[Original] solutions for IP operation in the database

[Original] solutions for IP operation in the database

Suitable for display output IP by IP range query.

A SQL statement to the database the IP format characters in the form of an integer stored interchangeable

IP, based on the four bytes of storage, maximum FF.FF.FF.FF bit dotted decimal to 255.255.255.255. We can understand that this is an integer and stored in the database, thus reducing the dotted decimal character in the form of storage redundancy, but also conducive to a range query, the database is designed so that is a good habit.

Integer in dotted decimal:

       PHP and MySQL for example, the idea is: by inspection IP, found that four per person is 256 229, so that we can mold (MOD) operations Dou you remove and plus display dotted decimal.

       We can write a function

function sql_long_to_ip ($ colum1) 
{ 
return “CONCAT (((“. $ colum1. “-8388608) / 16777216)% 256, ‘.’, ((“. $ colum1. “-32768) / 65536)% 256, ‘.’, ((“. $ colum1. “-128) / 256)% 256, ‘.’, (“. $ colum1. “)% 256) AS”. $ colum1. “”; 
}

              Which $ colum1 is the integer IP storage field

       I took a look at the concept of this function:

              This function returns a string that is converted to dotted decimal string string integer IP. The input parameters cosmetic IP column name. The use of methods such as: $ strSQL = ‘SELECT’ sql_long_to_ip (‘lip’) ‘FROM IP_TEST,;’ Then $ strSq the execution displayed in dotted decimal format.

 

Dotted decimal into IP:

       Because the database is an integer stored in the query needs to be entered by the user in dotted decimal into integer. I have already said, each is 256 decimal, so each shift or multiply the 256 power it wants, but the Web this operation pressure, so a similar split function for each demolition points, then converted to hexadecimal each inlet connected to the form of 0XFFFFFFFF the form of characters added to the sql statement it wants to.

       Here have a Liezi, but did not use the SPLIT function, because then I did not expect to use this function: P.

/ / IP conversion value format: 0.0.0.0

the function ip_to_long ($ ip)

{

       if (strlen ($ ip)> 0)

       {

              $ Ip_array = explode (“.”, $ Ip);;

 

              if (count ($ ip_array)! = 4)

                     return 0;

       }

       else

       {

              return 0;

       }

 

       $ Start = 0;

       $ Len = strpos ($ ip, ‘.’) – $ Start;

       $ Ip1 = trim (substr ($ ip, $ start, $ len)) +0;

 

       $ Start + = $ len +1;

       $ Len = strpos ($ ip, ‘.’, $ Start) – $ start;

       $ Ip2 = trim (substr ($ ip, $ start, $ len)) +0;

 

       $ Start + = $ len +1;

       $ Len = strpos ($ ip, ‘.’, $ Start) – $ start;

       $ Ip3 = trim (substr ($ ip, $ start, $ len)) +0;

 

       $ Start + = $ len +1;

       $ Len = strlen ($ ip) – $ start;

       $ Ip4 = trim (substr ($ ip, $ start, $ len)) +0;

 

       $ Ip1 = base_convert ($ ip1, 10, 16);

       $ Ip2 = base_convert ($ ip2, 10, 16);

       $ Ip3 = base_convert ($ ip3, 10, 16);

       $ Ip4 = base_convert ($ ip4, 10, 16);

 

       (Strlen ($ ip1) <2)? ($ Ip1 = “0”. $ Ip1): ($ ip1);

       (Strlen ($ ip2) <2)? ($ Ip2 = “0”. $ Ip2): ($ ip2);

       (Strlen ($ IP3) <2)? A ($ ip3 = “0”. $ IP3): $ (IP3);

       (Strlen ($ ip4) <2)? ($ Ip4 = “0”. $ Ip4): ($ ip4);

 

       return ‘0 x ‘. $ ip1. $ ip2. $ ip3. $ ip4;

}

In this connection, it has been integer IP field to query and display.