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
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']
// 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,"L
$zipLongitude = mysql_result($result,$i,"L
$zipState = mysql_result($result,$i,"P
$zipZipCode = mysql_result($result,$i,"P
$zipAreaCode = mysql_result($result,$i,"A
$zipCity = mysql_result($result,$i,"C
$zipCountyName = mysql_result($result,$i,"C
$groups_id = mysql_result($result,$i,"g
$groups_name = mysql_result($result,$i,"g
$groups_city = mysql_result($result,$i,"g
$zipDistance = number_format(DistanceCalc
if ($zipDistance < $zipMilesHigh){
?>
<?php echo $groups_name; ?> <?php echo $zipZipCode; ?><?php echo $groups_city; ?> <?php echo $zipDistance; ?> KM<br>
<?
}else{
$a--;
}
$i++;
}
?>
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
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']
// 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,"L
$zipLongitude = mysql_result($result,$i,"L
$zipState = mysql_result($result,$i,"P
$zipZipCode = mysql_result($result,$i,"P
$zipAreaCode = mysql_result($result,$i,"A
$zipCity = mysql_result($result,$i,"C
$zipCountyName = mysql_result($result,$i,"C
$groups_id = mysql_result($result,$i,"g
$groups_name = mysql_result($result,$i,"g
$groups_city = mysql_result($result,$i,"g
$zipDistance = number_format(DistanceCalc
if ($zipDistance < $zipMilesHigh){
?>
<?php echo $groups_name; ?> <?php echo $zipZipCode; ?><?php echo $groups_city; ?> <?php echo $zipDistance; ?> KM<br>
<?
}else{
$a--;
}
$i++;
}
?>
ASKER
can u apply that to my code above
ASKER
to further help, these are the fields needed
form postalcodes
PostalCode, Latitude, Longitude
from groups
groups_id, groups_name, groups_pc, groups_city
form postalcodes
PostalCode, Latitude, Longitude
from groups
groups_id, groups_name, groups_pc, groups_city
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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</optio
<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</opti
<option value="10">October</option
<option value="11">November</optio
<option value="12">December</optio
</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,"L
$zipLongitude = mysql_result($result,$i,"L
$zipState = mysql_result($result,$i,"P
$zipZipCode = mysql_result($result,$i,"P
$zipAreaCode = mysql_result($result,$i,"A
$zipCity = mysql_result($result,$i,"C
$zipCountyName = mysql_result($result,$i,"C
$groups_id = mysql_result($result,$i,"g
$groups_name = mysql_result($result,$i,"g
$groups_city = mysql_result($result,$i,"g
$zipDistance = number_format(DistanceCalc
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_edi
<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"
</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['
<input name="christmas_child" type="hidden" id="christmas_child" value="<?php echo $row_Recordset_christmas['
</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 } ?>
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</optio
<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</opti
<option value="10">October</option
<option value="11">November</optio
<option value="12">December</optio
</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,"L
$zipLongitude = mysql_result($result,$i,"L
$zipState = mysql_result($result,$i,"P
$zipZipCode = mysql_result($result,$i,"P
$zipAreaCode = mysql_result($result,$i,"A
$zipCity = mysql_result($result,$i,"C
$zipCountyName = mysql_result($result,$i,"C
$groups_id = mysql_result($result,$i,"g
$groups_name = mysql_result($result,$i,"g
$groups_city = mysql_result($result,$i,"g
$zipDistance = number_format(DistanceCalc
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_edi
<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"
</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['
<input name="christmas_child" type="hidden" id="christmas_child" value="<?php echo $row_Recordset_christmas['
</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 } ?>
ASKER
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 } ?>
>>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?)
Does that meant that those are the only values you are interested? (The ones within the if clause?)
ASKER
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
the array you are building is a great idea i think, but i dont know how to work with arrays
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
the array you are building is a great idea i think, but i dont know how to work with arrays
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++;
}
...
ASKER
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_edi
<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
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_edi
<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;
}
?>
>>it still isnt sorting...
use floatval() to compare the values:
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);
}
>>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.
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'];
}
ASKER
Hello,
i dont know what im doing wrong, the display is working fine, but still not sorting, this is what im using now
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);
}
?>
ASKER
i just read something on php.net...
May be any scalar type. You cannot use floatval() on arrays or objects.
May be any scalar type. You cannot use floatval() on arrays or objects.
right BEFORE:
usort(...);
put:
print_r($computedDistance)
paste the output here:
usort(...);
put:
print_r($computedDistance)
paste the output here:
ASKER
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
[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
ASKER
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;
}
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;
}
ASKER
thanks for the help, you just gave me a quick lesson on using arrays which i wont forget...
you are welcome. Glad it all worked out!
ex:
SELECT field1, field2, (field1 + field2) as field3
FROM table_name ORDER BY (field1 + field2);