use column value as column name mysql

Question

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

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.

you can't use aliases in field list

splash58
– splash58

2015-09-04 08:54:01 +00:00
Commented Sep 4, 2015 at 8:54 — splash58
– splash58, Commented Sep 4, 2015 at 8:54

Barmar · Accepted Answer · 2015-09-04 08:57:02Z

4

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.

edited Sep 4, 2015 at 8:57

answered Sep 4, 2015 at 8:54

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

2 Comments

urfusion Over a year ago

getting following erro #1248 - Every derived table must have its own alias

Barmar Over a year ago

Fixed. I gave the subquery an alias.

Collectives™ on Stack Overflow

use column value as column name mysql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related