[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 654
  • Last Modified:

sorting SQL results inside a while loop

Hello,

I cannot add it to my SQL query (as far as i know), I know this will be the first suggestion

I have a search which does a radius search for a postal code (Zip code). the code works great i just need to sort my results by distance. Unfortunately distance is not calculated until I am inside my output while loop as seen below

$zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "K"), 1);

in my sql query i can only sort by existing fields, so i cant figure out how to sort by a calculation... so i decided it might be best to sort it in my output while loop which is at bottom of code

the code runs from a form which has 2 variables postal code ($zip1) and distance($zipMilesHigh)

it has also been suggested to me that i could have stored procedure do that work, but i have no idea how to write one or install it using phpMyAdmin


this is my code.....



<?php

function DistanceCalc($lat1, $lon1, $lat2, $lon2, $unit) {

  $theta = $lon1 - $lon2;
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta));
  $dist = acos($dist);
  $dist = rad2deg($dist);
  $miles = $dist * 60 * 1.1515;
  $unit = strtoupper($unit);

  if ($unit == "K") {
    return ($miles * 1.609344);
  } else if ($unit == "N") {
      return ($miles * 0.8684);
    } else {
        return $miles;
      }
}
function zipRadiusSQL($sqlFields, $varZip, $varLatitude, $varLongitude, $varMiles){

      $varLatRange = $varMiles / ((6076 / 5280) * 60) + ($varMiles / 1000);
      $varLongRange = $varMiles / (((cos($varLatitude * 3.141592653589 / 180) * 6076.) / 5280.) * 60) + ($varMiles / 1000);

            $zipRadiusSQL_str = "SELECT * FROM postalcodes INNER JOIN groups ON postalcodes.PostalCode = groups.groups_pc WHERE  (";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ."Longitude <= (". $varLongitude ." + ". $varLongRange .")";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ." AND ";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ."Longitude >= (". $varLongitude ." - ". $varLongRange .")";
            $zipRadiusSQL_str = $zipRadiusSQL_str .")";
            $zipRadiusSQL_str = $zipRadiusSQL_str ." AND (";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ."Latitude <= (". $varLatitude ." + ". $varLatRange .")";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ." AND ";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ."Latitude >= (". $varLatitude ." - ". $varLatRange .")";
            $zipRadiusSQL_str = $zipRadiusSQL_str .")";
            $zipRadiusSQL_str = $zipRadiusSQL_str ." AND Longitude <> 0 AND Latitude <> 0 ORDER BY PostalCode ASC";

return $zipRadiusSQL_str;
}


//===================================================
// Process the requested values from the form and set to variables.
$zip1 = $_POST['zip1'];
$zipMilesHigh = $_POST['zipMilesHigh'];


//===================================================
// Processes the form request.
// ** You may need to change the table name from ZIPCODES to your table name
if (($_POST['Submit'] <> "") || ($_POST['MM_update'] == "form1")){

 if(!$_POST['zipMilesHigh']) { $zipMilesHigh = 4000; }
 
      // Get base ZIP Code
      $sql = "SELECT * FROM postalcodes WHERE PostalCode = '". $zip1 ."'";
      $result = mysql_query($sql, $conn)or die("Unable to query local database for base data <b>". mysql_error() ."</b><br>$sql");
    if (!$result){
        echo "database query failed.";
    }else{
            $i = 0;
            $result_ar = mysql_fetch_assoc($result);
            $zip1Latitude = $result_ar['Latitude'];
            $zip1Longitude = $result_ar['Longitude'];
            $zip1State = $result_ar['State'];
            $zip1City = $result_ar['City'];
            $zip1ZipCode = $zip1;       
        
         $sql = zipRadiusSQL("", $zip1ZipCode, $zip1Latitude, $zip1Longitude, $zipMilesHigh);
         $result = mysql_query($sql, $conn)or die("Unable to query local database for main data <b>". mysql_error() ."</b><br>$sql");
 
 
    }
}



$i = 0;
$a = 0;


