FAQ - Frecuently Asked Questions

1- How to convert a normal IP Address (192.168.0.1) to IP Long/decimal (3232235521) Format?

An IP address (Internet Protocol address) is a unique number that devices use in order to identify and communicate with each other on a computer network utilizing the Internet Protocol standard (IP). IPv4 uses 32-bit (4 byte) addresses, which limits the address space to 4,294,967,296 (2^32) possible unique addresses.

Example of manual conversion:

A.B.C.D = D + (C * 256) + (B * 256 * 256) + (A * 256 * 256 * 256) =
192.168.0.1 = 1 + (0 * 256) + (168 * 256 * 256) + (192 * 256 * 256 * 256) =
3232235521

Example in ASP, converting IP dotted address to IP Long/decimal

function IP2Long(ipadr)
	result=0
	faktorer=split(ipadr,".")
	for ix=0 to 3
		expn=3-ix
	result=result + faktorer(ix) * 256 ^ expn
	next
	IP2Long=result
end function

Example in PHP, converting IP dotted address to IP Long/decimal

function IP2LONG($a){
	$d = 0.0;
	$b = explode(".", $a,4);
	for ($i = 0; $i < 4; $i++) {
		$d *= 256.0;
		$d += $b[$i];
	};
	return $d;
}

Example in PERL, converting IP dotted address to IP Long/decimal

sub IP2LONG {
	my $address = @_[0];
	($a, $b, $c, $d) = split '\.', $address;
	$decimal = $d + ($c * 256) + ($b * 256**2) + ($a * 256**3);
	return $decimal;
}

Example in VB.NET, converting IP dotted address to IP Long/decimal

Private Function IP2LONG(ByVal IPAddress As Object) As Object
	Dim x As Integer
	Dim Pos As Integer
	Dim PrevPos As Integer
	Dim Num As Integer
	If UBound(Split(IPAddress, ".")) = 3 Then
		' On Error Resume Next
		For x = 1 To 4
		Pos = InStr(PrevPos + 1, IPAddress, ".", 1)
		If x = 4 Then Pos = Len(IPAddress) + 1
		Num = Int(Mid(IPAddress, PrevPos + 1, Pos - PrevPos - 1))
		If Num > 255 Then
		ConvertToLong = "0"
		Exit Function
	End If
	PrevPos = Pos
	ConvertToLong = ((Num Mod 256) * (256 ^ (4 - x))) + ConvertToLong
		Next
	End If
End Function

Example in Coldfusion, converting IP dotted address to IP Long/decimal

<cfset MyList = #cgi.REMOTE_ADDR#>

<CFSET IP_a = #trim(ListGetAt(MyList,1,'.'))#>
<CFSET IP_b = #trim(ListGetAt(MyList,2,'.'))#>
<CFSET IP_c = #trim(ListGetAt(MyList,3,'.'))#>
<CFSET IP_d = #trim(ListGetAt(MyList,4,'.'))#>

