I have the following SQL statement which selects companies from the database (with a stored lat/lng) and displays the nearest 5 locations to the customer's location. This is working perfectly:
$query = sprintf("SELECT company_name, address, telephone, fax,
contact_email, website, url, latitude, longitude, (1.609344 * 3959 *
acos( cos( radians('".$center_lat."') ) * cos( radians( latitude ) ) *
cos( radians( longitude ) - radians('".$center_lng."') ) + sin(
radians('".$center_lat."') ) * sin( radians( latitude ) ) ) ) AS
distance FROM cmsms_module_compdir_companies WHERE status='published'
AND latitude!='' AND longitude!='' ORDER BY distance limit 5 ",
mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat));
However, I'd like to limit the results to only those stores within X distance from the customer's location - say 50 kilometers. I thought I could add the bit in bold below:
$query = sprintf("SELECT company_name, address, telephone, fax,
contact_email, website, url, latitude, longitude, (1.609344 * 3959 *
acos( cos( radians('".$center_lat."') ) * cos( radians( latitude ) ) *
cos( radians( longitude ) - radians('".$center_lng."') ) + sin(
radians('".$center_lat."') ) * sin( radians( latitude ) ) ) ) AS
distance FROM cmsms_module_compdir_companies WHERE status='published'
AND latitude!='' AND longitude!='' AND distance<'50' ORDER BY
distance limit 5 ", mysql_real_escape_string($center_lat),
mysql_real_escape_string($center_lng),
mysql_real_escape_string($center_lat));
...but this returns no results at all.
Any ideas?
Answer
You cannot use calculated value distance
in where
condition.
plz use having
where ....
having distance < 50
Btw : the unit of distance is kilometer?
No comments:
Post a Comment