CODEWITHSUNDEEP
phpmysql
user1592953
user1592953

Reputation: 135

Using MySQL order by

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.

Upvotes: 1

Views: 70

Answers (3)

Fluffeh
Fluffeh

Reputation: 33512

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

Upvotes: 1

Nabab
Nabab

Reputation: 2644

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.

Upvotes: 2

Joe Day
Joe Day

Reputation: 7264

You can do something like:

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

Upvotes: 0

Related Questions