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 a function distance(zip1,zip2), you can use it in a query:

select a.username, b.username, distance(a.zip_code,b.zip_code) 
from user a, user b
where a.username='coolUser' and b.username='blueUser'

If you want all users sorted by distance to a given user, query

select a.username, b.username, distance(a.zip_code,b.zip_code) 
from user a, user b
where  b.sex = 'M' and b.birthdate > 4-12-1986
and a.username='coolUser' and b.username!=a.username
order by distance(a.zip_code,b.zip_code)
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.