unsure how to structure my query

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
nantucketjoe
Forum Newbie
Posts: 3
Joined: Sat Mar 15, 2008 12:27 am

unsure how to structure my query

Post 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.
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Re: unsure how to structure my query

Post 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).
nantucketjoe
Forum Newbie
Posts: 3
Joined: Sat Mar 15, 2008 12:27 am

Re: unsure how to structure my query

Post by nantucketjoe »

At the risk of sounding stupid, how might I store the results and sort them?
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Re: unsure how to structure my query

Post 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.
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Re: unsure how to structure my query

Post 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.
nantucketjoe
Forum Newbie
Posts: 3
Joined: Sat Mar 15, 2008 12:27 am

Re: unsure how to structure my query

Post 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!
Post Reply