ORDER BY Problem

[c p c]

I want to be able to order my database results by price and/or distance.

I have no problem with the price, it just gets it from the DB column but the distance is calculated from two postcodes and I'm left with the vaiable '$distance' (1 mile, 50 miles etc).

Any ideas how I can sort this distance LOW-HIGH?

hers the code:


// /////////////////////////
// OPEN DATABASE AND CONNECT
// /////////////////////////

$db_connection = mysql_connect("#", "#", "#")
or die("Could not connect to db: " . mysql_error() . "Please contact technical support to report this error.");

mysql_select_db ("pluto", $db_connection)
or die("Could not connect to table: " . mysql_error() . "Please contact technical support to report this error.");

// //////////////////////////////////
// RETURN NUMBER OF RECORDS IN SEARCH
// //////////////////////////////////

$myTblTot = "SELECT ID FROM testCars WHERE model LIKE '%" . $model . "%'" . $sqlPostcodeStr . $sqlPriceStr . $sqlYearStr . $sqlMileageStr . ";";

$total_result = mysql_query($myTblTot, $db_connection)
or die("Could not get results: " . mysql_error() . "Please contact technical support to report this error.");

$count = 0;

while ($row = mysql_fetch_object ($total_result))
{
$count = $count + 1;
}

// /////////////////////////////////////////////
// WORK OUT HOW MANY PAGES OF RESULTS THERE ARE
// /////////////////////////////////////////////

if ($limit == "") {
$limit = 0;
}

$setLimit = $limit * 20;
$pagesIndexed = floor($count / 20);
$searchPages = $pagesIndexed + 1;
$currentPage = $limit + 1;

print "\n\t\t\t\t<TR BGCOLOR=#FFFFFF>";
print "\n\t\t\t\t\t<TD COLSPAN=7 CLASS=\"whitelink\"><IMG SRC=\"images/1px.gif\" WIDTH=14 HEIGHT=1>$count cars found. Displaying page $currentPage of $searchPages</TD>";
print "\n\t\t\t\t</TR>";

$myTblTot = "";








// ///////////////////////////////////////////
// QUERY DATABASE WITH SQL AND RETURN RECORDS
// ///////////////////////////////////////////

$myquery = "SELECT * FROM testCars WHERE PRICE > 0 AND model LIKE '%" . $model . "%'" . $sqlPostcodeStr . $sqlPriceStr . $sqlYearStr . $sqlMileageStr . "ORDER BY price, model, mileage, year LIMIT $setLimit, 20;";

$query_result = mysql_query($myquery, $db_connection)
or die("Could not get results: " . mysql_error() . "Please contact technical support to report this error.");

$count = 0;



while ($row = mysql_fetch_object ($query_result))
{
if ($count % 2) {
$bgColor = "#EAEBED";
} else {
$bgColor = "#C1D0F3";
}

// ///////////////////////////////////////////
// WORK OUT POSTCODE DISTANCE
// ///////////////////////////////////////////


$distance = distance_between_postcodes (array(
'from_postcode' =>" $pcode ",
'to_postcode' =>" $row->postcode "

));

$strippedModel = str_replace("[no trim]", "", str_replace("'", "", $row->model));
$strippedModel = str_replace("(2001)", "", $strippedModel);
$strippedModel = str_replace("(2002)", "", $strippedModel);
$modelString = "postcode=" . $row->postcode . "&price=" . $row->price . "&dealer=" . urlencode($row->dealership) . "&model=" . rtrim($row->year) . "%20" . urlencode($strippedModel) . "%20" . urlencode("- " . $row->colour);

print "\n\t\t\t\t\t<TR BGCOLOR=" . $bgColor . ">";
print "\n\t\t\t\t\t\t<TD WIDTH=40 ALIGN=\"center\" CLASS=\"bodybig\">" . $row->year . "</TD>";
print "\n\t\t\t\t\t\t<TD WIDTH=15 ALIGN=\"center\" CLASS=\"bodybig\"><IMG SRC=\"images/1px.gif\" WIDTH=1 HEIGHT=16>P</TD>";
if ($row->flag1 != "") {
print "\n\t\t\t\t\t\t<TD CLASS=\"bodybig\"><B><a Href=\"http://##?url=" . urlencode($row->flag1) . "\" TARGET=\"new\">" . $strippedModel . "</B></a></TD>";
} else {
print "\n\t\t\t\t\t\t<TD WIDTH=250 CLASS=\"bodybig\"><B><a Href=\"details.htm\" onclick=\"NewWindow(this.href,'name','800','560','yes');return false;\">" . $strippedModel . "</B></a></TD>";
}
print "\n\t\t\t\t\t\t<TD WIDTH=30 ALIGN=\"center\"><IMG SRC=\"images/camera.gif\" ALT=\"Click for more details and pictures of this vehicle\"></TD>";
print "\n\t\t\t\t\t\t<TD WIDTH=70 ALIGN=\"center\" CLASS=\"bodybig\">" . $row->colour . "</TD>";
print "\n\t\t\t\t\t\t<TD WIDTH=55 ALIGN=\"center\" CLASS=\"bodybig\">" . $row->mileage . "</TD>";
print "\n\t\t\t\t\t\t<TD WIDTH=60 ALIGN=\"center\" CLASS=\"bodybig\">£<B>" . $row->price . "</B></TD>";
print "\n\t\t\t\t\t\t<TD WIDTH=60 ALIGN=\"center\" CLASS=\"bodybig\"><B>" . $distance . "<i>&nbsp;miles</i></B></TD>";
print "\n\t\t\t\t\t</TR>";
$count = $count + 1;
}

