Search Mysql Ordering Results Help

[mat3000000]

Here is my code, I want to order by distance, but can't see a way in which I could do it, Any help is appreciated...

 

//Connect to db further up

//Get Variables - I have validated these btw
$sector = mysql_real_escape_string(htmlentities(trim($_GET['sector'])));
$job = mysql_real_escape_string(htmlentities(trim($_GET['job'])));
$exp = mysql_real_escape_string(htmlentities(trim($_GET['exp'])));
$postcode = mysql_real_escape_string(htmlentities(trim($_GET['postcode'])));


if(isset($_GET['submit'])){
if(empty($errors)){


$limit=10;

$query = "select * from staff where sector='$sector' AND job='$job' AND exp>='$exp' "; 

$numresults=mysql_query($query);
$numrows=mysql_num_rows($numresults);

// If we have no results

if ($numrows == 0)
  {
  echo "<br /><tr><td class='errorbox'>Sorry, your search returned no results</td></tr>";
  }

if ($numrows > 0){

// next determine if s has been passed to script, if not use 0
  if (empty($s)) {
  $s=0;
  }




$query .= " order by '$distance'";
  $result = mysql_query($query);
  
  
  
//Get Results
$query .= " limit $s,$limit";
  $result = mysql_query($query) or die("Couldn't execute query");
// begin to show results set


$count = 1 + $s ;

// now you can display the results returned
  while ($row= mysql_fetch_array($result)) {
  
$username = $row['username'];
$fname = $row['fname'];
$lname = $row['lname'];
$sector = $row['sector'];
$job = $row['job'];
$exp = $row['exp'];
$pay = $row['pay'];
$town = $row['town'];
$county = $row['county'];
$theirpostcode = $row['postcode'];
$postcode1 = strtoupper(str_replace(" ", "", trim($theirpostcode))); 
$theirpostcode = substr($postcode1, 0, -3);

$distance = calc_postcode_seperation($buspostcode,$theirpostcode);




  echo "<tr class='profilebox' style='padding:10px; display:block;'>
  
  <td width='120' height='120' align='center'><img src='/staff/files/$username/profilepic/tn_profilepic.jpg'</td>
  <td width='40'> </td>	
  
  <td width='190'>$fname $lname<br />
      $sector - $job<br /><br /><br />Experience: $exp years<br /><br />
  Expected Pay: £$pay /hr<br /></td>
  
  <td width='40'> </td>
  
  <td width='160' align='center'>Feedback Score:<br /><span style='font-size:15pt;'>?/10</span> </td>
  
  <td width='40'> </td>
  
  <td valign='bottom' align='right' width='160'>
  Distance: $distance<br />$town, $county
  </td>	
  </tr>
  		<tr><td height='20'></td></tr>";
  
  
  $count++ ;
  }

$currPage = (($s/$limit) + 1);

//break before paging
  echo "<br />";

  // next we need to do the links to other results
  if ($s>=1) { // bypass PREV link if s is 0
  $prevs=($s-$limit);
  print " <a href=\"$PHP_SELF?s=$prevs&q=$var\"><< 
  Prev</a>&nbsp ";
  }

// calculate number of pages needing links
  $pages=intval($numrows/$limit);

// $pages now contains int of pages needed unless there is a remainder from division

  if ($numrows%$limit) {
  // has remainder so add one page
  $pages++;
  }

// check to see if last page
  if (!((($s+$limit)/$limit)==$pages) && $pages!=1) {

  // not last page so give NEXT link
  $news=$s+$limit;

  echo " <a href=\"$PHP_SELF?s=$news&q=$var\">Next >></a>";
  }

$a = $s + ($limit) ;
  if ($a > $numrows) { $a = $numrows ; }
  $b = $s + 1 ;
  echo "<tr><td height='30' valign='bottom'><p>Showing results $b to $a of $numrows</p></td></tr>";
  
}




}
}

[Psycho]

Impossible to help with what you have provided. You are using the function "calc_postcode_seperation" which is not in the script you have posted. You will have to duplicate the logic for calculating the distance using MySQL math functions to define a dynamic field (e.g.'distance'). Then you can sort the results in MySQL using that field.

 

Alternatively, you can dump the results into an array and while doing that calculate the distance and add the field for each record into the array. Once you have completed that, you could then sort the array by that field.

 

Also, why are you using htmlentities() on the values before using them for a db query?

 

 

EDIT: I just noticed that this is a pagination script. So, you really want to do this in the MySQL query. If you have to go the array route you would have to get ALL the records from the database, calculate the distance, then extract the current appropriate records for the current page. If you can't do it in MySQL and you don't have a lot of records it may be feasible, but is definitely a "hack" solution.

[mat3000000]

Thanks for your answer, but I did not really fully understand, could you tell me what you mean, here is the formula I used...

 

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 = round($hypot/1000,1).'kms     '.$pcodeA.'  --  '.$pcodeB;
return $text;


}

[Psycho]

I'll try to explain again.

 

You are currently selecting data from the data, then when you process that data you are using PHP code to calculate the distance, correct? Since the distance is not included in the database you cannot sort the data using that in the query - UNLESS you can move the distance calculation into the query. You can, possibly, create a dynamic field in the query that is a calculation of the distance. Here is a query that would return three values: fielda, fieldb and a dynamically generated field "sumAandB" that is the sum of fielda and fieldb

SELECT fielda, fieldb, (fielda + fieldb) AS sumAandB

 

Now, if the appropriate math functions are not available in MySQL for you to do the distance calculation in MySQL, then you will need to pull the records from the database first, run them through a process in PHP to calculate the distance but do not display them. Instead just store the records into an array along with the calculated distance value. Then you would sort the array using the distance value. The problem you have here is that you want to do pagination. So, if you wanted the records on page 2 with the sort order by distance you have no way in the query to determine which records those are without knowing the distance ahead of time. So, you would have to query ALL the records, calculate the distance as you add them to an array and then extract the relevant records from the array for the current page.

 

I can also think of a third option if you can't do the calculations within the query. I assume that the start point will not change (or at least won't change as the user is paging through the records). So, one possibility would be to pull all the records the first time the user selects/changes the start location, go through a process to determine the distance, the save the record ID and the distance to a temporary table. Then you could do a JOIN on the main table and the temp table and be able to sort by distance. I've not worked with temporary tables, so you would have to do some research if that is the route you take.

 

I was going to take a crack at modifying your code, but it's pretty unorganized and I just wasn't willing to invest the time.