while ($i < $num) {
      $a++;

      $zipLatitude = mysql_result($result,$i,"Latitude");
      $zipLongitude = mysql_result($result,$i,"Longitude");
      $zipState = mysql_result($result,$i,"Province");
      $zipZipCode = mysql_result($result,$i,"PostalCode");
      $zipAreaCode = mysql_result($result,$i,"AreaCode");
      $zipCity = mysql_result($result,$i,"City");
      $zipCountyName = mysql_result($result,$i,"CountyName");
      $groups_id = mysql_result($result,$i,"groups_id");
      $groups_name = mysql_result($result,$i,"groups_name");
      $groups_city = mysql_result($result,$i,"groups_city");
      $zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "K"), 1);

      if ($zipDistance < $zipMilesHigh){
?>

<?php echo $groups_name; ?> <?php echo $zipZipCode; ?><?php echo $groups_city; ?> <?php echo $zipDistance; ?> KM<br>

<?
}else{
$a--;
      }
$i++;
}
?>
0
jblayney
Asked:
jblayney
  • 11
  • 8
1 Solution
 
hieloCommented:
>>in my sql query i can only sort by existing fields, so i cant figure out how to sort by a calculation...
ex:
SELECT field1, field2, (field1 + field2) as field3
FROM table_name ORDER BY  (field1 + field2);
0
 
jblayneyAuthor Commented:
can u apply that to my code above
0
 
jblayneyAuthor Commented:
to further help, these are the fields needed

form postalcodes
PostalCode, Latitude, Longitude

from groups
groups_id, groups_name, groups_pc, groups_city
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
hieloCommented:
what you have there is something else buddy. Since you already have what you need in php, try:
<?php
 
function DistanceCalc($lat1, $lon1, $lat2, $lon2, $unit) { 
 
  $theta = $lon1 - $lon2; 
  $dist = sin(deg2rad($lat1)) * sin(deg2rad($lat2)) +  cos(deg2rad($lat1)) * cos(deg2rad($lat2)) * cos(deg2rad($theta)); 
  $dist = acos($dist); 
  $dist = rad2deg($dist); 
  $miles = $dist * 60 * 1.1515;
  $unit = strtoupper($unit);
 
  if ($unit == "K") {
    return ($miles * 1.609344); 
  } else if ($unit == "N") {
      return ($miles * 0.8684);
    } else {
        return $miles;
      }
}
function zipRadiusSQL($sqlFields, $varZip, $varLatitude, $varLongitude, $varMiles){
 
      $varLatRange = $varMiles / ((6076 / 5280) * 60) + ($varMiles / 1000);
      $varLongRange = $varMiles / (((cos($varLatitude * 3.141592653589 / 180) * 6076.) / 5280.) * 60) + ($varMiles / 1000);
 
            $zipRadiusSQL_str = "SELECT * FROM postalcodes INNER JOIN groups ON postalcodes.PostalCode = groups.groups_pc WHERE  (";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ."Longitude <= (". $varLongitude ." + ". $varLongRange .")";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ." AND ";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ."Longitude >= (". $varLongitude ." - ". $varLongRange .")";
            $zipRadiusSQL_str = $zipRadiusSQL_str .")";
            $zipRadiusSQL_str = $zipRadiusSQL_str ." AND (";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ."Latitude <= (". $varLatitude ." + ". $varLatRange .")";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ." AND ";
                  $zipRadiusSQL_str = $zipRadiusSQL_str ."Latitude >= (". $varLatitude ." - ". $varLatRange .")";
            $zipRadiusSQL_str = $zipRadiusSQL_str .")";
            $zipRadiusSQL_str = $zipRadiusSQL_str ." AND Longitude <> 0 AND Latitude <> 0 ORDER BY PostalCode ASC";
 
return $zipRadiusSQL_str;
}
 
 
//===================================================
// Process the requested values from the form and set to variables.
$zip1 = $_POST['zip1'];
$zipMilesHigh = $_POST['zipMilesHigh'];
 
 
//===================================================
// Processes the form request.
// ** You may need to change the table name from ZIPCODES to your table name
if (($_POST['Submit'] <> "") || ($_POST['MM_update'] == "form1")){
 
 if(!$_POST['zipMilesHigh']) { $zipMilesHigh = 4000; }
 
      // Get base ZIP Code
      $sql = "SELECT * FROM postalcodes WHERE PostalCode = '". $zip1 ."'";
      $result = mysql_query($sql, $conn)or die("Unable to query local database for base data <b>". mysql_error() ."</b><br>$sql"); 
    if (!$result){ 
        echo "database query failed."; 
    }else{
            $i = 0;
            $result_ar = mysql_fetch_assoc($result);
            $zip1Latitude = $result_ar['Latitude'];
            $zip1Longitude = $result_ar['Longitude'];
            $zip1State = $result_ar['State'];
            $zip1City = $result_ar['City'];
            $zip1ZipCode = $zip1;        
         
         $sql = zipRadiusSQL("", $zip1ZipCode, $zip1Latitude, $zip1Longitude, $zipMilesHigh);
         $result = mysql_query($sql, $conn)or die("Unable to query local database for main data <b>". mysql_error() ."</b><br>$sql"); 
  
 
    }
} 
 
 
 
