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

Question

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?

@alditis - I tried this, made no difference.

Stuart Pinfold
– Stuart Pinfold

2012-12-02 16:11:54 +00:00
Commented Dec 2, 2012 at 16:11 — Stuart Pinfold
– Stuart Pinfold, Commented Dec 2, 2012 at 16:11

pensz · Accepted Answer · 2012-12-02 16:00:12Z

4

You cannot use calculated value distance in where condition.

plz use having

where ....
having distance < 50

Btw : the unit of distance is kilometer?

answered Dec 2, 2012 at 16:00

pensz

1,8811 gold badge13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Luca Rainone Over a year ago

Please reference for use HAVING without GROUP BY?

Stuart Pinfold Over a year ago

Thanks, this is perfect and @chumkiu it seems to work without GROUP BY.

Luca Rainone Over a year ago

If someone give me a doc for this I could upvote this answer :-) Because does not works for me (maybe as different mysql version)

pensz Over a year ago

@chumkiu ^_^ try it yourself. HAVING without GROUP BY can run correctly. Mysql support this behavior. The SQL standard requires that HAVING must reference only columns in the GROUP BY clause or columns used in aggregate functions. However, MySQL supports an extension to this behavior, and permits HAVING to refer to columns in the SELECT list and columns in outer subqueries as well.

pensz Over a year ago

@chumkiu doc link: dev.mysql.com/doc/refman/5.1/en/select.html search HAVING.

Luca Rainone · Accepted Answer · 2012-12-02 16:00:49Z

0

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

answered Dec 2, 2012 at 16:00

Luca Rainone

16.5k2 gold badges45 silver badges54 bronze badges

1 Comment

Luca Rainone Over a year ago

@AndreasLinden mmh having works only for group by I think

Pankaj Khairnar · Accepted Answer · 2012-12-02 16:05:38Z

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));

Collectives™ on Stack Overflow

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

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

5 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related