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

[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)

[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";


[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)

[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