$i = 0;
$a = 0;
 
$computedDistance=array();
while ($i < $num) {
      $a++;
 
      $zipLatitude = mysql_result($result,$i,"Latitude");
      $zipLongitude = mysql_result($result,$i,"Longitude");
      $zipState = mysql_result($result,$i,"Province");
      $zipZipCode = mysql_result($result,$i,"PostalCode");
      $zipAreaCode = mysql_result($result,$i,"AreaCode");
      $zipCity = mysql_result($result,$i,"City");
      $zipCountyName = mysql_result($result,$i,"CountyName");
      $groups_id = mysql_result($result,$i,"groups_id");
      $groups_name = mysql_result($result,$i,"groups_name");
      $groups_city = mysql_result($result,$i,"groups_city");
      $zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "K"), 1);
 
     //cache the resutls
	$computedDistance[]=array("groups_name"=>$groups_name,"zipZipCode"=>$zipZipCode,"groups_city"=>$groups_city,"zipDistance"=>$zipDistance);
 
	//I don't know what that if-else is for so I'm leaving it alone;
      if ($zipDistance < $zipMilesHigh)
	 {
		echo $groups_name; 
		echo $zipZipCode;
		echo $groups_city; 
		echo $zipDistance . " KM<br>";
	}
	else
	{
		$a--;
	}
	
	$i++;
}
//sort the results
usort($computedDistance, "cmp");
 
//print whole array
//print_r($computedDistance);
 
//if you need to, you can iterate over the results here
echo "<table>";
echo "<tr><th>". implode("</th><th>" , array_keys($subArr)) . " KM</th></tr>";
foreach($computedDistance as $subArr){
	echo "<tr><td>". implode("</td><td>" , $subArr) . " KM</td></tr>";
}
echo "</table>";
 
//this is the sort function
function cmp($a, $b)
{
    if ($a['zipDistance'] == $b['zipDistance']) {
        return 0;
    }
    return ($a['zipDistance'] < $b['zipDistance']) ? -1 : 1;
}
?>

Open in new window

0
 
jblayneyAuthor Commented:
thank you heilo, i suspect you are a genius and i appreciate your help

the results are a bit weird... i am obviously dumping the results twice, the one below is fetching the proper records, that IF statement you were wondering about is making sure that the results are less than the Kilometers which were entered into the form

 if ($zipDistance < $zipMilesHigh)
         {
                echo $groups_name . " ";
                echo $zipZipCode . " ";
                echo $groups_city . " ";
                echo $zipDistance . "<br>";
        }
        else
        {
                $a--;
        }
       
        $i++;
}

Your results are doubling approximately (eg, i enter 50 kilometer search, it is grabbing up to 100 KM) and they are not in any order at all,

this is my actual export code ( i had cleaned it up before posting because it is alot, this entire export is part of a form),  but it will give you an idea of whats im doing


