How do I filter a SQL result based on distance when I have the latitude and longitude

[xecure]

I have a database full of user. This is roughly how the database looks:

| username | sex | birthdate  | zip_code |
|----------+-----+------------+----------|
| coolUser |  M  | 02-14-1987 |  90210   |
| blueUser |  F  | 06-16-1982 |  10011   |
|     .    |  .  |     .      |    .     |
|     .    |  .  |     .      |    .     |
|     .    |  .  |     .      |    .     |

A user will be able to search the database for another user based on their sex, age and distance. I have another database with a list of zipcodes in the U.S. along with with their latitude and longitude. I have written distance function where it calculates the distance of 2 zipcodes based on their latitude and longitude values. I understand I can sort the users by sex and age by something like this:

SELECT * FROM userList WHERE sex = 'M' AND birthdate > 4-12-1986

Which brings me to my actual question: Is there any possible way to calculate the distance of the 2 users via an SQL query? Or do I have to sort the user based on age and sex first and then get that list and then filter it by distance? If I go with the second method how do I properly display the first 10 results, and have a pagination feature?

[sv3tli0]

If you have an exact latitude and longitude for all zipcodes I think you have to do:

1st - SELECT the latitude and longitude of the user who is searching (with query or with user session information)..
2nd - When user set the distance and search - your script must calculate acceptable area for this.

$lt_min; $lt_max; $lg_min; $lg_max;

Something like from 43.0000 to 44.0000 and from -103.0000 to -102.0000..
This can be made if you have a calculator (range of 50km= 0.000500x0.000500) and etc.. and geting user 0.000000x0.000000 you can set the 4 vars with lt_min=0.000-0.005 lt_max=0.000+0.005 etc..

3rd - After you have this acceptable ranges you must make query which will get all zipcodes which are with in this acceptable ranges..
I will use the vars from my previous code..

$query="SELECT * FROM ZIPCODE WHERE 
latitude>$lt_min AND latitude<$lt_max AND 
longitude>$lg_min AND longitude<$lg_max";
 
$all_zips='('; 
$sql=mysql_query($query);
while($res=mysql_fetch_array($sql)){ $all_zips.=$res['zipcode'].','; }
$all_zips=substr($all_zips, 0, -1);
$all_zips.=')';

After that you make your query as :

SELECT * FROM userList WHERE sex = 'M' AND birthdate > 4-12-1986 AND zip_code IN $all_zips

This way your users will get results. Now here is a problem that this search will be made within a Rectangle range which is not 100% correct because your real range of 5km is with circle form not with rectangle.
To fix that you must make a calculation with your function in the WHILE at my upper code for each entry.. something like:

//FROM UP ! 
while($res=mysql_fetch_array($sql)){ $all_zips.=$res['zipcode'].','; }
//MUST BECOME
while($res=mysql_fetch_array($sql)){ 
if(your_function($res['lat'],$res['long'],$user['lat'],$user['long'],$search_distance)==true) $all_zips.=$res['zipcode'].',';
 }

Your function must return TRUE or FALSE depending if some zipcode position is within the search distance of user position

[Sogo7]

It would make more sense to store the distance value between zip codes as a seperate 'look-up' table of pre-computed results as this information will never change.