[RESOLVED] Order By Distance

[chalex90]

So far i have a searching script that will return something like this (simplified obv):

$sql = etc;
$result = etc;
while($list = $result){
echo $list['name'];
echo $list['address'];
echo distance_calc($userpostcode, $SQL postcode);
};

which all works fine. i also have an order by button which works as well, however this can only order things through the sql query, because the distance is worked out out external to the sql query i can't figure out a way of ordering it by that.

the distance function uses an sql database of postcodes and latitude longitude coordinates then works out the hypotenuse of a triangle and because it has to be worked out separately for each user i can't store a fixed distance in the sql database. Any help will be appreciated.

[bradgrafelman]

chalex90 wrote:

because it has to be worked out separately for each user i can't store a fixed distance in the sql database.

So? SQL has plenty of math operations - why not incorporate the distance calculation into the SQL query itself?

chalex90 wrote:

because the distance is worked out out external to the sql query i can't figure out a way of ordering it by that.

Well if you still insist on working out the distance separately, then simply retrieve the SQL results unordered, store them in an array, and then do the ordering yourself in PHP.

[chalex90]

ok so how would i go about putting the SQL information into an array then calling it out ordered by a certain fieldset in the array.

[johanafm]

$a = array();
while ($row = fetchnextrow()) {
	$a[] = $row;
}	

[chalex90]

yea i knew that bit but how do you order it when outputting from the array

[johanafm]

Oh, I see. Well, either set distance as key of the top level array as in

$arr[$row['distance']][] = $row;

Then use asort. The reason I'm creating an array of rows for each distance is in the case that two or more rows have the same distance. If you know that will never happen, you could just $arr[$row['distance']] = $row;

Or use the approach from example 3 in array_multisort

[chalex90]

ok nice one, I'll get experimenting.

[chalex90]

Ok, i've been doing some playing around and got it to work. I thought just in case someone else is looking to do the same thing here's a simplified version of what i'm using:

$result = mysql_query($_SESSION[$search.'sql']) or trigger_error("SQL", E_USER_ERROR);
while ($list = mysql_fetch_assoc($result)) {

if($_POST['submit'] == "Show Distance"){
//If user has set postcode create distance info.
	//Users postcode
	$pcodeA = strstr($_SESSION['postcode'], ' ', true);
	//Database Postcode
        $pcodeB = strstr($row['postcode'], ' ', true);
	//distance variable
	$distance = calc_postcode_seperation($pcodeA,$pcodeB);
}else{
//User hasn't submitted distance so distance variable is blank
	$distance = "";
};


$data[] = array('name' => $list['name'], 'distance' => $distance);
	};

// Obtain a list of columns
    foreach ($data as $key => $row) {
		$name[$key]  = $row['name'];
	$distance[$key] = $row['distance'];
}

// Sort the data with volume descending, edition ascending
// Add $data as the last parameter, to sort by the common key
array_multisort($distance, SORT_DESC, $name, SORT_ASC, $data);

foreach($data as &$list){
	echo $list['name']."<br>";
	echo $list['distance']."miles";
};
};

If anyone has any suggestions to optimize it id love to know.

[chalex90]

actually, tell a lie, this is returning this:
Warning: array_multisort() [function.array-multisort]: Argument #1 is expected to be an array or a sort flag in etc.