<?php
$num=mysql_numrows($result);

 if( (!$num) ){
 } else {
 
   ?>
<form name="form2" method="POST" action="<?php echo $editFormAction; ?>">
<span style="font-weight: bold">Groups</span><br />
<table width="100%"  border="0" cellspacing="3" cellpadding="0">
<tr align="left" valign="top">
              <td nowrap>Date matched:<br />
                <br /></td>
              <td colspan="4">
 
                      <select name="month" id="month">
                        <option selected>Choose Month</option>
                        <option value="01">January</option>
                        <option value="02">February</option>
                        <option value="03">March</option>
                        <option value="04">April</option>
                        <option value="05">May</option>
                        <option value="06">June</option>
                        <option value="07">July</option>
                        <option value="08">August</option>
                        <option value="09">September</option>
                        <option value="10">October</option>
                        <option value="11">November</option>
                        <option value="12">December</option>
                        </select>
                      <select name="day" id="day">
                        <option selected>Choose day</option>
                        <option value="01">01</option>
                        <option value="02">02</option>
                        <option value="03">03</option>
                        <option value="04">04</option>
                        <option value="05">05</option>
                        <option value="06">06</option>
                        <option value="07">07</option>
                        <option value="08">08</option>
                        <option value="09">09</option>
                        <option value="10">10</option>
                        <option value="11">11</option>
                        <option value="12">12</option>
                        <option value="13">13</option>
                        <option value="14">14</option>
                        <option value="15">15</option>
                        <option value="16">16</option>
                        <option value="17">17</option>
                        <option value="18">18</option>
                        <option value="19">19</option>
                        <option value="20">20</option>
                        <option value="21">21</option>
                        <option value="22">22</option>
                        <option value="23">23</option>
                        <option value="24">24</option>
                        <option value="25">25</option>
                        <option value="26">26</option>
                        <option value="27">27</option>
                        <option value="28">28</option>
                        <option value="29">29</option>
                        <option value="30">30</option>
                        <option value="31">31</option>
                        </select>
                <select name="year" id="year">
                        <option value="" selected>Choose year</option>
                        <? for ($i = date('Y') -2; $i < date('Y') + 3; $i++) {
echo '<option value="' . $i . '">' . $i . '</option>';
}
?>
                </select>                     
                    
                    
                    </td>
            </tr>

<?
$i = 0;
$a = 0;


while ($i < $num) {
      $a++;

      $zipLatitude = mysql_result($result,$i,"Latitude");
      $zipLongitude = mysql_result($result,$i,"Longitude");
      $zipState = mysql_result($result,$i,"Province");
      $zipZipCode = mysql_result($result,$i,"PostalCode");
      $zipAreaCode = mysql_result($result,$i,"AreaCode");
      $zipCity = mysql_result($result,$i,"City");
      $zipCountyName = mysql_result($result,$i,"CountyName");
      $groups_id = mysql_result($result,$i,"groups_id");
      $groups_name = mysql_result($result,$i,"groups_name");
      $groups_city = mysql_result($result,$i,"groups_city");
      $zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "K"), 1);




      if ($zipDistance < $zipMilesHigh){
?>


<?php ?>

  <tr align="left" valign="top">
    <td width="23%"><input name="groups" type="radio" value="<?php echo $groups_id; ?>"> Add as Sponsor </td>
    <td width="37%" nowrap="nowrap"><a href="../groups/groups_edit.php?id=<?php echo $groups_id; ?>"><?php echo $groups_name; ?></a></td>
    <td width="16%"><?php echo $zipZipCode; ?></td>
    <td width="13%"><?php echo $groups_city; ?> </td>
    <td width="11%" align="right"><?php echo $zipDistance; ?> KM</td>
   
  </tr>
  <tr>
    <td colspan="5" class="bg1"><img src="../images/spacer.gif" height="1" /></td>
   
  </tr>


<?
}else{
$a--;
            //nothing      
      }
$i++;
}

