calculate distance

[patrickstrijdonck]

Hi all,

Im trying to calculate distance between 2 postalcodes with Google maps API

/** 
 * GETCOORD 
 * Uses Google Maps to resolve the coordinates of a postal code 
 */ 

function getCoord($postcode) 
{ 
    // Je google api key 
    $apikey = 'ABQIAAAAeOni3x2kczXhabinaZCOKhTeAW5jAnA4Ui_xih8dHcjFOdLV4BQ01HPEcs1LdFhlniCpPP0-zQj3cw'; 

    $xml = simplexml_load_file('[URL unfurl="true"]http://maps.google.com/maps/geo?q='[/URL] . $postcode . '%20Netherlands&output=xml&key=' . $apikey); 
// Of voor 'n nederlandse vertaling van je plaats namen 
// $xml = simplexml_load_file('[URL unfurl="true"]http://maps.google.nl/maps/geo?q='[/URL] . $postcode . '%20Nederland&output=xml&key=' . $apikey); 
    if ($xml && $xml->Response->Status->code == '200') { 
        $adres = $xml->Response->Placemark->address; 
        list($lng, $lat) = explode(',', (string) $xml->Response->Placemark->Point->coordinates); 
        // print_r($xml); 
        return array('Lat' => (float) $lat, 'Lng' => (float) $lng, 'Adres' => (string) $adres);  
    } 
    return false; 
} 

/** 
 * CALCDISTANCE 
 * Calculates the distance between to postal codes 
 */ 
function calcDistance($postal1, $postal2) 
{ 
    $dst1 = getCoord($postal1); 
    // evt 1 fixed eigen coordinaat 
    // Bijv Paleis Soestdijk 3744AA Baarn 
    // $dst1 = array('Lat' => 52.198269, 'Lng' => 5.278128, 'Adres' =>  'Baarn'); 
    $dst2 = getCoord($postal2); 

    if (!$dst1 or !$dst2) 
        return false; // Invalid postal codes 

    $km = rad2deg(acos(sin(deg2rad($dst1['Lat'])) * sin(deg2rad($dst2['Lat'])) +   
        cos(deg2rad($dst1['Lat'])) * cos(deg2rad($dst2['Lat'])) *  
        cos(deg2rad($dst1['Lng'] - $dst2['Lng'])))) * 60 * 1.1515 * 1.609344;  

    return array($dst1['Adres'], $dst2['Adres'], number_format($km, 2, '.', '')); 
}

when a user presses SUBMIT, this code will run:

$query1="UPDATE personeel SET cursus = '$cursusnr' WHERE calcDistance('postcode', '$cursuspostcode')";
mysql_query($query1) or die(mysql_error());

Now im trying to get that function in the WHERE condition to work, im getting the following Error

FUNCTION cmict_bhv.calcDistance does not exist

But the Function isnt called cmict_bhc.calcDistance
but its named calcDistance

ALSO

WHERE calcDistance('--->>>postcode<<<---', '$cursuspostcode')

--->>>postcode<<<--- must be a field in the same table as personeel, so when the user presses SUBMIT, the SQL must look trough every record in the table "personeel" and look for every "user" wich postalcode can be calculated,

so if the first record has a postalcode of 1506XW
the WHERE condition must be like:

WHERE calcDistance('1506XW', '$cursuspostcode')

but i havnt managed to do that

can someone help me with this?

 

[jpadie]

you cannot use a php function inside a SQL string (other than in sqlite - see UDF's).

$query1="UPDATE personeel SET cursus = '$cursusnr' WHERE [red]" . [/red]calcDistance('postcode', '$cursuspostcode');

also calcDistance would need to return a string for use in the mysql query. as it stands it returns an array.

 

[lgarner]

You're trying to call a PHP function from MySQL. You need to call the PHP function and then put the results into your SQL query.