CODEWITHSUNDEEP
phpmysqlselectprintf
Stuart Pinfold
Stuart Pinfold

Reputation: 318

PHP MySQL statement - "select as" with "where"

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?

Upvotes: 0

Views: 4060

Answers (3)

Pankaj Khairnar
Pankaj Khairnar

Reputation: 3108

You can only use aliases in ORDER BY, GROUP BY and in HAVING clauses. use HAVING in your query for distance Use your code like this : 

$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!='' HAVING  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));

Upvotes: 0

Luca Rainone
Luca Rainone

Reputation: 16458

you can't use a column alias in the WHERE clause.

So you should use

 WHERE (1.609344 * 3959 * acos( cos( radians('".$center_lat."') ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians('".$center_lng."') ) + sin( radians('".$center_lat."') ) * sin( radians( latitude ) ) ) ) < 50

OR add a query select as table (not tested, please be patient)

SELECT tmpt.distance, company_name, address, telephone, fax, contact_email, website, url, latitude, longitude, 
   FROM (SELECT *your expression* FROM table_name) AS tmpt, 
   cmsms_module_compdir_companies
   WHERE  status='published' AND latitude!='' AND longitude!='' AND distance<'50' ORDER BY distance limit 5

Upvotes: 0

pensz
pensz

Reputation: 1881

You cannot use calculated value distance in where condition.

plz use having

where ....
having distance < 50

Btw : the unit of distance is kilometer?

Upvotes: 4

Related Questions