NickSoft
Site Admin
Joined: 13 Nov 2006
Posts: 22
|
 Posted: Wed May 09, 2007 5:40 pm Post subject: Storing ips in database as hexadecimal strings |
 |
|
PHPBB2 stores ips as hexadecimal strings. php function that could convert ip to phpbb2 ip string is this:
| Code: | | echo sprintf('%08X',ip2long('192.168.0.1'));//result: C0A80001 |
converting back hexadecimal IP to dot style with php can be done with base_convert function:
| Code: | | echo long2ip(base_convert("C0A80001",16,10)); //result: 192.168.0.1 |
This way IPs can be indexed better and you can search for class A, B and C networks. For example to see all ips from 192.168.0.0/24 network you could use this query:
| Code: | | select * from users where ip like 'C0A800%'; |
if you need to convert hexadecimal to dot style ips within mysql client or phpMyAdmin you can define a function hex2ip (for mysql 5.x):
| Code: | DELIMITER ;;
DROP FUNCTION IF EXISTS hex2ip;;
CREATE FUNCTION hex2ip (x VARCHAR(8))
RETURNS varchar(15)
DETERMINISTIC NO SQL
BEGIN
DECLARE x1 varchar(2);
DECLARE x2 varchar(2);
DECLARE x3 varchar(2);
DECLARE x4 varchar(2);
set x1=substring(x,1,2);
set x2=substring(x,3,2);
set x3=substring(x,5,2);
set x4=substring(x,7,2);
RETURN concat(conv(x1,16,10),'.',conv(x2,16,10),'.',conv(x3,16,10),'.',conv(x4,16,10));
END;;
DELIMITER ; |
Then select ip from users table:
| Code: | | select ID,hex2ip(IP) from users; |
|
|
FAQ
Search
Register
Profile
Log in to check your private messages
Log in
