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:

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:

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:

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

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:

SELECT ID,hex2ip(IP) FROM users;