<cfset RemoteIP = ( (#ip_d#) + (#ip_c# * 256) + (#ip_b# * 256 * 256) +
(#ip_a# * 256 * 256 * 256) ) >

2- How to convert a IP Long/decimal (3232235521) address to a normal IP address (192.168.0.1) Format?

Example in ASP, converting IP Long / decimal to IP dotted address

Function LONG2IP(ByVal asNewIP)
	Dim lnResults
	Dim lnIndex
	Dim lnIpAry
	lnIpAry = Split(asNewIP, ".", 4)
	For lnIndex = 0 To 3
	If Not lnIndex = 3 Then
		lnIpAry(lnIndex) = lnIpAry(lnIndex) * (256 ^ (3 - lnIndex))
	End If
	lnResults = lnResults + lnIpAry(lnIndex)
	Next
	CLngIP = lnResults
End Function

Example in PHP, converting IP Long / decimal to IP dotted address

function LONG2IP($a){
	$b=array(0,0,0,0);
	$c = 16777216.0;
	$a += 0.0;
	for ($i = 0; $i < 4; $i++) {
		$k = (int) ($a / $c);
		$a -= $c * $k;
		$b[$i]= $k;
		$c /=256.0;
	};
	$d=join('.', $b);
	return($d);
}

Example in PERL, converting IP Long / decimal to IP dotted address

sub dec2dot {
	my $address = @_[0];
	$d = $address % 256; $address -= $d; $address /= 256;
	$c = $address % 256; $address -= $c; $address /= 256;
	$b = $address % 256; $address -= $b; $address /= 256;
	$a = $address;
	$dotted="$a.$b.$c.$d";
	return $dotted;
}

3- How to create the database table

Creating the tables is your first step towards using any IPligence product, in case you are using mysql you can avoid this step by using the mysql-ready dump available for download, the dump will create any necessary tables for you.

We strongly recommend the use of Indexes for our BASIC and MAX products due to the quantity of records these data sets feature, the mysql examples shown bellow contain the index over the ip_to field. Using indexes correctly should benefit you and reduce the query time down to miliseconds.

Create table for IPligence LITE/COMMUNITY using MYSQL:

CREATE TABLE ipligence (
	ip_from int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
	ip_to int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
	country_code varchar(10) NOT NULL,
	country_name varchar(255) NOT NULL,
	continent_code varchar(10) NOT NULL,
	continent_name varchar(255) NOT NULL,
	PRIMARY KEY( ip_to)
);

Create table for IPligence BASIC using MYSQL:

CREATE TABLE ipligence (
	ip_from int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
	ip_to int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
	country_code varchar(10) NOT NULL,
	country_name varchar(255) NOT NULL,
	continent_code varchar(10) NOT NULL,
	continent_name varchar(255) NOT NULL,
	time_zone varchar(10) NOT NULL,
	region_code varchar(10) NOT NULL,
	region_name varchar(255) NOT NULL,
	owner varchar(255) NOT NULL,
	PRIMARY KEY( ip_to)
);

Create table for IPligence MAX using MYSQL:

CREATE TABLE ipligence (
	ip_from int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
	ip_to int UNSIGNED ZEROFILL NOT NULL DEFAULT '0000000000',
	country_code varchar(10) NOT NULL,
	country_name varchar(255) NOT NULL,
	continent_code varchar(10) NOT NULL,
	continent_name varchar(255) NOT NULL,
	time_zone varchar(10) NOT NULL,
	region_code varchar(10) NOT NULL,
	region_name varchar(255) NOT NULL,
	owner varchar(255) NOT NULL,
	city_name varchar(255) NOT NULL,
	county_name varchar(255) NOT NULL,
	latitude double NOT NULL,
	longitude double NOT NULL,
	PRIMARY KEY( ip_to)
);

Create table for IPligence LITE/COMMUNITY using Microsoft SQL:

CREATE TABLE [dbo].[ipligence](
	[ip_from] NUMERIC(11) NOT NULL,
	[ip_to] NUMERIC(11) NOT NULL,
	[country_code] NVARCHAR(10) NOT NULL,
	[country_name] NVARCHAR(255) NOT NULL,
	[continent_code] NVARCHAR(10) NOT NULL,
	[continent_name] NVARCHAR(255) NOT NULL
);

Create table for IPligence BASIC using Microsoft SQL:

CREATE TABLE [dbo].[ipligence] (
	[ip_from] NUMERIC(11) NOT NULL,
	[ip_to] NUMERIC(11) NOT NULL,
	[country_code] NVARCHAR(10) NOT NULL,
	[country_name] NVARCHAR(255) NOT NULL,
	[continent_code] NVARCHAR(10) NOT NULL,
	[continent_name] NVARCHAR(255) NOT NULL,
	[time_zone] NVARCHAR(10) NOT NULL,
	[region_code] NVARCHAR(10) NOT NULL,
	[region_name] NVARCHAR(255) NOT NULL,
	[owner] NVARCHAR(255) NOT NULL
);

Create table for IPligence MAX using Microsoft SQL:

CREATE TABLE [dbo].[ipligence] (
	[ip_from] NUMERIC(11) NOT NULL,
	[ip_to] NUMERIC(11) NOT NULL,
	[country_code] NVARCHAR(10) NOT NULL,
	[country_name] NVARCHAR(255) NOT NULL,
	[continent_code] NVARCHAR(10) NOT NULL,
	[continent_name] NVARCHAR(255) NOT NULL,
	[time_zone] NVARCHAR(10) NOT NULL,
	[region_code] NVARCHAR(10) NOT NULL,
	[region_name] NVARCHAR(255) NOT NULL,
	[owner] NVARCHAR(255) NOT NULL,
	[city_name] NVARCHAR(255) NOT NULL,
	[county_name] NVARCHAR(255) NOT NULL,
	[latitude] FLOAT NOT NULL,
	[longitude] FLOAT NOT NULL
);

Create table for IPligence LITE/COMMUNITY using ORACLE:

CREATE TABLE ipligence (
	ip_from NUMBER NOT NULL,
	ip_to int NUMBER NOT NULL,
	country_code varchar(10) NOT NULL,
	country_name varchar(255) NOT NULL,
	continent_code varchar(10) NOT NULL,
	continent_name varchar(255) NOT NULL
);

Create table for IPligence BASIC using ORACLE:

CREATE TABLE ipligence (
	ip_from NUMBER NOT NULL,
	ip_to int NUMBER NOT NULL,
	country_code varchar(10) NOT NULL,
	country_name varchar(255) NOT NULL,
	continent_code varchar(10) NOT NULL,
	continent_name varchar(255) NOT NULL,
	time_zone varchar(10) NOT NULL,
	region_code varchar(10) NOT NULL,
	region_name varchar(255) NOT NULL,
	owner varchar(255) NOT NULL
);

Create table for IPligence MAX using ORACLE:

CREATE TABLE ipligence (
	ip_from NUMBER NOT NULL,
	ip_to int NUMBER NOT NULL,
	country_code varchar(10) NOT NULL,
	country_name varchar(255) NOT NULL,
	continent_code varchar(10) NOT NULL,
	continent_name varchar(255) NOT NULL,
	time_zone varchar(10) NOT NULL,
	region_code varchar(10) NOT NULL,
	region_name varchar(255) NOT NULL,
	owner varchar(255) NOT NULL,
	city_name varchar(255) NOT NULL,
	county_name varchar(255) NOT NULL,
	latitude NUMBER NOT NULL,
	longitude NUMBER NOT NULL
);

4- How to import the data into the database

All IPligence products are available in two downloadable formats, CSV (comma separated values) and MYSQL DUMP.

If you are using Microsoft SQL you should be able to use the SQL Server import wizard available from tools->Import Data. We strongly recommend reading our Microsoft SQL step by step guide on how successfully importing the data, you can download this guide in PDF format here: https://www.ipligence.com/pdf/howto-mssql2000.pdf

If you are using Oracle, please reffer to the following FAQ, https://www.orafaq.com/faqloadr.htm

For Mysql users, you can choose either to use the LOAD DATA INFILE command ( LOAD DATA INFILE "/path to file.csv" INTO TABLE ipligence FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';) , (https://dev.mysql.com/doc/refman/5.1/en/load-data.html).

Or by using the Mysql specific dump file and issuing the following shell command "mysql -u user -p db_name < backup-file.sql"
5- How to use and query the database

We want to know the owner, latitude, longitude, city_name,coutry_name details for the ip address 209.132.177.50, after converting the ipaddress to IP Number/Decimal with any of the methods described above (question #1) the result is 3515134258, we have now to query the database for the fields ip_from and ip_to, where ip_from is equal or bigger than the ipaddress and ip_to equal or lower than the ip address.

This translates to a query like this one (example in mysql format):
```
SELECT owner,latitude,longitude,city_name,country_name FROM ipligence
WHERE ip_from <= '3515134258' and ip_to >= '3515134258' LIMIT 1;
```
As result the dataset in IPligence Max gives us back the following:
```
+------------------+----------+-----------+-----------+---------------+
| owner | latitude | longitude | city_name | country_name |
+------------------+----------+-----------+-----------+---------------+
| RED HAT SOFTWARE | 33.4481 | -112.0732 | PHOENIX | UNITED STATES |
+------------------+----------+-----------+-----------+---------------+
1 row in set (0.00 sec)
```
Alternatively, you can take advantage of the sql function INET_ATON(), which translates the IP address to IP Number/Decimal for you, check if your SQL database supports such function.

The query using INET_ATON() looks like this:
```
SELECT owner,latitude,longitude,city_name,country_name FROM ipligence
WHERE ip_from <= INET_ATON('209.132.177.50') and ip_to >=
INET_ATON('209.132.177.50') LIMIT 1;
```
You can also use INET_NTOA() to translate a IP Number/Decimal to a normal IP address dotted format:
```
select INET_NTOA('3515134258');
```
The result is:
```
+-------------------------+
| INET_NTOA('3515134258') |
+-------------------------+
| 209.132.177.50 |
+-------------------------+
1 row in set (0.00 sec)						
```

IPligence

FAQ - Frecuently Asked Questions

1- How to convert a normal IP Address (192.168.0.1) to IP Long/decimal (3232235521) Format?

2- How to convert a IP Long/decimal (3232235521) address to a normal IP address (192.168.0.1) Format?

3- How to create the database table

4- How to import the data into the database

5- How to use and query the database