Using MySQL order by

Question

I have a website where users can look up other users. It is a basic search feature, but I want the users' results to be location based. So maybe they search "David". The basic query for the would be

SELECT * 
FROM   users 
where  fname = 'David' or 
       lname= 'David' 
ORDER BY distance ASC

The problem is that the distance has to be first calculated between each user and the searcher. How can I do this? Not asking to be shown how to calculate distance or anything, just how to logically solve this problem.

How are you encoding the location? Do you have the current user's location accessible directly from PHP (e.g. in a variable) or do you have to query for that? — nico, Aug 19 '12 at 14:02
completing nico ... what does distance store in the db? distance between where and where? — shampoo, Aug 19 '12 at 14:04
similar ... http://stackoverflow.com/questions/7379423/sorted-difference-between-two-columns, might be a duplicate :D — shampoo, Aug 19 '12 at 14:22

score 2 · Accepted Answer · answered Aug 19 '12 at 14:10

EDIT: The thing is to calculate the distance directly in your select statement and to order the result based on this calculation.

So if you stored the latitude/longitude in the database, you should be able to do something like that:

$lat = {CURRENT_USER_LATITUDE};
$long = {CURRENT_USER_LONGITUDE};
SELECT users.*,
6371 * 2 * ASIN(SQRT( POWER(SIN(($lat - ABS(users.latitude)) * pi()/180 / 2), 2) + COS($lat * pi()/180 ) * COS( ABS(users.latitude) * pi() / 180 ) * POWER(SIN(($long - users.longitude)*pi()/180 / 2),2))) AS distance
FROM users
ORDER BY distance

And you might have a much simpler query if you're using the geo data types in MySQL.

should the caculation as a function or stored procedure?: – Baiyan Huang Aug 19 '12 at 14:14 — Baiyan Huang, Aug 19 '12 at 14:14
I do it as it is but I guess you can use either. – Nabab Aug 19 '12 at 14:16 — Nabab, Aug 19 '12 at 14:16

score 1 · Answer 2 · answered Aug 19 '12 at 14:11

If you are using a calculated field like

select
    someColumn,
    abs(someValue-someValue2)
from 
    myTable

And you need to order by the minimum abs() value you need to add it to the order by clause as follows:

select
    someColumn,
    abs(someValue-someValue2)
from 
    myTable
order by
    abs(someValue-someValue2) asc

Or you can use this syntax:

select
    someColumn,
    abs(someValue-someValue2)
from 
    myTable
order by
    2 asc

score 0 · Answer 3 · answered Aug 19 '12 at 14:09

0

You can do something like:

SELECT users.*, <distance calculation> AS distance
FROM users
WHERE fname = 'David' or
      lname = 'David'
ORDER BY distance ASC

answered Aug 19 '12 at 14:09

Joe Day

6,965
4
25
26

Using MySQL order by

3 Answers3