Order By DESC/ASC not working within PHP Query

I have this code so far开发者_Go百科 which is within 2 while loops:

mysql_query("SELECT * FROM listing WHERE 
(category_id='$category' OR category_id_2='$category' OR category_id_3='$category') 
AND listing_status='1' AND listing_type='1' AND listing_id='$listing_id' 
ORDER BY overall DESC");

The data is showing exactly what I want, however the ORDER BY simply isn't working. I'm not too sure what it's ordering by. The overall column itself is DECIMAL(12,2).

The values are saved to only 2 decimal places. For instance, in each row it could be 2.56, 2.89. In this case I want the 2.89 to show before the 2.56. However, it's not.

Many thanks in advance.

---

I believe you are only selecting one element at a time in your query, something like

while(...){
    $category = ...;
    $listing_id = ...;
    // Your query which only returns one result here
}

Then since your query only returns one result it has nothing to sort, and you see the results in the order the queries are executed.

You need to rewrite your query to select all the rows you want in one go instead of having it in a loop if you want ORDER BY to work. Using IN in your query may help you.

---

Have you tried casting the field as a decimal in the order by?

ORDER BY CAST(overall as DECIMAL) DESC

---

I have managed to solve the problem.

By implementing the 'overall' column in the first loops table, instead of the second. It orders the data first by overall, and then goes ahead and gathers the other data from the second table.

Many thanks for your help.

---

Try:

mysql_query("SELECT * FROM listing WHERE 
    (category_id='$category' OR category_id_2='$category' OR category_id_3='$category') 
    AND listing_status='1' AND listing_type='1' AND listing_id='$listing_id' 
    ORDER BY overall+0 DESC");

---

The query is fine. The problem is probably with how you iterate returned data. Try changing it. If not, provide us with the whole relevant piece of your PHP code.

---

I don't know why, but I found out by copying and pasting from phpmyadmin that this worked to solve a similar problem. The ' is changed to ` - dunno if it's important. But definitely ASC worked with the second way.

Important - the have been stripped from the second method, put them around the table name and the column names.

instead of

$sql = "SELECT * FROM 'dczcats' ORDER BY 'first' , 'second' ASC";

I typed this

$sql = "SELECT * FROM `dczcats` ORDER BY `dczcats`.`first` , `dczcats`.`second` ASC";