Page 1 of 1

unsure how to structure my query

Posted: Sat Mar 15, 2008 12:46 am
by nantucketjoe
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.

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>&nbsp;</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";
              }
        }
    }
?>
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.

Re: unsure how to structure my query

Posted: Sat Mar 15, 2008 8:22 am
by Chalks
nantucketjoe wrote: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?
Without changing your first mysql query, there's no way to do this in your second query ($r2). However, it would be pretty easy to put all the data from r2 into an array, and sort that once everything is collected, then output from the array (instead of outputting while still in the second loop).

Re: unsure how to structure my query

Posted: Sat Mar 15, 2008 2:32 pm
by nantucketjoe
At the risk of sounding stupid, how might I store the results and sort them?

Re: unsure how to structure my query

Posted: Sun Mar 16, 2008 12:09 am
by RobertGonzalez
Search for "zip code proximity" or something like that. There are tons of open source free snippets around on how to pull certain rows of data from a database that are within a certain mileage range from a zip code.

If you want to do it yourself you will be looking for something called a "great circle radius" in addition to some spherical calculations for distance.

I know I posted a few snippets of mine once before. I am not on the machine that has that code at the moment but I will be on Monday if you can't find anything by searching.

Re: unsure how to structure my query

Posted: Sun Mar 16, 2008 1:26 am
by Chalks
nantucketjoe wrote:At the risk of sounding stupid, how might I store the results and sort them?
All your code stays the same except for adding a few lines:

Code: Select all

// ... left off most of your code for readabilities sake
        while($r2=mysql_fetch_array($loopresult)) 
              {
                 $name         = $r2["name"];
                 $level          = $r2["level"];
                 $nostudents = $r2["noStudents"];
                 $noteachers = $r2["noTeachers"];
                 $city           = $r2["city"];
                 $data[] = array($name, $level, $nostudents, $noteachers, $city);  // store info into an array
              }
        }
    }
    sort($data);
        // This sorts based on index 0 of the second level array
        // in $data... $name, in this case.  for reverse order, use rsort()
    foreach($data as $info)  // display it!
         echo "$info[0] - $info[4]<br>$info[1]<br> No. of Students $info[2]<br> No. of Teachers $info[3]";
Doing it this way makes it so all of the information from every city is stored into the array $data. Once you have all that information it is trivial to sort it based on the name of the school. There's a way to do this and maintain associative names (i.e. $info['name'] instead of $info[0]), but it looks a bit more complicated. I would research it but... I'm tired (2:28am). Anyways, hope it helps.

Re: unsure how to structure my query

Posted: Mon Mar 17, 2008 11:54 pm
by nantucketjoe
Thanks Everah for the Zip Code Proximity tip, I found a great script for it!

Thanks Chalks for the help, it works great!