Category: MySQL


Storing IP Addresses in MySQL with PHP

Filed Under: MySQL, PHP by Steve — Leave a comment
June 6, 2006

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!

Comment

MySQL 5 Stored Procedures – Practical Example

Filed Under: MySQL, PHP by Steve — 1 Comment
June 6, 2006

I have come up with a practical use for MySQL Stored Procedures and developed a very useful example for the sceptics. The following is a MySQL SP that calculates the distance between two ZIP codes – all you pass it is the zip codes! This code assumes you have a database of US ZIP Codes with their Longitudes and Latitudes.

——————–MySQL CODE————————-

DELIMITER //
CREATE PROCEDURE `zipDist`(zipA INT, zipB INT)
BEGIN
 DECLARE latA DECIMAL(10,6);
 DECLARE lonA DECIMAL(10,6);
 DECLARE latB DECIMAL(10,6);
 DECLARE lonB DECIMAL(10,6);
 SELECT latitude, longitude INTO latA, lonA FROM zipcodes WHERE zip=zipA;
 SELECT latitude, longitude INTO latB, lonB FROM zipcodes WHERE zip=zipB;
 SELECT ACOS(SIN(RADIANS(latA)) * SIN(RADIANS(latB))  + COS(RADIANS(latA))
     * COS(RADIANS(latB))  * COS(RADIANS(lonB) - RADIANS(lonA)))
     * 3956 AS distance;
END//
DELIMITER ;

Using this stored procedure in your code will save you 2 MySQL queries and a little bit of math in your code.
The following is a stored procedure to find all ZIP codes within a given redius, plus a bit of PHP5 code that calls the procedure using the mysqli extension.

——————–MySQL CODE————————-

DELIMITER //
CREATE PROCEDURE `zipRad`(inZip INT, radius INT)
BEGIN
    DECLARE lat DOUBLE;
    DECLARE lon DOUBLE;
    SELECT latitude, longitude INTO lat, lon FROM zipcodes WHERE zip = inZip;
    SELECT zip FROM zipcodes WHERE (POW((69.1*(longitude - lon)
        *cos( lat /57.3)),2)+POW((69.1*(latitude - lat)),2))<(radius * radius);
END//
DELIMITER ;

———————PHP Code————————–

function zipRadius2($zip,$radius){
    $zipcon2 = mysqli_connect('127.0.0.1',"zipuser","zippass","zipdb")
        or die("Could not connect to DB: ".mysqli_connect_error());
    if($radius < 1 || $radius > 1000){return(false);}
    $query = "CALL zipRad($zip, $radius)";
    $result = mysqli_query($zipcon2, $query) or die(mysqli_error());
    $num = mysqli_num_rows($result);
    $i = 0;
    if($num != 0) {
        while($row = mysqli_fetch_assoc($result)) {
            $zipArray[$i] = $row["zip"];
            $i++;
        }
    }
 return $zipArray;
}
Comment