ORDERING BY my calculation

MitchEvans

Is there any way I can ORDER BY $sum so that the smallest one is shown first, the problem is because the sum comes after the SELECT statement.
This is a simplified version to my much larger problem. Cheers Guys!

$maths = "SELECT * FROM maths";
$mathsResults = mysql_query($maths);
while ($mathsRow = mysql_fetch_array ($mathsResults)){
$int1 = $mathsRow["int1"];
$int2 = $mathsRow["int2"];
$sum=$int1+$int2;
echo $sum;
}

Roger_Ramjet

Yes, you can do the calculation in the query and order by the results

SELECT int1, int2, (int1+int2) AS sum FROM maths ORDER BY sum

Don't use SELECT *, name the columns you want.

MitchEvans

ahhh I see, i'm calculating something by using a function so could this still be done through the statement? How would it be done?

calc_int_seperation("$int1","$int2")

This then goes into my function and is worked out.

Roger_Ramjet

Not sure that I follow you?

You can define and use User Defined Functions if that is what you want.

Otherwise, you can replicate the function with sql control flow and maths functions, eg:

SELECT int1, int2, (CASE WHEN int1<int2  THEN int2-int1 WHEN int1>int2 THEN int1-int2 ELSE 0 END) AS separation FROM maths

MitchEvans

function calc_postcode_seperation($pcodeA,$pcodeB) 
{ 
// PCODE A 

$result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$pcodeA' LIMIT 1"); 

$row=mysql_fetch_array($result); 

$gridn[0]=$row[Grid_N]; 

$gride[0]=$row[Grid_E]; 

// PCODE B 
$result=mysql_query("SELECT * FROM postcodes WHERE Pcode='$pcodeB' LIMIT 1"); 

$row=mysql_fetch_array($result); 

$gridn[1]=$row[Grid_N]; 

$gride[1]=$row[Grid_E]; 

// TAKE GRID REFS FROM EACH OTHER. 

$distance_n=$gridn[0]-$gridn[1]; 

$distance_e=$gride[0]-$gride[1]; 

// CALCULATE THE DISTANCE BETWEEN THE TWO POINTS 

$hypot=sqrt(($distance_n*$distance_n)+($distance_e*$distance_e)); 

$text .='Distance between '.$pcodeA.' and '.$pcodeB.' is: '.round($hypot/1000*0.6214,2).'miles'; 
$distance .=round($hypot/1000*0.6214,2);
return $distance; 
}

This is my function.

And this is what is input to it:

calc_postcode_seperation("$dir1","$checkpost");

$dir1 is the post code that a user has searched with and $checkpost is the postcode for the record in the database.
The distance between the two is then worked out.
It's this distance that I need the results to be ordered by.
Obviously the function is called within the SELECT statement so it can use each of the post codes in the database and work them out with the post code the user has searched with.

bpat1434

Well, what you could do is get all the results into an array, but make the key the distance. Then order by the key to give you an ordered distance

<?php

// ....

$result = mysql_query($query);

while($row = mysql_fetch_array($result))
{
  $key = calc_postcode_seperation($dir1, $checkpost);

  $rslt[$key] = $row;
}

ksort($rslt, SORT_NUMERIC); // Ascending order
krsort($rslt, SORT_NUMERIC); // Descending Order
?>

Hope that helps.

MitchEvans

Okay so I now have this in my statement:

$distancetocleaner = calc_postcode_seperation("$dir1","$checkpost");
$rslt[$distancetocleaner] = $directoryRow;

I have put this at the end:

ksort($rslt, SORT_NUMERIC); // Ascending order

So what do I do now as it looks identical at the moment with the above, how do i get the order to work?

MitchEvans

I still can't get them to ORDER BY the distance, the suggestion above has been tried but the page looks no different I think the ksort needs to be in a certain place maybe? Not really sure, would love to get this working!
Thanks for the help so far guys!🙂

Roger_Ramjet

Look, you really have not explained your data nor how you calculate 'distance' between postcodes. In the real world, distance between points on the earth is the great circle distance which can be calculated from their latitude and longditude. Now nothing so far has indicated that your postcodes are in any way related to geospacial coordinates of any kind.

Asking incomplete questions about false data is not going to get the help you need. All it is going to do is to produce answers that move you further away from the real problem.

So, explain yourself: your data structure, how it is stored and what it is used for.

MitchEvans

I have a table in my database that has co-ordinates for each UK postcode. As you can see from an above post the distance between two post codes is worked out.
My user will enter their post code into the search box and click submit. This post code is then compared to the post codes in my company table. Each company in my table has a post code. So what the code does is works out how far each company is from the user.
As the results are shown they are in a random order, so I would like them to be ordered by this distance from user to company so the user sees the nearest one to them first.
The function that calculates two post codes is found before the SELECT statement that gets the companys postcode. My problem is that I cannot ORDER BY this distance in the SELECT statement as the companys postcodes are not known until after the query.

Hope this makes more sense appreciate the help!

Roger_Ramjet

Well, those calculations can also be done in sql using the Maths Functions. The trouble is that as you add more companies to your list then the queries will take a long time. Now this same problem has been posted here before several times and most solutions discussed exhaustively. This thread is about exactly the same thing (USA so zip codes not post codes) and is still ongoing and very because it has moved into using pgsql. Other threads on this problem are
here
here
and plenty more if you search the board.

One thing I will point out is that you can use the MySQL Spatial Extensions for this, as discussed in the first thread I linked.

Whichever way you go, if you do it in the query then you can order on the result.