PHP: Dufficult MySQL Query

[hermanSA]

Hi all,

Sorry for the long post but i need to describe my problem properly to avoid confusion, i hope

I have a table with users, and every user has a specific latitude and longitude coordinate.
let say user Test_User is logged in and then he decides he wants to search for users who lives within a certain distance from him - lets say 10 miles.

the only way to achieve the desired results at the moment is to get ALL the users in the 'users' table and then looping through the results and checking the distance with a PHP Function and then either displaying that users results or not.

below is a simplified version of my code:

-----------------------------------------

$sql = "SELECT * FROM users LIMIT 0, 10";

...

while($row = mysql_fetch_array($result)){
	$users_lon = $row["lon"];
	$users_lat = $row["lat"];
        // below I go to a php function and calculate the distance
	$dist = distance($my_lon, $my_lat, $users_lon, $users_lat);
}
if($dist < 10){ // 10 miles
   // show the user
} else {
   // skip - dont show user
}

-----------------------------------------
One of the problems with the way i do it above is that lets say i have a million users, then there will be unneccessary cpu power, or whatevr, used each time a user do a search, right?

AND the other problem is that i only want to display 10 results per page which is why i use LIMIT in my query, but if only 6 of the results i loop through is the right distance and gets displayed, i still need 4 more to display 10 results. i can however limit to 20 or 50 results and only display the first correct 10, but that will mess up the follwing pages..

So my question is:

Can i somehow check the distance while the query is running? (Im pretty sure you cant do a php function in a query).
If not what do you recommend?

Thanks in advance
Herman

[pritaeas]

See this tutorial. It contains the query you need to let mysql calculate the distance.

[hermanSA]

Thank you pritaeas, got it working