// /////////////////////////////////////////////////////
// IF NOTHING FOUND, REDIRECT PAGE AND DISPLAY ALL CARS
// /////////////////////////////////////////////////////

if ($count == 0) {
print "\n\t\t\t\t<TR BGCOLOR=#000000>";
print "\n\t\t\t\t\t<TD COLSPAN=8 ALIGN=\"center\"><FONT FACE=arial SIZE=1 COLOR=#FFFFFF>No exact matches for your search.<BR></FONT></TD>";
print "\n\t\t\t\t</TR>";
}

// ////////////////////////////////////////////////

$query_result = "";

mysql_close($db_connection);

// ///////////////////////////////
// ADD LINK BAR AT BOTTOM OF PAGE
// ///////////////////////////////

$previous = $limit - 1;
$next = $limit + 1;
$last = $pagesIndexed;
print "\n\t\t\t\t<TR BGCOLOR=\"#ccccff\">";
print "\n\t\t\t\t\t<TD COLSPAN=8 ALIGN=\"center\">";
if ($pagesIndexed > 0 & $limit > 0) {
print "\n\t\t\t\t\t<a Href=\"pcresults.php?model=$model&pcode=$pcode&pricerange=$pricerange&limit=0\"><IMG SRC=\"images/start.gif\" BORDER=0></a>";
}

if ($limit > 0) {
print "\n\t\t\t\t\t&nbsp;<a Href=\"pcresults.php?model=$model&pcode=$pcode&pricerange=$pricerange&limit=" . $previous . "\"><IMG SRC=\"images/previous.gif\" BORDER=0></a>&nbsp;";
}

if ($limit < $pagesIndexed) {
print "\n\t\t\t\t\t&nbsp;<a Href=\"pcresults.php?model=$model&pcode=$pcode&pricerange=$pricerange&limit=" . $next . "\"><IMG SRC=\"images/next.gif\" BORDER=0></a>&nbsp;";
}

if ($pagesIndexed > 0 & $pagesIndexed > $limit) {
print "\n\t\t\t\t\t<a Href=\"pcresults.php?model=$model&postcode=$pcode&pricerange=$pricerange&limit=" . $last . "\"><IMG SRC=\"images/end.gif\" BORDER=0></a>";
}
print "\n\t\t\t\t\t</TD>";
print "\n\t\t\t\t</TR>\n";

?>

[modernlife]

Here's one solution:

Instead of generating the table based directly from $row = mysql_fetch array();, make a two dimensional array with all your results in it, then calculate the distance for each array assignment. Finally, organize the new array however you want (like sort based on distance), then you display the table.

So, a quick, dirty, untested example solely for the purpose of illustrating my point would be:


while( $row = mysql_fetch_array( $result ) ) {
$distance = distance_between_postcodes (
array(
'from_postcode' =>" $pcode ",
'to_postcode' =>" $row->postcode "
)
);

$output[] = array( $row['foo']. $row['bar'], $distance );

}

// now you have your $output array compiled with distances computed
// at this point just sort the array and then build your table
// just as you did before, and you're all set.

[c p c]

thanks.

I'll give it a try and see what happens