Price descending order problem

[dave_cdv]

I'm hoping someone can help me with this as I'm fairly new to MySQL.

 

I have a holiday rental peoperty website and at the moment all the properties are listed in search results are ordered by the home_no (a unique auto generated number) this gives the first properties that sign up an advantage as they are always at the top of the search results.

 

Therefore I want to give an option to sort by lowest price.

 

The problem is that the rates are stored in a seperate table to the value that is searched (city).

 

Therefore I want to select home_no from the one table, and sort by the value of the lowest price field in another table.

 

Does that make sense? Any ideas on how I should do this?

[fenway]

Well, you'll need a JOIN... could you post the relevant table structures?

[adi5402]

you need to Get the data from the table with the data you want to sort first then use a while look inside the outer loop to get the rest of the data.

 

can you list the table structures of both tables

 

[adi5402]

what is the secondry field (the field that exists in both tables)

[adi5402]

im sorry im getting back to this stuff after a long break so my refrences are messed around.

 

I have 2 tables Table 1 stores book info

 

Table 2 stores ranks of the books in your case price.

 

So this is what i did to display my data based on ranks

 

 

$sql2 = 'SELECT * FROM `rank` ORDER BY `RankUK` ASC LIMIT 0, 100 ';
$results2= mysql_query($sql2) or die(mysql_error());
$count2 =0;

while($row2 =mysql_fetch_array($results2)){
$count2 = $count2+1;




$model =$row2['book_ISBN'];
$RankUK=$row2['RankUK'];
$RankUS=$row2['RankUS'];
$RankCAN=$row2['RankCAN'];



$sql = 'SELECT * FROM `products` WHERE `book_ISBN` = "'.$model.'"';

$result = mysql_query($sql);
if (!$result) {
    echo 'Could not run query: ' . mysql_error();
    exit;
}
$row = mysql_fetch_row($result);
$title =$row[1];
$Author=$row[2];
$model=$row[3];

echo "<tr><td>$count2</td><td>$title </td><td>$Author</td><td>$model</td><td>$RankUK</td><td>$RankUS</td><td>$RankCAN</td></tr>";




}// edn while loop

 

In my case my secondary field is the  book_ISBN  you have to do something similar treat the price table like my rank table and then run the query for your product data inside the while loop

 

i hope this helps

 

 

 

[fenway]

Try this:

 

SELECT * FROM `rank` as r  INNER JOIN `products`AS p USING ( `book_ISBN` ) ORDER BY r.`RankUK` ASC