I’ve seen a lot of people trying to store IP Addresses in MySQL VARCHAR fields – this is very inefficient! There are two common ways to store ips that are database friendly: as a hexidecimal number and as a long integer. I recommend the long integer method since this functionality is already implemented in PHP. Here’s how it’s done:

1. Make a column in MySQL to store the ip address. Give it the type INT(11).

2. Get the client’s IP Address. I use this function to both retrieve and validate the client’s IP:

function getip() {
   if (validip($_SERVER["HTTP_CLIENT_IP"])) {
       return $_SERVER["HTTP_CLIENT_IP"];
   }
   foreach (explode(",",$_SERVER["HTTP_X_FORWARDED_FOR"]) as $ip) {
       if (validip(trim($ip))) {
           return $ip;
       }
   }
   if (validip($_SERVER["HTTP_PC_REMOTE_ADDR"])) {
        return $_SERVER["HTTP_PC_REMOTE_ADDR"];
   } elseif (validip($_SERVER["HTTP_X_FORWARDED"])) {
       return $_SERVER["HTTP_X_FORWARDED"];
   } elseif (validip($_SERVER["HTTP_FORWARDED_FOR"])) {
       return $_SERVER["HTTP_FORWARDED_FOR"];
   } elseif (validip($_SERVER["HTTP_FORWARDED"])) {
       return $_SERVER["HTTP_FORWARDED"];
   } else {
       return $_SERVER["REMOTE_ADDR"];
   }
}
function validip($ip) {
   if (!empty($ip) && ip2long($ip)!=-1) {
       $reserved_ips = array (
       array('0.0.0.0','2.255.255.255'),
       array('10.0.0.0','10.255.255.255'),
       array('127.0.0.0','127.255.255.255'),
       array('169.254.0.0','169.254.255.255'),
       array('172.16.0.0','172.31.255.255'),
       array('192.0.2.0','192.0.2.255'),
       array('192.168.0.0','192.168.255.255'),
       array('255.255.255.0','255.255.255.255')
       );

       foreach ($reserved_ips as $r) {
           $min = ip2long($r[0]);
           $max = ip2long($r[1]);
           if ((ip2long($ip) >= $min) && (ip2long($ip) <= $max)) return false;
       }
       return true;
   } else {
       return false;
   }
}

3. Store the IP in the database:

$ip = getip();
$longip = ip2long($ip);
$query = sprintf("INSERT INTO table (ipaddr) VALUES (%s)",$longip);
@mysql_query($query, $link) or die("Error inserting record: ".mysql_error());
if(mysql_affected_rows() != 1){
  //nothing was inserted
}else{
  //1 row was inserted
}

4. Retrieve the IP later (I’ll sort them descending)

$res = @mysql_query("SELECT * FROM table ORDER BY ipaddr DESC")
or die("Error selecting records".mysql_error());
while($row = mysql_fetch_assoc($res)){
   $ip = long2ip($row['ipaddr']);
   echo "IP: $ip<br />";
}

That’s it! It’s easy, huh? Now you can sleep well knowing you’re MySQL server doesn’t hate you!

« »