Storing IP Addresses in MySQL with PHP

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:

Make a column in MySQL to store the ip address. Give it the type INT(11).
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;
    }
}

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
}

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!

Menu