[SOLVED] MYsQL order by problem

[JJBlaha]

I have a list of prices, all the numbered prices start with a  $ sign and some are words.  For example my list might look like

$25
$9
Free
$5.29
$42323
$0.79
Best Offer

I want to sort this list so that the lowest price is on top, then all of the words are at the bottom.  With the default mysql sort (order by price ASC) the list is sorted like this:

$0.79
$25
$42323
$5.29
$9
Best Offer
Free

i want it to be sorted like this:

$0.79
$5.29
$9
$25
$42323
Best Offer
Free

[JJBlaha]

Anyone know?

[Jessica]

Because they're strings, they're sorted as strings, which is correct. You could get all of them into an array, take out the $, and sort the numbers in PHP. Then add on the non-numeric ones at the end.

If you post some of your code, we can help you edit it, but you should try on your own first.

[shoz]

On top of what has been said, you should store the prices in a different column type. I'd recommend DECIMAL. You could then have two other tables. One holding the list of the different deals that can be offered (free, best offer  etc) and another that would hold any relations between specific products and a specific deal.

[code]
item_id, offer_id
1          2
2          1
---------------------
offer_id. offer
1          free
2          best offer
[/code]

This would allow you to remove the deal at any time and the code would automatically show the original marked up price for the item.

To sort them in MYSQL you could use the following however.
[code]
SELECT
*
FROM
tablename
ORDER BY
IF(SUBSTRING(price, 1, 1) = '$', 0, 1) ASC, SUBSTRING(price, 2) + 0 ASC, price ASC
[/code]

[JJBlaha]

Thanks alot for both replies, they were very helpful  :)