Ordering columns question

[amg182]

Hi guys.

 

I have a webpage echoing out the a table of client details. I also have a table that contains all the UK postcodes that will work out the distance from the clients home to a postcode that can be entered in a form(variable).  In other words a user can enters a postcode into the form and it willl show how many miles the clients are from them. This is working fine, but would like to sort the results ASC by distance. I now how to order table columns wihtout any problems but because the distance is variable depending on the postcode entered, I have no idea how to go about getting the results to order ASC.

 

Would I need to temporarily store the variable in the table and then sort from this field. or is this even possible?

 

Any ideas would be greatly appreciated!

[AyKay47]

lets have a look at the code and approach this the best way...

[mikesta707]

This depends entirely on how you calculate the distance. As AyKay said posting the code will surely help. Until then, all I can offer is that you first store the results from the table into an array, and use PHP to sort that array based on distance. This solution isn't optimal however, if you are able to use SQL to sort it. However, without any code I can't say for sure

[amg182]

Hi guys.

 

Thanks for reply, its quite lenghty so i cut it done to the main parts. Sorry if its unorganised... I have placed it on my server so you can see whats happening.

 

http://cars.netau.net/index.php

 

Here is the code:

 

<?php
  $conn = connect();
  $showrecs = 3;
  $pagerange = 30;

  $a = @$_GET["a"];
  $recid = @$_GET["recid"];
  $page = @$_GET["page"];
  if (!isset($page)) $page = 1;

  switch ($a) {
    case "view":
      viewrec($recid);
      break;
    default:
      select();
      break;
  }

  if (isset($order)) $_SESSION["order"] = $order;
  if (isset($ordtype)) $_SESSION["type"] = $ordtype;
  if (isset($postcode)) $_SESSION["postcode"] = $postcode;

  mysql_close($conn);
?>


<?php function select()
  {
   global $a;
  global $showrecs;
  global $page;
  global $postcode;
  global $order;
  global $ordtype;

if ($a == "reset") {

    $order = "";
    $ordtype = "";
  }

  $checkstr = "";
  
  if ($ordtype == "asc") { $ordtypestr = "desc"; } else { $ordtypestr = "asc"; }
  $res = sql_select();
  $count = sql_getrecordcount();
  if ($count % $showrecs != 0) {
    $pagecount = intval($count / $showrecs) + 1;
  }
  else {
    $pagecount = intval($count / $showrecs);
  }
  $startrec = $showrecs * ($page - 1);
  if ($startrec < $count) {mysql_data_seek($res, $startrec);}
  $reccount = min($showrecs * $page, $count);
?>

<form action="index.php?" method="post">
<p>Enter Your Postcode:(i.e W12)</p><input type="text" name="postcode" maxlength=4/">
<input type="submit" name="submit" value="Enter Postcode" />
</form>

<?php

//1st criteria check

if ( $postcode == "" ) {
  print "No postcode set.";
}
else {
print "Your Postcode $postcode";
}
?>

<table border="1">
<tr>
<tr>
<td><a class="hr" href="index.php?order=<?php echo "Model" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Model") ?></a></td>
<td><a class="hr" href="index.php?order=<?php echo "Year" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Year") ?></a></td>
<td><a class="hr" href="index.php?order=<?php echo "Make" ?>&type=<?php echo $ordtypestr ?>"><?php echo htmlspecialchars("Make") ?></a></td>
</tr>
</table>


<?php
  for ($i = $startrec; $i < $reccount; $i++)
  {
    $row = mysql_fetch_assoc($res);
?>
<table border="1">
<tr>
<td><?php echo htmlspecialchars($row["Make"]) ?></td>
<td><?php echo htmlspecialchars($row["Model"]) ?></td>
<td><?php echo htmlspecialchars($row["Year"]) ?></td>
<br></br>
</tr>
<?php

//working out the distance from Postcode entered and the postcodes stored within table

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

//finds value from database field

{
$ends[]=$row['Location'];
}

// Postcode enterd 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 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.
$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))/1.609;

//VARIABLE FOR ORDER
$dist=''.round($hypot/1000,0).'';

global $dist;

if ( $postcode == "" ) {

  echo '
   <td>No postcode set</td>
   </tr>';
}
else {
  echo '
   <td>Distance:<br><b/br>'.$dist.' Miles</td>
   </tr>';
   }
}
?>

 

Thanks again

[Psycho]

Assuming you are calculating the distance in PHP logic the solution is to order the results in PHP. Do a pre-processing of the data and dump the results into a multi-dimensional array. Then create a function to sort the array using usort()

 

Example code:

//Create and run query
$query = "SELECT * FROM table_name"l
$result = mysql_query($query);

//Process the results into array
$resultAry = array();
while($row = mysql_fetch_assoc($result))
{
    //calculate the distance
    // - insert code here to get distance
    
    //Put record + distance into array
    $resultAry = array_merge($row, array('distance'=>$distance));
}

//function to custom sort the array
function sortByDistance($a, $b)
{
    if ($a['distance'] == $b['distance']) { return 0; }
    return ($a['distance'] < $b['distance']) ? -1 : 1;
}

//Sort the results array
usort($resultAry, 'sortByDistance');

//Use foreach to iterate through the sorted array to output results
foreach($resultAry as $record)
{
    //Insert code to create HTML output
}

[kickstart]

Hi

 

You can either store all the resulting rows in an array and then sort the array, or you can go the calculation within the SQL (something I recently had to do in SQL server) and put an ORDER BY in the SQL.

 

All the best

 

Keith

[amg182]

Hi guys, thanks for your replies.

 

or you can go the calculation within the SQL (something I recently had to do in SQL server) and put an ORDER BY in the SQL.

 

I would much prefer to sort be SQL. Is it possible to do? Because the result of the distances to the clients is stored in a variable called $dist; how can that be sorted using within SQL?

 

Thanks again guys