[RESOLVED] Zipcode Search Script - Need some help

[BenGilbert]

So I have written a script that will search a database of dealer information to locate dealers within 100 miles of the input zipcode.

The script works, but I am having a hard time trying to comprehend how to produce the results by closest to farthest.

I would think storing the results that matched the criteria into an array, then sorting the array and printing that information.

so i have two databases

Dealers --> information, zipcode, address, etc
Zipcodes --> Zipcode, Lat, Long

<?php

//function to calculate distance between two zip codes
function calcDist($lat_A, $long_A, $lat_B, $long_B) { 
  $distance = sin(deg2rad($lat_A)) 
                * sin(deg2rad($lat_B)) 
                + cos(deg2rad($lat_A)) 
                * cos(deg2rad($lat_B)) 
                * cos(deg2rad($long_A - $long_B)); 

  $distance = (rad2deg(acos($distance))) * 69.09; 
  return $distance; 
}

//display the search form on the page for user input
function displayOptions(){
echo <<<_END
 <form style="width:75%;" method='post' id="boatshow" enctype="multipart/form-data" action='dealer_search.php?action=search'>

<input type='text'  id="text" name='szip'/> <input type='submit' id="submit" style="float:right;" value='search' />

 </form>
_END;

}
//compare zipcode and locate closest dealers
function search(){
$search_zip = $_POST['szip'];
     //get the long and lat of the searched zip code
     $query  = "SELECT * FROM pro_zip_data WHERE zipcode='$search_zip'";
     $result = mysql_query($query);

 list($zip,$long1,$lat1) =  mysql_fetch_row($result);      

 //get the long and lat of all zipcodes stored for dealers and find distance  
 $query2  = "SELECT * FROM us_dealers";
 $result2 = mysql_query($query2);

 $total = 0; // if it stays 0 no results message displayed

 while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)){
     $name = $row2['dealer_name'];
     $newzip = $row2['postal_code'];

     $query3  = "SELECT * FROM pro_zip_data WHERE zipcode='$newzip'"; // get the lat and long of each zipcode stored in the dealer db
     $result3 = mysql_query($query3);
     $row3 = mysql_fetch_array($result3, MYSQL_ASSOC);
     $lon2 = $row3['lon'];
     $lat2 = $row3['lat'];
     $zipcode = $row3['zipcode'];   

     $distance = calcDist($lat1, $long1, $lat2, $lon2); //calculate the distance between two dealer zipcodes
     $distance = substr($distance,0,4);

   if ($distance <= 100){ // display the results of dealers under 100 miles

      echo $name . " - " . $distance . " miles away<br/><br/>";
      $total++;

   }
}
if ($total == 0){
echo "No Matches Found ";
}

////////////////////end of function
}

switch($_GET['action']) {

   case 'search':
        search(); 
        break;
    default:
        displayOptions();


}


?>

I realize this code doesn't attempt to do what I want, it displays all the results in the order they are processed in the while loop.

I am looking for advice on how to achive results listed from Shortest distance to longest.

-Ben

[Derokorian]

You could assign name and distance to arrays in the loop

$names = array();
$dist = array();
 while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)){
         $name = $row2['dealer_name'];
         $newzip = $row2['postal_code'];

     $query3  = "SELECT * FROM pro_zip_data WHERE zipcode='$newzip'"; // get the lat and long of each zipcode stored in the dealer db
     $result3 = mysql_query($query3);
     $row3 = mysql_fetch_array($result3, MYSQL_ASSOC);
     $lon2 = $row3['lon'];
     $lat2 = $row3['lat'];
     $zipcode = $row3['zipcode'];   

     $distance = calcDist($lat1, $long1, $lat2, $lon2); //calculate the distance between two dealer zipcodes
     $distance = substr($distance,0,4);

   if ($distance <= 100){ // display the results of dealers under 100 miles
      $names[] = $name;
      $dist[] = $distance;
   }
}
asort($dist);
if (count($name) > 0) {
    foreach($dist as $key => $distance) {
        echo $name[$key] . " - " . $distance . " miles away<br/><br/>";
    }
}

*Untested, just a theory

[BenGilbert]

For some reason when I test your code above, it sorts the results by distance fine, but for the name array it is only returning a single letter at random locations in the array.

I had been experimenting with storing all info in Multidimensional Arrays, so I could call more information about each dealer.

 while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)){
         $name = $row2['dealer_name'];
         $newzip = $row2['postal_code'];

     $query3  = "SELECT * FROM pro_zip_data WHERE zipcode='$newzip'"; // get the lat and long of each zipcode stored in the dealer db
     $result3 = mysql_query($query3);
     $row3 = mysql_fetch_array($result3, MYSQL_ASSOC);
     $lon2 = $row3['lon'];
     $lat2 = $row3['lat'];
     $zipcode = $row3['zipcode'];   

     $distance = calcDist($lat1, $long1, $lat2, $lon2); //calculate the distance between two dealer zipcodes
     $distance = substr($distance,0,4);

   if ($distance <= 100){ // display the results of dealers under 100 miles

      $zip_results_arr[$distance][] = $row2;  //store the distances, and then for each distance store the complete information
      $total++;

   }


}
 print_r($zip_results_arr); //just to test that the info was there
if ($total == 0){
echo "No Matches Found ";
}

[bradgrafelman]

One option would be to take your calcDist() function and convert it into a MySQL function. That way you can use it in your SQL queries, e.g. you could just do a simple JOIN to get the lat/lon of a zipcode from the results and ORDER BY the result of the calcDist() function.

[BenGilbert]

I got it working, thank you for the suggestions that lead to me getting it working the way I wanted it too. Here is how I completed it.

    while($row2 = mysql_fetch_array($result2, MYSQL_ASSOC)){
         $name = $row2['dealer_name'];
         $newzip = $row2['postal_code'];

     $query3  = "SELECT * FROM pro_zip_data WHERE zipcode='$newzip'"; // get the lat and long of each zipcode stored in the dealer db
     $result3 = mysql_query($query3);
     $row3 = mysql_fetch_array($result3, MYSQL_ASSOC);
     $lon2 = $row3['lon'];
     $lat2 = $row3['lat'];
     $zipcode = $row3['zipcode'];   

     $distance = calcDist($lat1, $long1, $lat2, $lon2); //calculate the distance between two dealer zipcodes
     $distance = substr($distance,0,4);

   if ($distance <= 100){ // display the results of dealers under 100 miles

      $dist[] = $distance;  //store distances
      $combinedResults[] = $row2;  //store all information for entry 

       $total++;      
   }

}
if ($total == 0){
echo "No Matches Found ";
} 
else if ($total != 0){
 asort($dist );  //sort the results by distance

foreach($dist as $key => $distance) { 
echo $combinedResults[$key]['dealer_name'] . " - " . $distance ." miles away<br/><br/>"; 
} 
}