Menu
Possible Duplicate:
How to store an IP in mySQL
I want to get the IP address from $_SERVER['REMOTE_ADDR'] and some other $_SERVER variables, which datatype is the right one for this?
Is it VARCHAR(n)?
699
We can store an IP address with the help of INT unsigned. While using INSERT, include INET_ATON() and with SELECT, include INET_NTOA().
IP Network Address Data Types. IPV4 and IPV6 are abstract data types that store IPv4 and IPv6 host addresses, respectively, in binary format. IPV4 is a 4-byte host address in dotted-decimal notation (four decimal numbers, each ranging from 0 to 255, separated by dots).
select host from information_schema. processlist WHERE ID=connection_id(); Will give you the host name . You will get IP address( like 192.168.
Hostname: The host name or IP address of the MySQL server. The host name "localhost" might resolve to "127.0. 0.1" or "::1" on your host, so note this when checking permissions.
Since IPv4 addresses are 4 byte long, you could use an INT (UNSIGNED) that has exactly 4 bytes:
`ipv4` INT UNSIGNED And INET_ATON and INET_NTOA to convert them:
INSERT INTO `table` (`ipv4`) VALUES (INET_ATON("127.0.0.1")); SELECT INET_NTOA(`ipv4`) FROM `table`; For IPv6 addresses you could use a BINARY instead:
`ipv6` BINARY(16) And use PHP’s inet_pton and inet_ntop for conversion:
'INSERT INTO `table` (`ipv6`) VALUES ("'.mysqli_real_escape_string(inet_pton('2001:4860:a005::68')).'")' 'SELECT `ipv6` FROM `table`' $ipv6 = inet_pton($row['ipv6']); You have two possibilities (for an IPv4 address) :
varchar(15), if your want to store the IP address as a string 192.128.0.15 for instanceinteger (4 bytes), if you convert the IP address to an integer 3229614095 for the IP I used before
The second solution will require less space in the database, and is probably a better choice, even if it implies a bit of manipulations when storing and retrieving the data (converting it from/to a string).
About those manipulations, see the ip2long() and long2ip() functions, on the PHP-side, or inet_aton() and inet_ntoa() on the MySQL-side.
32
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