?>
 <tr align="left" valign="top">
              <td colspan="5">               <br>
                <br>
                <input type="submit" name="Submit" value="Add Selected Group Sponsor">
<input type="hidden" name="user_id" value="<?php echo $user_id; ?>">
<input type="hidden" name="todayDate" value="<?php echo $todayDate; ?>">
                  <input name="christmas_id" type="hidden" id="christmas_id" value="<?php echo $row_Recordset_christmas['christmas_id']; ?>">
                  <input name="christmas_child" type="hidden" id="christmas_child" value="<?php echo $row_Recordset_christmas['christmas_child']; ?>"></td></tr>
               

</table>
  <input name="type" type="hidden" id="type" value="<?php echo $type; ?>" />
 
  <input type="hidden" name="christmas_filled" value="1">
              <input type="hidden" name="towho" value="Groups">
            <input type="hidden" name="MM_insert" value="form2">
            <input type="hidden" name="MM_update" value="form2">
          </form>

<?php } ?>


0
 
jblayneyAuthor Commented:
Sorry, im reposted using the code function
<?php
$num=mysql_numrows($result);
 
 if( (!$num) ){
 } else {
 
   ?>
<form name="form2" method="POST" action="<?php echo $editFormAction; ?>">
<span style="font-weight: bold">Groups</span><br />
<table width="100%"  border="0" cellspacing="3" cellpadding="0">
<tr align="left" valign="top">
              <td nowrap>Date matched:<br />
                <br /></td>
              <td colspan="4">
 
                      <select name="month" id="month">
                        <option selected>Choose Month</option>
                        <option value="01">January</option>
                        <option value="02">February</option>
                        <option value="03">March</option>
                        <option value="04">April</option>
                        <option value="05">May</option>
                        <option value="06">June</option>
                        <option value="07">July</option>
                        <option value="08">August</option>
                        <option value="09">September</option>
                        <option value="10">October</option>
                        <option value="11">November</option>
                        <option value="12">December</option>
                        </select>
                      <select name="day" id="day">
                        <option selected>Choose day</option>
                        <option value="01">01</option>
                        <option value="02">02</option>
                        <option value="03">03</option>
                        <option value="04">04</option>
                        <option value="05">05</option>
                        <option value="06">06</option>
                        <option value="07">07</option>
                        <option value="08">08</option>
                        <option value="09">09</option>
                        <option value="10">10</option>
                        <option value="11">11</option>
                        <option value="12">12</option>
                        <option value="13">13</option>
                        <option value="14">14</option>
                        <option value="15">15</option>
                        <option value="16">16</option>
                        <option value="17">17</option>
                        <option value="18">18</option>
                        <option value="19">19</option>
                        <option value="20">20</option>
                        <option value="21">21</option>
                        <option value="22">22</option>
                        <option value="23">23</option>
                        <option value="24">24</option>
                        <option value="25">25</option>
                        <option value="26">26</option>
                        <option value="27">27</option>
                        <option value="28">28</option>
                        <option value="29">29</option>
                        <option value="30">30</option>
                        <option value="31">31</option>
                        </select>
                <select name="year" id="year">
                        <option value="" selected>Choose year</option>
                        <? for ($i = date('Y') -2; $i < date('Y') + 3; $i++) {
echo '<option value="' . $i . '">' . $i . '</option>';
}
?>
                </select>                      
                    
                    
                    </td>
            </tr>
 
<?
$i = 0;
$a = 0;
 
 
while ($i < $num) {
      $a++;
 
      $zipLatitude = mysql_result($result,$i,"Latitude");
      $zipLongitude = mysql_result($result,$i,"Longitude");
      $zipState = mysql_result($result,$i,"Province");
      $zipZipCode = mysql_result($result,$i,"PostalCode");
      $zipAreaCode = mysql_result($result,$i,"AreaCode");
      $zipCity = mysql_result($result,$i,"City");
      $zipCountyName = mysql_result($result,$i,"CountyName");
      $groups_id = mysql_result($result,$i,"groups_id");
      $groups_name = mysql_result($result,$i,"groups_name");
      $groups_city = mysql_result($result,$i,"groups_city");
      $zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "K"), 1);
 
 
 
 
      if ($zipDistance < $zipMilesHigh){
?>
 
 
<?php ?>
 
  <tr align="left" valign="top">
    <td width="23%"><input name="groups" type="radio" value="<?php echo $groups_id; ?>"> Add as Sponsor </td>
    <td width="37%" nowrap="nowrap"><a href="../groups/groups_edit.php?id=<?php echo $groups_id; ?>"><?php echo $groups_name; ?></a></td>
    <td width="16%"><?php echo $zipZipCode; ?></td>
    <td width="13%"><?php echo $groups_city; ?> </td>
    <td width="11%" align="right"><?php echo $zipDistance; ?> KM</td>
   
  </tr>
  <tr>
    <td colspan="5" class="bg1"><img src="../images/spacer.gif" height="1" /></td>
   
  </tr>
 
 
<?
}else{
$a--;
            //nothing      
      }
