FRIHOST FORUMS SEARCH FAQ TOS BLOGS COMPETITIONS
You are invited to Log in or Register a free Frihost Account!


Storing IPs in mysql





imagefree
what about the idea of storing IPs in mysql in the following way. I think it will optimize the space usage and increase searching speed.

Code:

function encode_ip($ip)
{
   list($ips[0], $ips[1],$ips[2],$ips[3]) = explode('.',$ip);
   foreach($ips as $key => $value)
   {
      $e_string .= substr(base64_encode(chr($value)) , 0 , 2 );
      //base64_encode 0 - 255 returns 4 characters each, and at the end of each encoded string, there is a '==', it can be handcodded later, so i am removing it.
   }
   return $e_string;
}
function decode_ip($string)
{
   $ip[0]=ord(base64_decode($string[0].$string[1].'=='));
   $ip[1]=ord(base64_decode($string[2].$string[3].'=='));
   $ip[2]=ord(base64_decode($string[4].$string[5].'=='));
   $ip[3]=ord(base64_decode($string[6].$string[7].'=='));
   return $ip[3].'.'.$ip[2].'.'.$ip[1].'.'.$ip[0];
}
$ip='117.102.13.168';
echo 'Actual IP:&nbsp;&nbsp;&nbsp;'.htmlentities($ip).'<br />';  //remove htmlentities()
echo 'Encoded IP:&nbsp;&nbsp;'.encode_ip($ip).'<br />';
// for extreme safety (before INSERTing IPs in mysql, remember to apply mysql_real_escape_string() after encoding.
echo 'Decoded IP:&nbsp;&nbsp;'.decode_ip(encode_ip($ip)).'<br />';


Benifits:

Each IP will require 8 bytes of space (otherwise 1 IP requires 15 bytes).
base64_encode() makes the saving of IPs in mysql safe.
Searching is expected to be much much faster if this sys is used. (i think).

I seriously need your suggestions in this regard about the usefulness and reliability of this code, and also about the better alternatives.
djclue917
Honestly, the code looks very inefficient, even just by inspection. I see some string concatenation, loops, calls to base64_decode and ord, list and explode, etc. I don't think you need to store the IP as a string.

Storing it as a long integer is a better approach, in my opinion, since it's like the most efficient way of doing so. Also, you can use built-in MySQL functions in converting from long to IP and vice versa (as far as I can remember). You can also do the conversion in PHP via ip2long (IP to long integer) and long2ip (long integer to IP). I prefer and use the latter approach. That is, via PHP.

http://www.php.net/ip2long
http://www.php.net/long2ip

I hope this helps...
Horizon
Just for in-case purposes, you can call forth the users IP address using:

$_SERVER['REMOTE_ADDR'];

and using variables to shorten it:

$ip = $_SERVER['REMOTE_ADDR'];
ftv_flung
Why bother when, as Horizon said, you can just echo it with $_SERVER['REMOTE_ADDR']; ??

And then just INSERT into the MySQL db Cool
Horizon
Unfortunatly, that won't encode it. But there are plenty of alternatives for encoding, none of which I know xD.
AftershockVibe
Storing it as an integer is the way to go. After that you can tell your database engine to index the IP field and all the optimisation for searching is done for you, by people whose job it is to get it right.
djclue917
ftv_flung wrote:
Why bother when, as Horizon said, you can just echo it with $_SERVER['REMOTE_ADDR']; ??

And then just INSERT into the MySQL db Cool


The issue is not the way of obtaining the IP address itself but how to efficiently and, I assume, elegantly store an IP address in MySQL. From what I see, the poster suggested a way of encoding the IP address into another form, but like I said, why bother encoding an IP address using such complex algorithms?

An IP address is composed of four octets which means: 4 x 8 bits = 32 bits. This means that all IP addresses can be represented by positive integers (actually, there can be negative numbers too) up to 2^32 - 1. With that in mind, one can easily do a very fast and efficient conversion from the dotted-decimal notation to the long integer representation and vice versa. This is easily accomplished by using built-in PHP and/or MySQL functions.
Hogwarts
Indeed. You'll definitely not get faster/smaller than djclue917's suggestion of storing it in an integer. An IP at the lowest level is 32 bits long. Any shorter than that and you'd need to be using a repetitive-compression algorithm or somehow losing half of those in transit

And whilst we're going on about this topic, let me point out that this is realllly simple to change to if you just modify your model layer slightly =]
rohan2kool
I don't understand why an IP requires 15 bytes to store? An IP address should not be saved as a string. Eventually, it is a 32-bit integer (for IPv4, IPv6 address are 64-bit), so 4 bytes is the exact amount that needs to be used. The code to convert it to integer would be:

Code:

function getIntegerIP($stringIP) {
    $intIP = 0;
    $_IPparts = explode(".", $stringIP);
    $size = count($_IPparts);
    for($i=0; $i<$size; $i++) {
        $num =  $_IPparts[($size-1)-$i] + 0;
        $intIP += $num*pow(256, $i);
    }

    return $intIP;
}
Hogwarts
Why not just use the (built-in) functions ip2long and long2ip? Wink

Anyhow, you've just repeated half of what I said Razz
imagefree
rohan2kool wrote:
I don't understand why an IP requires 15 bytes to store? An IP address should not be saved as a string. Eventually, it is a 32-bit integer (for IPv4, IPv6 address are 64-bit), so 4 bytes is the exact amount that needs to be used. The code to convert it to integer would be:

Code:

function getIntegerIP($stringIP) {
    $intIP = 0;
    $_IPparts = explode(".", $stringIP);
    $size = count($_IPparts);
    for($i=0; $i<$size; $i++) {
        $num =  $_IPparts[($size-1)-$i] + 0;
        $intIP += $num*pow(256, $i);
    }

    return $intIP;
}

how to convert IPv6 to long?
Hogwarts
How not? It's just base16 (hexadecimal) instead of base10 (decimal)

Just base convert it and use a modified ip2long
imagefree
I have to clear 2 things:
IPv6 is 128 Bit and
PHPbb 3 is using 40 bytes field size for saving IP. Means its taking an IP address as String (and IPv6 takes 39 characters max).
Related topics
mysql connection question
php admin and mysql admin console
Mysql And PHP HELP PLZ
Do you use a CMS script?
Question about MySQL!
PHP, MySQL...
What is the best way to save a date/timestamp in mysql
mysql gives the wrong result [solved]
question on vb language and mysql...
Storing and handling image in mysql.
Images in MySQL
MySQL dates
Storing MySQL connection data securely and conveniently
CMS without MYSQL
Reply to topic    Frihost Forum Index -> Scripting -> Php and MySQL

FRIHOST HOME | FAQ | TOS | ABOUT US | CONTACT US | SITE MAP
© 2005-2011 Frihost, forums powered by phpBB.