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