$i++;
}
 
?>
 <tr align="left" valign="top">
              <td colspan="5">               <br>
                <br>
                <input type="submit" name="Submit" value="Add Selected Group Sponsor">
<input type="hidden" name="user_id" value="<?php echo $user_id; ?>">
<input type="hidden" name="todayDate" value="<?php echo $todayDate; ?>">
                  <input name="christmas_id" type="hidden" id="christmas_id" value="<?php echo $row_Recordset_christmas['christmas_id']; ?>">
                  <input name="christmas_child" type="hidden" id="christmas_child" value="<?php echo $row_Recordset_christmas['christmas_child']; ?>"></td></tr>
               
 
</table>
  <input name="type" type="hidden" id="type" value="<?php echo $type; ?>" />
  
  <input type="hidden" name="christmas_filled" value="1">
              <input type="hidden" name="towho" value="Groups">
            <input type="hidden" name="MM_insert" value="form2">
            <input type="hidden" name="MM_update" value="form2">
          </form>
 
<?php } ?>

Open in new window

0
 
hieloCommented:
>>that IF statement you were wondering about is making sure that the results are less than the Kilometers which were entered into the form
Does that meant that those are the only values you are interested? (The ones within the if clause?)
0
 
jblayneyAuthor Commented:
yes, let me explain the whole process

I start with a simple form with 2 fields postal code ($zip1) and radius($zipMilesHigh)

so i am searching for all records which have a postal code within say for example 15 kilometers of the entered postal code.

So the SQl query starts ($sql) and it runs the sub query function (zipRadiusSQL) and it successfully returns me the proper results.

then the results of that query populate a new form which lists all the results as radio button items, the user will pick one of the results and submit form number 2


in my results (while loop) this little function below is doing the math to give me my final kilometers for display purposes

$zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "K"), 1);

the array you are building is a great idea i think, but i dont know how to work with arrays
0
 
hieloCommented:
assuming the answer is yes, cache the results within the if. Based on my previous post, this is the updated section:
...
while ($i < $num) {
      $a++;
 
      $zipLatitude = mysql_result($result,$i,"Latitude");
      $zipLongitude = mysql_result($result,$i,"Longitude");
      $zipState = mysql_result($result,$i,"Province");
      $zipZipCode = mysql_result($result,$i,"PostalCode");
      $zipAreaCode = mysql_result($result,$i,"AreaCode");
      $zipCity = mysql_result($result,$i,"City");
      $zipCountyName = mysql_result($result,$i,"CountyName");
      $groups_id = mysql_result($result,$i,"groups_id");
      $groups_name = mysql_result($result,$i,"groups_name");
      $groups_city = mysql_result($result,$i,"groups_city");
      $zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "K"), 1);
 
 
	//I don't know what that if-else is for so I'm leaving it alone;
      if ($zipDistance < $zipMilesHigh)
	 {
//		echo $groups_name; 
//		echo $zipZipCode;
//		echo $groups_city; 
//		echo $zipDistance . " KM<br>";
 
     //cache the resutls
	$computedDistance[]=array("groups_name"=>$groups_name,"zipZipCode"=>$zipZipCode,"groups_city"=>$groups_city,"zipDistance"=>$zipDistance);
 
	}
	else
	{
		$a--;
	}
	
	$i++;
}
...

