I was asked by a client to build a website that would rate schools in our state which is easy enough however, here's the interesting part. How he wants to do this is to display the schools within a certain distance so, for example, to show how many schools are within 5 miles of downtown Boston (or 10 miles, 25, 50 etc. you get the picture).
To do this, I take the zipcode and distance from the search query and get the latitude and longitude, I then add to the resulting co-ordinates to get my radius and spit out any cities that are within that radius. I then use a foreach loop on each resulting city to query the database to spit out any schools in that city.
Code: Select all
<?php
$query = "SELECT * FROM zipCodes WHERE zipCode = '$zipcode'";
$result = mysql_query($query) or die(mysql_error());
$total = mysql_num_rows($result);
if ($total == '0') echo "The Zipcode you entered is not valid";
else {
while($r=mysql_fetch_array($result))
{
$id = $r["id"];
$ziplat = $r["zipLatitude"];
$ziplon = $r["zipLongitude"];
$city = $r["city"];
echo "$city: $ziplat, $ziplon<p> </p>";
}
}
if ($distance == '10'){
$newlat1 = "$ziplat"-0.07722;
$newlon1 = "$ziplon-"-0.07107;
$newlat2 = "$ziplat"+0.07722;
$newlon2 = "$ziplon"+0.07107;
}
if ($distance == '25'){
$newlat1 = "$ziplat"-0.19305;
$newlon1 = "$ziplon"-0.177675;
$newlat2 = "$ziplat"+0.19305;
$newlon2 = "$ziplon"+0.177675;
}
if ($distance == '50'){
$newlat1 = "$ziplat"-0.3861;
$newlon1 = "$ziplon"-0.35535;
$newlat2 = "$ziplat"+0.3861;
$newlon2 = "$ziplon"+0.35535;
}
if ($distance == '75'){
$newlat1 = "$ziplat"-0.57915;
$newlon1 = "$ziplon"-0.533025;
$newlat2 = "$ziplat"+0.57915;
$newlon2 = "$ziplon"+0.533025;
}
if ($distance == '100'){
$newlat1 = "$ziplat"-0.7722;
$newlon1 = "$ziplon"-0.7107;
$newlat2 = "$ziplat"+0.7722;
$newlon2 = "$ziplon"+0.7107;
}
$query = "SELECT * FROM zipCodes
WHERE zipLatitude >= '$newlat1'
AND zipLongitude <= '$newlon1'
AND zipLatitude <= '$newlat2'
AND zipLongitude >= '$newlon2'
GROUP BY city, state
ORDER BY state, city ASC";
$result = mysql_query($query);
$noresults = mysql_num_rows($result);
while($r=mysql_fetch_array($result))
{
$city = $r["city"];
$state = $r["state"];
$lat = $r["latitude"];
$lon = $r["longitude"];
$cities = array("$city");
reset($cities);
foreach ($cities as $city1)
{
$loopquery = "SELECT * FROM schools
WHERE city = '$city1'";
$loopresult = mysql_query($loopquery);
$looptotal = mysql_num_rows($loopresult);
while($r2=mysql_fetch_array($loopresult))
{
$name = $r2["name"];
$level = $r2["level"];
$nostudents = $r2["noStudents"];
$noteachers = $r2["noTeachers"];
$city = $r2["city"];
echo "$name - $city<br>$level<br> No. of Students $nostudents<br> No. of Teachers $noteachers";
}
}
}
?>I apologize for being confusing and any help you can give would be appreciated.