What's X

DELIMITER $$

CREATE FUNCTION uFn_CalcuDistance(lat1 NUMERIC(20,8),lng1 NUMERIC(20,8),lat2 NUMERIC(20,8),lng2 NUMERIC(20,8),unit VARCHAR(2))

RETURNS NUMERIC(20,8)

BEGIN

/*

使用範例：

SELECT uFn_CalcuDistance(5.075990,34.888294,5.075800,34.888400,'m')

*/

DECLARE pi80 FLOAT;

DECLARE r FLOAT;

DECLARE dlat FLOAT;

DECLARE dlng FLOAT;

DECLARE a FLOAT;

DECLARE c FLOAT;

DECLARE km NUMERIC(20,10);

DECLARE _lat1 FLOAT;

DECLARE _lng1 FLOAT;

DECLARE _lat2 FLOAT;

DECLARE _lng2 FLOAT;

SET pi80 = PI() / 180;

SET _lat1 = lat1 * pi80;

SET _lng1 = lng1 * pi80;

SET _lat2 = lat2 * pi80;

SET _lng2 = lng2 * pi80;

SET r = 6372.797;

SET dlat = _lat2 - _lat1;

SET dlng = _lng2 - _lng1;

SET a = sin(dlat / 2) * sin(dlat / 2) + cos(_lat1) * cos(_lat2) * sin(dlng / 2) * sin(dlng / 2);

SET c = 2 * atan2(sqrt(a), sqrt(1 - a));

SET km = r * c;

IF (unit='m') THEN

RETURN (km * 1000);

ELSEIF(unit='km')THEN

RETURN km;

ELSEIF(unit='mi')THEN

RETURN (km * 0.621371192);

ELSE

RETURN km;

END IF;

END $$

DELIMITER $$

//php
function getMembersFilter($latitude_, $longitude_, $distance_, $format_='json')

{

//query

$query = "select * from makingFriends_members " .

"where uFn_CalcuDistance(last_latitude,last_longitude,'$latitude_','$longitude_','m')<='$distance'";

$members = array();

$result = mysql_query($query,$this->dbLink)or die('fail to query data!');

if(mysql_num_rows($result))

{

while($member = mysql_fetch_assoc($result))

{

$members[] = array('member'=>$member);

}

}

//output

//json

if($format_=='json')

{

header('Content-type: application/json');

echo $this->toJson('members',$members);

}

}