Tricky sorting problem

Hi there,

This is my first post, so I apologize if this is a dumb question or has been answered previously. I did search around a little, but didn’t know what exactly I was searching for.

Background:

The site I am building uses Javascript to get the the viewers current GPS coordinates and then uses them to build a SQL statement to pull out all the addresses nearby from a database. To accomplish this, I shave off 0.0045 from the latitude and longitude and use the following SQL :

SELECT id, address, lat, lon FROM mdu
WHERE lat BETWEEN ‘.$lat1.’ AND ‘.$lat2.’ AND lon BETWEEN ‘.$lon1.’ AND '.$lon2

This works great, and my resulting page shows all the results in a 2 km square block. I then use a PHP script to calculate the distance away from the current location to each of the locations from the database and then display them in a table. This also works great, and I see a distance in km away from each item.

My Problem:

I want to sort these results numerically from smallest to largest (shortest distance in km to farthest distance in km). I can’t use ORDER BY in my SQL because I have to get the data out of the DB and then perform a calculation on each item before I can sort it.

I thought about dropping everything into a PHP associative array and then using asort() to sort it and then another while loop to draw my table, but I don;t think this will work either since I have three pieces of data (the ID field from the DB, the address and the distance in km away). An associative array just holds two pieces of data.

The only thing I could think of was to extract my results, do the distance calculation and then insert everything back into a temporary MySQL table then re-extract the same data with a SQL SORT BY using the newly inserted distance calculation. This just seems ugly and inefficient, and I thought there must be a better way.

I am a little rusty on PHP and MySQL, I haven’t done a project in several years and I’m starting to remember it as I go, so maybe I’m missing something obvious? Any ideas?

not seems… is

:cool:

just sort them in the same php script that calculates the distance

I’m not sure how I can do this, since I’m processing them with a php WHILE loop and calculating each one at a time. Here’s my code :

This code grabs addresses from the DB only in a 2 km square

$km = 5;
$meters = $km * 1000;
$decimaldegrees = $meters * 0.000009;

$starting = 0.042; // 0.042 decimal degrees = 5km
$increment = 0.0045;	//increments 1 km each increment

$lat1 = ($_GET['lat'] - $decimaldegrees/2);
$lat2 = ($_GET['lat'] + $decimaldegrees/2);

$lon1 = ($_GET['lon'] - $decimaldegrees/2);
$lon2 = ($_GET['lon'] + $decimaldegrees/2);

$result = mysqli_query($link, '
SELECT id, address, lat, lon FROM mdu 
WHERE lat BETWEEN '.$lat1.' AND '.$lat2.' AND lon BETWEEN '.$lon1.' AND '.$lon2);

This code puts the results into an array that I use to draw the table

<?php 
while ($row = mysqli_fetch_array($result))
{
$mdus[] = array('id' => $row['id'], 'address' => $row['address'], 'lat' => $row['lat'], 'lon' => $row['lon']);

}

?>

This code calculates the distance away and displays it using echo



foreach ($mdus as $mdu): 

/*
	DISTANCE CALCULATION GOES HERE
	-need four things (latA, latB, lonA, lonB) then do pythag - then convert from dd to meters
	convert from meters to dd :  multiply meters by 0.000009 : example : 500m x 0.000009 = 0.0045 dd
	convert from dd to meters : divide dd by 0.000009 : example : 0.0045dd / 0.000009 = 500m
*/
	$latA = $mdu['lat'];	//latitude of the line item pulled from DB
	$latB = $_GET['lat'];	//latitude of the current location from the URL
	$lonA = $mdu['lon'];	//longitude of the line item pulled from DB
	$lonB = $_GET['lon'];	//longitude of he current location from the URL
	
	$A = $latA - $latB;
	$B = $lonA - $lonB;
	$C =  sqrt(($A*$A) + ($B*$B));
	
	$metersAway = ($C / 0.000009);
	$kmAway = $metersAway / 1000;
	echo round($kmAway, 1).' km';

I’m not sure at which stage I would do the calculation, because I need to calculate each one first, then somehow store it, then sort it and then display it.

Thanks for your quick response by the way!

Okay, so I moved the code that calculates distance up by the code that creates my array. The newly calculated distance is added to the array with the other stuff (id, address, lat and long) However, I’m still not able to get this working the way I want.

I start by querying my DB with this line :

$result = mysqli_query($link, '
SELECT id, address, lat, lon FROM mdu 
WHERE lat BETWEEN '.$lat1.' AND '.$lat2.' AND lon BETWEEN '.$lon1.' AND '.$lon2);

Then I use a while loop with mysqli_fetch_array($result) and assign it to $row


while ($row = mysqli_fetch_array($result))

Then in each iteration of the while loop, I create an array $mdus in which I put the various things I need (id, address, lat, long and the newly calculated “distance”).


$mdus[] = array('id' => $row['id'], 'address' => $row['address'], 'lat' => $row['lat'], 'lon' => $row['lon'], 'distance' => $distance);
asort($mdus);

In this example, 5 results (5 rows) exist, and I want to somehow display them in numerical order by distance.

If I use asort($mdus), doesn’t that just sort the various items in the array? I need to sort 5 different arrays and display them.

Maybe I’m not explaining this very well… I’ve attached a screenshot of what the website looks like. Any help would be greatly appreciated!!

Thanks

Why are you asking someone this? Just try it out and you will see.

Anyhow, I am not completely sure I have gathered what you want to accomplish, but I think you can use usort. Write a comparison function, something like this

function sort_compare ($search, $searched) {
		
	return ($search['distance'] > $searched['distance'] ? 1 : 
	($search['distance'] == $searched[distance'] ? 0 : '-1' ));

}

usort ($mdus, 'sort_compare');

http://www.php.net/manual/en/function.usort.php

I am tired though and may have misunderstood.

Heres an oldie but goodie…

The select works fine, I can attest to that, but can you bend it to your will?

I think I found the solution :

Thanks to "r937"s post, I was able to redesign my initial code that pulls the data from mySQL, then makes my distance calculation and then add all the mySQL data plus the newly calculated distance to a new PHP array. Next my problem was how to put all the rows in order. All of the sort functions don’t help, since I don’t want to sort within an array, I need to sort multiple arrays. It looks like I need a 2 dimensional array.

So… If I use a two dimensional array in PHP, then I can load all 5 arrays into another array then I can use uasort() with a “callback function” compare(). This then sorts the arrays numerically in order of whichever array slot I choose. In the example below I compare slot [4] of the array, which is my distance calculation, the thing I want to sort by.

Here’s my new code :

$addresses = array(	array("63","865 View St", "48.424962","-123.35971","0.3"),
					array("37","520 Dunedin", "48.4415893","-123.3740615","2.2"),
					array("56","845 Yates St", "48.425745","-123.360139","0.2"),
					array("57","939 Johnson St", "48.426329","-123.357886","0.5") );
					
function compare($x, $y)
{
	if ( $x[4] == $y[4] )
		return 0;
	else if ( $x[4] < $y[4] )
		return -1;
	else
		return 1;
}

uasort($addresses, "compare");

//print_r($addresses);

foreach ($addresses as $address):

	echo $address[1]." | ".$address[4]."<br>";

endforeach;

This works exactly the way I want it to. I just need to make a few modifications so that it creates the array from the mySQL data and not my hard-coded data, and I might change it to an associative array to make it more readable, or I might just comment the hell out of the code and abandon it forever :slight_smile:

I never fully understood multidimensional arrays before or why someone might use one, but this problem helped me break through that. Thanks for all the help to everyone who replied.

Scott