4

I currently have a liking function on my images site that stores user IPs in the database against unique $imgids.

The IPs are currently stored as strings. To save space, I'd like to store the IPs not as strings with decimal points, but as 32-bit integers (1 bit per integer vs 1 byte per char in the string). I think this could save considerable space because I have the potential for n unique IPs to like x images...

So given string "109.181.156.221" that'd be a max of 12 bytes for the numbers, + 3 bytes per decimal point... so 15 bytes * 5000 IPs * 10 image IDs = 7.1 Mb

Versus 32bit 109181156221, 4 bytes * 5000 IPs * 100 image IDs = 2 Mb

So, before I inser the IP, I'd like to use a regex to remove decimals, and then store the IP as a number... "109.181.156.221" -> 109181156221 I'm new to Regexs, but I've tried this, but it won't work:

$ipINT = preg_replaceAll("\\.+$", "" , $ipString);

So my questions are:

1) Would the space savings even matter in a Mysql database? Is this worth the trouble?

2) Where am I off with my regex?

3) Would I be able to convert it back if I'm trying to read it?

Any thoughts?

Thanks!

user3871
  • 12,432
  • 33
  • 128
  • 268
  • 1
    Some info regarding IP database storage here: http://stackoverflow.com/questions/4982701/best-way-to-store-ip-in-database – showdev Feb 21 '13 at 17:51
  • 4
    so is the ip `192.168.0.1` which becomes `19216801` going to be able to be converted back? Given `19216801` can you tell if that is `19.21.68.01` or `192.16.80.1` or any other number of combinations. – Jonathan Kuhn Feb 21 '13 at 17:51
  • possible duplicate of [Most efficient way to store IP Address in MySQL](http://stackoverflow.com/questions/2542011/most-efficient-way-to-store-ip-address-in-mysql) – Quentin Feb 21 '13 at 17:53
  • @JonathanKuhn Not sure. I've appended that question to my list of questions. Any thoughts? – user3871 Feb 21 '13 at 17:54

4 Answers4

4

You can use ip2long(), then it should fit in an unsigned int column.

Josh
  • 12,448
  • 10
  • 74
  • 118
4

There are different ways to do this:

The right way:

By letting the database do the conversion for you. You have to store the ip in the database as INT(10) UNSIGNED and use INET_ATON & INET_NTOA:

SELECT INET_ATON("109.181.156.221"); // result 1840618717
SELECT INET_NTOA("1840618717"); // result 109.181.156.221

The alternative way:

By using PHP internal functions ip2long() & long2ip() and then store it in the DB:

$ipINT = ip2long('109.181.156.221'); // result 1840618717
$ip = long2ip('1840618717'); // result 109.181.156.221

The non-standard way:

By removing the dots and adding "0" if needed to be able to convert it back:

function ip2int($ip){
  $chunks = explode(".", $ip);
  $int = '';
  foreach($chunks as $chunk){
    $int .= str_pad($chunk, 3, '0', STR_PAD_LEFT);
  }
  return $int;
}

function int2ip($int){
  $chunks = str_split($int, 3);
  $c = count($chunks);
  $ip = ltrim($chunks[0], '0');
  for($i=1;$i<$c;$i++){
    $ip .= '.' . ltrim($chunks[$i], '0');
  }
  return($ip);
}

echo ip2int("109.1.156.5") . '<br>'; // result 109001156005
echo int2ip("109001156005"); // result 109.1.156.5

Fixing your RegEx:

$ip = "109.181.156.221";
$replace = preg_replace("/\./", "", $ip); // This will remove all the dots
echo $replace; // result 109181156221
HamZa
  • 14,671
  • 11
  • 54
  • 75
2

Use the ip2long() function to store IP addresses - Unsigned INT(10) should be great.

Use long2ip() to decode.

http://php.net/manual/en/function.ip2long.php

Your solution wouldn't work for IP Address like 1.123.123.123, as you wouldn't know where to restore the decimal point. The correct way to store an IP address would be with the method described above.

Prash
  • 1,915
  • 3
  • 20
  • 32
1

if you want to extract only digits you dont need regex you can just use:

filter_var('109.181.156.221', FILTER_SANITIZE_NUMBER_INT);

will give you 109181156221

but i dont think you would be able to convert it back to IP form.

I would store it with dots.

GGio
  • 7,563
  • 11
  • 44
  • 81