Archive for June, 2006


As an experiment I have created a full-featured drop down (slide down) menu in Actionscript – there are no movie clips, buttons, bitmaps, shapes – nothing. My library is empty! Check it out here The nice thing about this menu is that it does not have to stick out over the HTML since the submenu items appear under the menu item. I have tested it in IE, FireFox and Opera and it looks good.

I just ran across a problem where I needed to ignore/disable <br> tags. Here’s the situation – I use CSS to spice up my <code> tags in this blog, but when I add new entries it adds a <br /> after every line feed; this normally wouldn’t be a problem but I use ‘white-space: pre’ to keep my code indents. ‘white-space: pre’ is like using a <pre> tag for your element, so both line feeds and <br /> tags are converted to new lines. After searching on the net for a bit I realized that people are over complicating the answer – just change the line-height to 0px:

code br{
    line-height: 0px;
}

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!

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;
}