Solved

sorting SQL results inside a while loop

Posted on 2008-10-09
19
641 Views
Last Modified: 2013-12-12
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
Comment
Question by:jblayney
  • 11
  • 8
19 Comments
 
LVL 82

Expert Comment

by:hielo
ID: 22681471
>>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
 
LVL 1

Author Comment

by:jblayney
ID: 22681728
can u apply that to my code above
0
 
LVL 1

Author Comment

by:jblayney
ID: 22683714
to further help, these are the fields needed

form postalcodes
PostalCode, Latitude, Longitude

from groups
groups_id, groups_name, groups_pc, groups_city
0
 
LVL 82

Accepted Solution

by:
hielo earned 250 total points
ID: 22684082
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
 
LVL 1

Author Comment

by:jblayney
ID: 22684344
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
 
LVL 1

Author Comment

by:jblayney
ID: 22684351
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
 
LVL 82

Expert Comment

by:hielo
ID: 22684363
>>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
 
LVL 1

Author Comment

by:jblayney
ID: 22684397
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
 
LVL 82

Expert Comment

by:hielo
ID: 22684403
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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
LVL 1

Author Comment

by:jblayney
ID: 22685328
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
 
LVL 82

Expert Comment

by:hielo
ID: 22686095
>>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
 
LVL 82

Expert Comment

by:hielo
ID: 22686121
>>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
 
LVL 1

Author Comment

by:jblayney
ID: 22686237
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
 
LVL 1

Author Comment

by:jblayney
ID: 22686262
i just read something on php.net...



May be any scalar type. You cannot use floatval() on arrays or objects.
0
 
LVL 82

Expert Comment

by:hielo
ID: 22686271
right BEFORE:
usort(...);

put:
print_r($computedDistance);

paste the output here:
0
 
LVL 1

Author Comment

by:jblayney
ID: 22686335
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
 
LVL 1

Author Comment

by:jblayney
ID: 22686717
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
 
LVL 1

Author Comment

by:jblayney
ID: 22686728
thanks for the help, you just gave me a quick lesson on using arrays which i wont forget...
0
 
LVL 82

Expert Comment

by:hielo
ID: 22687470
you are welcome. Glad it all worked out!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now