I have one table listing zip codes, city, state, lat, and long. I have a second listing restaurant id numbers, city, and state.
In the second table lets say I have:
idcity__state
1New YorkNY
2Los Angeles_CA
I'm trying to set it up so that if I query a 50 mile radius around LA I only get one entry for LA to echo. The way I have it now I get an echo for every result of the first query each one echoing "LA,2".
The first query searches zipcodes for all locations within a 50 mile radius of whatever lat/long I give it. The second query is then supposed to take the results ($selected_cities) and search the second table for those city names ($selected_cities) and echo those results. Instead I get an "LA,2" for every result of the first query. I should only be getting one.
$sql = "SELECT DISTINCT city, zipcode, ( 3959 * acos( cos( radians( {$coords['latitude']} ) ) * cos( radians( latitude ) ) * cos( radians( longitude ) - radians( {$coords['longitude']} ) ) + sin( radians( {$coords['latitude']} ) ) * sin( radians( latitude ) ) ) ) AS distance FROM zipcodes HAVING distance <= {$radius} ORDER BY distance";
$query = mysql_query($sql);
while($row = mysql_fetch_assoc($query)){
$selected_cities = $row['city'];
//echo "{$row['city']} {$row['zipcode']} ({$row['distance']})<br>\n";
$Get_City = mysql_query("SELECT rest_id, city FROM restaurant_info WHERE state='$state' AND city = '$selected_cities' ORDER BY city",$db_link);
$got_city = mysql_fetch_array($Get_City);
$city_names = $got_city['city'];
$rest_id = $got_city['rest_id'];
if ($rest_id){
echo $city_names . "," . $rest_id . "<br />";
}else{
echo '';
}}
