Order/Sort mysql results

[amg182]

Hi guys,  Having problems sorting mysql results. I can easily order/sort table columns but would like to order the results by a variable.(Distance in Miles).

Havent much experience in PHP and really struggling with this. Basically a query echoing out car make and models and how far they are located from a postcode. Can get it to work no porblem, but not to order/sort by least distance.

 

Heres what i'm working with:

$res=mysql_query("SELECT * FROM cars");
while ($row = mysql_fetch_assoc($res)){

echo $row['Make'];
echo $row['Model'];

//variable will be determimed by user input form
$postcode="W12 3SR";

$start=$postcode;
$ends = array();

//finds value from database field
{
$ends[]=$row['Location'];
}

// Postcode entered by user via input form
$result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$start'");
while($row=mysql_fetch_array($result)){
$gridn[0]=$row['Grid_N'];
$gride[0]=$row['Grid_E'];
}

foreach($ends as $fin){
// Postcodes within mysql table
$result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$fin'");
while($row=mysql_fetch_array($result)){
	$gridn[1]=$row['Grid_N'];
	$gride[1]=$row['Grid_E'];
}

// TAKE GRID REFS FROM EACH OTHER TO WORK OUT DISTANCE.
$distance_n=$gridn[0]-$gridn[1];
$distance_e=$gride[0]-$gride[1];

// CALCULATE THE DISTANCE BETWEEN THE TWO POSTCODES AND DIVIDE BY 1.6 TO CONVERT KM TO MILES
$hypot=sqrt(($distance_n*$distance_n)+($distance_e*$distance_e))/1.609;

//VARIABLE FOR DISTANCE AND ROUNDED OF TO NEAREST WHOLE NUMBER.
$distance=''.round($hypot/1000,0).'';


  echo " $distance miles";
  echo "<br>";
}
}
?>

 

I wish i could just do something like this but isnt possible, is it ?

"Select * FROM cars ORDER BY $distance";

 

Thanks!

[jcbones]

In order to sort by a distance within MySQL, you would need to build the function inside MySQL.  There are ways to do that, but they can get confusing.  This tutorial should get you started, although you will have to make note that he isn't storing his data like you are.  So it would have to be modified.

[amg182]

Hi Thanks for reply, will take a look at this.

 

Can usort, or any other sort function sort MYSQL results, i know the can sort varibable and standard arrays etc..

[jcbones]

Once data is passed back to the script from a database, you can manipulate that data with any and all functions of the programming language you are using.

[amg182]

Ok, that makes sense. I have been trying to sort mysql results using sort functions, but no luck yet. My lack of experince is letting me down.

 

I will keep trying... :-\

 

Thanks

[Psycho]

Ok, that makes sense. I have been trying to sort mysql results using sort functions, but no luck yet. My lack of experince is letting me down.

 

I will keep trying... :-\

 

This has already been answered in your previous post. Either:

 

1. Build the logic into MySQL to calculate the distance and create a dynamic value to use the MySQL sorting functions on or

 

2. Get all the results from MySQL then process the data into a multi-dimensional array (while also creating the distance value for each record). Then you can sort the results using one of the PHP sorting functions. However, as I stated in your earlier post, this will not allow you to use the queries for pagination. Since you need to use LIMIT to get the correct results for the page AFTER they are sorted. So, if you go this route, you will need to extract the correct "page" of results from the array of all records.