I am currently working on building a site that needs to have a search by distance around a zip code. It's been done I know, but I am doing this for two reasons, 1. I want to have my own and not spend any extra on it and 2. I am looking for specific results that are coming from other tables.
Okay, so here is the run down of what I have for a db structure.
profile table (contains all the information about the user like zip code, city etc.)
profile image table ( contains the image related to the users profile)
zip codes (table with thousands of zip codes and latitude longitude)
I have created a query to get all the zip codes within a distance
SELECT zipcode,truncate((degrees(acos(sin(radians(`Latitude`)) * sin( radians(".$latitude.")) + cos(radians(`Latitude`)) * cos( radians(".$latitude.")) * cos( radians(`Longitude` - ".$longitude.") ) ) ) * 69.09),1) as distance FROM `zipcodes_tbl` HAVING `distance` <= '$distance';
This works as expected as long as it receives $latitude, $longitue and $distance (which i submit from a form)
Next I have a select query that will get all the user profiles and join their photo up too, that wasn't too hard.
But what I need is to have a query select from the profile table when it is within the distance which is calculated from the table that can get the zipcodes. I have tried a select (subquery) from within the WHERE of the profiles table but that didn't seem to work.
I'm stuck and must be overlooking something or method. I don't do many complex queries so this seems to be beyond my level.
Can anyone think of a way to query all profiles based off the results returned from the calculation of the distance and have it all in one table? I would like to have the distance availble to display.
Thanks