Open in new window

0
 
jblayneyAuthor Commented:
Hello,

we are getting closer, it still isnt sorting... this is my result using 10 km search, you see it is very random

Central Pentecostal Church      L4Z 3L3      Mississauga      6.5 KM
Queensway Christian College      M8Z 1T5      Toronto      5.8 KM
Gathered Together      M8Z 5G8      Toronto      6.6 KM
Justin Second Church      L4Y 3C5      Mississauga      0.0 KM

All i have changed is the export, i haven't changed anything in the sql query except to remove the ORDER BY

Also, how do i grab the array items one at a time, so I can write the code like this

  <tr align="left" valign="top">
    <td width="23%"><input name="groups" type="radio" value="<?php echo $groups_id; ?>"> Add as Sponsor </td>
    <td width="37%" nowrap="nowrap"><a href="../groups/groups_edit.php?id=<?php echo $groups_id; ?>"><?php echo $groups_name; ?></a></td>
    <td width="16%"><?php echo $zipZipCode; ?></td>
    <td width="13%"><?php echo $groups_city; ?> </td>
    <td width="11%" align="right"><?php echo $zipDistance; ?> KM</td>
   
  </tr>
 

My final code that I have changed is below
$i = 0;
$a = 0;
 
$computedDistance=array();
while ($i < $num) {
	$a++;
 
	$zipLatitude = mysql_result($result,$i,"Latitude");
	$zipLongitude = mysql_result($result,$i,"Longitude");
	$zipState = mysql_result($result,$i,"Province");
	$zipZipCode = mysql_result($result,$i,"PostalCode");
	$zipAreaCode = mysql_result($result,$i,"AreaCode");
	$zipCity = mysql_result($result,$i,"City");
	$zipCountyName = mysql_result($result,$i,"CountyName");
	$groups_id = mysql_result($result,$i,"groups_id");
	$groups_name = mysql_result($result,$i,"groups_name");
	$groups_city = mysql_result($result,$i,"groups_city");
	$zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "K"), 1);
 
 
 
 
	if ($zipDistance < $zipMilesHigh){
	
	$computedDistance[]=array("groups_name"=>$groups_name,"zipZipCode"=>$zipZipCode,"groups_city"=>$groups_city,"zipDistance"=>$zipDistance);
?>
 
 
<?php ?>
 
  
 
 
<?
}else{
$a--;
		//nothing	
	}
$i++;
}
usort($computedDistance, "cmp");
 
foreach($computedDistance as $subArr){
	echo "<tr><td>". implode("</td><td>" , $subArr) . " KM</td></tr>";
}
 
 
 
function cmp($a, $b)
{
    if ($a['zipDistance'] == $b['zipDistance']) {
        return 0;
    }
    return ($a['zipDistance'] < $b['zipDistance']) ? -1 : 1;
}
 
 
 
?>

Open in new window

0
 
hieloCommented:
>>it still isnt sorting...
use floatval() to compare the values:

function cmp2($a, $b)
{
    if (floatval($a['zipDistance']) == floatval($b['zipDistance'])) {
        return 0;
    }
    return ( floatval($a['zipDistance']) < (floatval($b['zipDistance'])) ? -1 : 1);
}

Open in new window

0
 
hieloCommented:
>>Also, how do i grab the array items one at a time
The elements of $computedDistance ARE arrays. You just need to use the "key" to extract the value of each one for every record/iteration.
foreach($computedDistance as $subArr){
 //
 echo $subArr['groups_name'] . ' ' .  $subArr['zipZipCode'];
}

Open in new window

0
 
jblayneyAuthor Commented:
Hello,

i dont know what im doing wrong, the display is working fine, but still not sorting, this is what im using now
<?
$i = 0;
$a = 0;
 
