Page 1 of 1

Whats the key to decyphering the ip column in the ip4 table?

PostPosted: Thu Jun 01, 2006 7:53 pm
by kb3ien
I may be missing the obvious, but to what do the ip numbers correspond?

mysql> show tables;
+---------------+
| Tables_in_ip |
+---------------+
| cityByCountry |
| countries |
| ip4 |
+---------------+
3 rows in set (0.01 sec)

mysql> select * from ip4 order by ip desc limit 10;
+------------+---------+-------+---------------------+
| ip | country | city | cron |
+------------+---------+-------+---------------------+
| 4294967040 | 226 | 1310 | 2005-07-15 07:53:39 |
| 4067238400 | 226 | 136 | 2005-07-15 23:18:26 |
| 4028718080 | 226 | 78 | 2005-02-01 03:22:23 |
| 3884713472 | 105 | 12165 | 2005-07-25 14:49:03 |
| 3742866432 | 226 | 187 | 2005-09-30 02:32:10 |
| 3741318912 | 232 | 0 | 2004-12-12 22:14:48 |
| 3741318656 | 232 | 0 | 2004-12-12 22:14:48 |
| 3741318400 | 232 | 0 | 2004-12-12 22:14:48 |
| 3741318144 | 232 | 0 | 2004-12-12 22:14:48 |
| 3741317888 | 232 | 0 | 2004-12-12 22:14:48 |
+------------+---------+-------+---------------------+
10 rows in set (0.08 sec)

PostPosted: Mon Jun 05, 2006 8:01 am
by robocoder
The ip is the underlying 32-bit long for the common dotted decimal representation. Each ip in the table is the start of a /24 block.

For example, 4294967040 converts to 255.255.255.0. Its row in the (current) table represents the range: 255.255.255.0 to 255.255.255.255. Similarly, 3884713472 converts to 231.140.6.0, and its row represents the range: 231.140.6.0 to 231.140.6.255.

Converting from long to dotted decimal:
Code: Select all
#!/usr/bin/perl

sub long2ip {
  my($long) = @_;
  my $ip = '';

  if ($long =~ /^[0-9]+/) {
    $ip = sprintf("%d.%d.%d.%d",
                  ($long >> 24) & 0xff,
                  ($long >> 16) & 0xff,
                  ($long >> 8) & 0xff,
                  ($long) & 0xff);
  }
  return $ip;
}

$long = shift;
print &long2ip($long) . "\n";

Converting from dotted decimal to long:
Code: Select all
#!/usr/bin/perl

sub ip2long {
  my($ip) = @_;
  my $long = 0;

  if ($ip =~ /^([0-9]+)\.([0-9]+)\.([0-9]+)\.([0-9]+)/) {
    $long = ($1 << 24) | ($2 << 16) | ($3 << 8) | ($4);
  }
  return $long;
}

$ip = shift;
print &ip2long($ip) . "\n";

Soo...?

PostPosted: Wed Jan 03, 2007 7:09 pm
by levi
So, is there an updated version of the PHP which uses this form of the database schema, or do I need to translate the perl you've supplied into PHP and modify the 'ipRoughLocate' and 'ipLocate' methods myself? (I'm up to date with the latest code in subversion)

ip2long and long2ip

PostPosted: Tue Jan 09, 2007 3:44 am
by IvanGL
2 robocoder:
There is a easier way to transform ip to integer:

Code: Select all
#!/usr/bin/perl

sub ip2long
{
my $ip = shift;
return unpack('N',pack('C4', split(/\./, $ip)));
}

sub long2ip
{
my $int = shift;
return join('.',unpack('C4',pack('N',$int)));
}


In PHP functions ip2long and long2ip already exists:
http://php.net/manual/en/function.ip2long.php
http://php.net/manual/en/function.long2ip.php