So I have a MySQL database with cities/counties within the United States with longitude and latitude.
This is the query we are using to get results within 10 miles on any position for this example.
SELECT city, state,((ACOS(SIN('38.2966016' * PI() / 180) * SIN(lat * PI() / 180) + COS('38.2966016' * PI() / 180) * COS(lat * PI() / 180) * COS(('-77.634079'-lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS distance FROM zcta HAVING distance <= '10' ORDER BY distance ASC
When testing in Sequel Pro results come back as expected:
Fredericksburg VA 4.5932632328546
Spotsylvania VA 6.1939678870939
Richardsville VA 6.8293644913069
Locust Grove VA 7.6479616882979
Hartwood VA 7.6509448009002
Fredericksburg VA 7.8178182509761
Fredericksburg VA 7.8328645163604
Fredericksburg VA 7.8499527233007
Fredericksburg VA 7.8832043684573
Fredericksburg VA 8.8620101042208
However when plugged into the following Zend Framework:
$db = $this->getAdapter();
$sql = "SELECT city, state,((ACOS(SIN('38.2965709' * PI() / 180) * SIN(lat * PI() / 180) + COS('38.2965709' * PI() / 180) * COS(lat * PI() / 180) * COS(('-77.6340366'-lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `zcta` HAVING `distance`<='10' ORDER BY `distance` ASC";
//$sql = "SELECT city, state,((ACOS(SIN('".$lat."' * PI() / 180) * SIN(lat * PI() / 180) + COS('".$lat."' * PI() / 180) * COS(lat * PI() / 180) * COS(('".$lon."'-lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `zcta` HAVING `distance`<='10' ORDER BY `distance` ASC";
$stmt = $db->query($sql);
$rows = $stmt->fetchAll();
return $rows;
We get results (Order by closes)
Champlain, VA
Hustle, VA
Caret, VA
Tappahannock, VA
Loretto, VA
Champlain, VA is over 3开发者_运维知识库9 miles away. It's the same query, I even tested it within shell and got the same results as the Sequel Pro Fredericksburg results. Because of this I'm assuming Zend Framework is changing something when querying the database, thus the Champlain, VA results.
I am a newbie to ZF so is there something I'm missing to get ZF to return the results correctly?
Never heard of this problem. Have you tried to remove any quotes from Integers/Floats of your query:
$sql = "SELECT city, state,((ACOS(SIN(38.2965709 * PI() / 180) * SIN(lat * PI() / 180) + COS(38.2965709 * PI() / 180) * COS(lat * PI() / 180) * COS((-77.6340366-lon) * PI() / 180)) * 180 / PI()) * 60 * 1.1515) AS `distance` FROM `zcta` HAVING `distance`<=10 ORDER BY `distance` ASC";
Compare with 10 as number. The query seems to work for me after removing '' from '10'.
精彩评论