Good Tut for PHP search results - Paged and Ordered

[mat3000000]

I need to make a results page, which will display all the different results - paged. This is easy enough, however I need to calculate the distance between a postcode entered and the one stored in the database record, and order by this if the "Sort By:" drop down box is set to distance.

I have been trying for a couple of days now and I can't figure anything out.

 

The problem is that the results are obtained by a query and where clause, and in the where clause is where you get the second postcode from. This means, the query is already run and the results order has already been determined.

 

I will post the script I have now (Not Finished). I would be very grateful if someone will take a look, or direct me to a good tutorial on this.

 

$sector = mysql_real_escape_string(trim($_GET['sector']));
$job = mysql_real_escape_string(trim($_GET['job']));
$exp = mysql_real_escape_string(trim($_GET['exp']));
$postcode = mysql_real_escape_string(trim($_GET['postcode']));

if(!is_numeric($exp)){$errors[] = 'The Experience Field must contain only numbers';}

//POSTCODE VAL
function validatePostcode($str)
{.......}

if (!validatePostcode($postcode))
{$errors[] = 'Postcode is Invalid';}


$postcode2 = strtoupper(str_replace(" ", "", trim($postcode))); 
$buspostcode = substr($postcode2, 0, -3);


function calc_postcode_seperation($pcodeA,$pcodeB)
{........}


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

$limit=10;

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

$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;
  }

  
  
//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>";
  
}
}
}

[mat3000000]

Anyone? Please?

[jcbones]

You will need to do the calculations in MySQL, because you need to order by the distance.  I would suggest http://www.goondocks.com/blog/08-01-22/zip_code_radius_search_using_mysql.aspx to start, and maybe http://www.scribd.com/doc/2569355/Geo-Distance-Search-with-MySQL to optimize.