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?

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

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.