CODEWITHSUNDEEP
phpmysql
urfusion
urfusion

Reputation: 5501

use column value as column name mysql

I am trying to get all users in 5 KM radius. here is my table structure 

id| location
-------------
1|  26.851791,75.781810
2|  26.860729,75.7633127
3|  34.057811,-84.239125

I write a query to separate the location according to lat and long

SELECT SUBSTRING_INDEX( location, ',', 1 ) AS lat, SUBSTRING_INDEX( location, ',', -1 ) AS lng
FROM `users_test`
LIMIT 0 , 30

Which work fine and I got results as following

enter image description here

then I write a query to get all the users who are in 5KM Radius according to the following blog.

https://www.marketingtechblog.com/calculate-distance/

SELECT *,SUBSTRING_INDEX( location, ',', 1 ) AS lat, SUBSTRING_INDEX( location, ',', -1 ) AS lng,(((acos(sin(("26.851791"*pi()/180)) * sin((`lat`*pi()/180))+cos(("26.851791"*pi()/180)) * cos((`lat`*pi()/180)) * cos((("75.781810"- `lng`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance
FROM `users_test`
WHERE distance >= "5"

but I am getting following error.

#1054 - Unknown column 'lat' in 'field list'

can anyone tell me where I am wrong.

Upvotes: 2

Views: 96

Answers (1)

Barmar
Barmar

Reputation: 780974

You can't refer to a column alias in the same SELECT clause. You need to move it into a subquery. And you can't refer to an alias in the WHERE clause of the same SELECT, you need to use HAVING.

SELECT *, (((acos(sin(("26.851791"*pi()/180)) * sin((`lat`*pi()/180))+cos(("26.851791"*pi()/180)) * cos((`lat`*pi()/180)) * cos((("75.781810"- `lng`)*pi()/180))))*180/pi())*60*1.1515*1.609344) as distance
FROM (SELECT *, 
        SUBSTRING_INDEX( location, ',', 1 ) AS lat, 
        SUBSTRING_INDEX( location, ',', -1 ) AS lng
      FROM users_test) x
HAVING distance > 5

I strongly suggest you fix your table design to put the latitude and longitude in their own columns, instead of having to split on comma every time.

Upvotes: 4

Related Questions