Solved

# sorting SQL results inside a while loop

Posted on 2008-10-09
641 Views
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 = acos(\$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 ."Longitude >= (". \$varLongitude ." - ". \$varLongRange .")";
\$zipRadiusSQL_str = \$zipRadiusSQL_str ."Latitude <= (". \$varLatitude ." + ". \$varLatRange .")";
\$zipRadiusSQL_str = \$zipRadiusSQL_str ."Latitude >= (". \$varLatitude ." - ". \$varLatRange .")";
\$zipRadiusSQL_str = \$zipRadiusSQL_str ." AND Longitude <> 0 AND Latitude <> 0 ORDER BY PostalCode ASC";

}

//===================================================
// 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
Question by:jblayney
• 11
• 8

LVL 82

Expert Comment

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

ID: 22681728
can u apply that to my code above
0

LVL 1

Author Comment

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

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 = acos(\$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 ."Longitude >= (". \$varLongitude ." - ". \$varLongRange .")";

\$zipRadiusSQL_str = \$zipRadiusSQL_str ."Latitude <= (". \$varLatitude ." + ". \$varLatRange .")";

\$zipRadiusSQL_str = \$zipRadiusSQL_str ."Latitude >= (". \$varLatitude ." - ". \$varLatRange .")";

\$zipRadiusSQL_str = \$zipRadiusSQL_str ." AND Longitude <> 0 AND Latitude <> 0 ORDER BY PostalCode ASC";

}

//===================================================

// 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;

}

?>
``````
0

LVL 1

Author Comment

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 />
<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="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="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

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 />

<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="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="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 82

Expert Comment

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

ID: 22684397
yes, let me explain the whole process

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

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++;

}

...
``````
0

LVL 1

Author Comment

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="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;

}

?>
``````
0

LVL 82

Expert Comment

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);

}
``````
0

LVL 82

Expert Comment

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'];

}
``````
0

LVL 1

Author Comment

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="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);

}

?>
``````
0

LVL 1

Author Comment

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

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

put:
print_r(\$computedDistance);

paste the output here:
0

LVL 1

Author Comment

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 ) )

0

LVL 1

Author Comment

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

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

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

## Featured Post

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.