unsure how to structure my query
Posted: Sat Mar 15, 2008 12:46 am
Hi everyone, I am in need of a little help.
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.
All this works fine but the client wants the resulting output of schools to be sorted by name alphabetically regardless of the city they are in. How/Can I query the database with an array in the WHERE clause to achieve this? If not, how can I do it?
I apologize for being confusing and any help you can give would be appreciated.
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.