$computedDistance=array();
while ($i < $num) {
	$a++;
 
	$zipLatitude = mysql_result($result,$i,"Latitude");
	$zipLongitude = mysql_result($result,$i,"Longitude");
	$zipState = mysql_result($result,$i,"Province");
	$zipZipCode = mysql_result($result,$i,"PostalCode");
	$zipAreaCode = mysql_result($result,$i,"AreaCode");
	$zipCity = mysql_result($result,$i,"City");
	$zipCountyName = mysql_result($result,$i,"CountyName");
	$groups_id = mysql_result($result,$i,"groups_id");
	$groups_name = mysql_result($result,$i,"groups_name");
	$groups_city = mysql_result($result,$i,"groups_city");
	$zipDistance = number_format(DistanceCalc($zip1Latitude, $zip1Longitude, $zipLatitude, $zipLongitude, "K"), 1);
 
 
 
 
	if ($zipDistance < $zipMilesHigh){
	
	$computedDistance[]=array("groups_name"=>$groups_name,"zipZipCode"=>$zipZipCode,"groups_city"=>$groups_city,"zipDistance"=>$zipDistance,"groups_id"=>$groups_id);
 
}else{
$a--;
		//nothing	
	}
$i++;
}
usort($computedDistance, "cmp");
 
foreach($computedDistance as $subArr){
 
?>
 
 
  <tr align="left" valign="top">
    <td width="23%"><input name="groups" type="radio" value="<?php echo $subArr['groups_id']; ?>"> Add as Sponsor </td>
    <td width="37%" nowrap="nowrap"><a href="../groups/groups_edit.php?id=<?php echo $subArr['groups_id']; ?>"><?php echo $subArr['groups_name']; ?></a></td>
    <td width="16%"><?php echo $subArr['zipZipCode']; ?></td>
    <td width="13%"><?php echo $subArr['groups_city']; ?> </td>
    <td width="11%" align="right"><?php echo $subArr['zipDistance']; ?> KM</td>
   
  </tr>
  <tr>
    <td colspan="5" class="bg1"><img src="../images/spacer.gif" height="1" /></td>
   
  </tr>
<?php
}
 
 
function cmp($a, $b)
{
    if (floatval($a['zipDistance']) == floatval($b['zipDistance'])) {
        return 0;
    }
    return ( floatval($a['zipDistance']) < (floatval($b['zipDistance'])) ? -1 : 1);
}
 
 
?>

Open in new window

0
 
jblayneyAuthor Commented:
i just read something on php.net...



May be any scalar type. You cannot use floatval() on arrays or objects.
0
 
hieloCommented:
right BEFORE:
usort(...);

put:
print_r($computedDistance);

paste the output here:
0
 
jblayneyAuthor Commented:
Array ( [0] => Array ( [groups_name] => Central Pentecostal Church [zipZipCode] => L4Z 3L3 [groups_city] => Mississauga [zipDistance] => 6.5 [groups_id] => 342 )
[1] => Array ( [groups_name] => Queensway Christian College [zipZipCode] => M8Z 1T5 [groups_city] => Toronto [zipDistance] => 5.8 [groups_id] => 379 )
[2] => Array ( [groups_name] => Gathered Together [zipZipCode] => M8Z 5G8 [groups_city] => Toronto [zipDistance] => 6.6 [groups_id] => 380 )
[3] => Array ( [groups_name] => Justin Second Church [zipZipCode] => L4Y 3C5 [groups_city] => Mississauga [zipDistance] => 0.0 [groups_id] => 536 ) )

i added the line breaks
0
 
jblayneyAuthor Commented:
ok, i got it working,

i put the function at top of page...

function cmp($a, $b)
{
    if ($a['zipDistance'] == $b['zipDistance']) {
        return 0;
    }
    return ( $a['zipDistance'] < $b['zipDistance']) ? -1 : 1;
}



0
 
jblayneyAuthor Commented:
thanks for the help, you just gave me a quick lesson on using arrays which i wont forget...
0
 
hieloCommented:
you are welcome. Glad it all worked out!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 11
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now