Reading Time: 4 minutes

19,862 Views

In MySQL there are several types of Data Types. Each data type is responsible to handle each respective value. MySQL stores various data, it includes string values, integer values, date and time, blob for images, enum for options etc.

Inside this article we will see Which DataType stores IP Address in MySQL. DataType in MySQL is used to store different different types of data.

Learn More –

Let’s get started.

IP Address Structure

The IP address is a 32-bit number that uniquely identifies a network interface on a machine. An IP address is typically written in decimal digits, formatted as four 8-bit fields separated by periods. Each 8-bit field represents a byte of the IP address.

Data Type in MySQL – IP Address

To store IP address in MySQL, use integer data type. Using INT data type we can save more space in database.

CREATE TABLE `ip_addresses` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `ip_address` int(4) unsigned NOT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

As you can see we have taken int(4) unsigned to store IP address.

Let’s see an example how can we do any row insertion into this table.

Example

INSERT INTO `ip_addresses` (`ip_address`) VALUES (INET_ATON("127.0.0.1"));

What is INET_ATON ?

INET_ATON is a MySQL function. This method is used to convert an ip address to number.

In the above image we can see the inserted IP address is now converted into a number format. This is magic done by INET_ATON MySQL function.

This function remove dots(.) from IP address value and store.

How can we access IP address from Database ?

To access the IP address into it’s format means with 3 dots(.), we need to use INET_NTOA. INET_NTOA is a MySQL function which converts number to ip address.

Example

SELECT id, INET_NTOA(ip_address) as ip FROM ip_addresses

This is the use of INET_ATON and INET_NTOA mysql functions.

PHP Functions For IP Address

If we are looking for PHP functions which works same as MySQL functions for IP address.

PHP Functions

The ip2long() and long2ip() functions

Note : You should use those for IPv4 addresses. Please make sure, $_SERVER[‘REMOTE_ADDR’] actually contains a valid IPv4 address (and not some IPv6-stuff).

Example

var_dump(ip2long('123.63.153.253'));
var_dump(long2ip(2067765757));

Output

int(2067765757)
string(14) "123.63.153.253"

We hope this article helped you to learn about i.e Which DataType Stores IP Address In MySQL Tutorial in a very detailed way.

Online Web Tutor invites you to try Skillshike! Learn CakePHP, Laravel, CodeIgniter, Node Js, MySQL, Authentication, RESTful Web Services, etc into a depth level. Master the Coding Skills to Become an Expert in PHP Web Development. So, Search your favourite course and enroll now.

If you liked this article, then please subscribe to our YouTube Channel for PHP & it’s framework, WordPress, Node Js video tutorials. You can also find us on Twitter and Facebook.