PHP/MySQL Sort Question

[clamum]

I'm doing two SELECT statements (combined by a UNION operation) to get a bunch of records from a MySQL database.

The thing I'm interested in sorting is a price field. It's defined as a VARCHAR because users may enter things like OBO or FIRM after the price.

However, the sort I'm doing does not work. I want it to display highest price to lowest price. The code is below.

I'm wondering if sorting the values in PHP instead of in the MySQL query would work? If so, how would I do this after doing the $result = mysql_query($sql) statement?

Thanks for any help.

 

[imported_LoKe]

ORDER BY price DESC LIMIT 5000

 

[clamum]

Oops, sorry. I did have the DESC keyword in the select statements. I'll update the OP to reflect this.

Here's an example of the sort:
$700 OBO
$700 OBO
$700 OBO
$700
$7,000 OBO
$600 OBO
$600 OBO
$5,995
$5,800 OBO
$5,000
$2,500 OBO
$17,500 OBO
$16,000
$13,500

 

[QED]

It's working as its supposed to-- sorting on a character field sorts alphabetically-- meaning 1,000,000 will come before 2.

If I were you, I would seperate the dollar amount from the field, convert to a decimal, and then sort on that instead of trying to sort on a character field.

In fact, the best overall option is not have a character field for dollar amount-- just have a decimal field for asking price, and a seperate logical and/or character field to indicate whether that asking price is negotiable or not.

 

[clamum]

Originally posted by: QED
It's working as its supposed to-- sorting on a character field sorts alphabetically-- meaning 1,000,000 will come before 2.

If I were you, I would seperate the dollar amount from the field, convert to a decimal, and then sort on that instead of trying to sort on a character field.

In fact, the best overall option is not have a character field for dollar amount-- just have a decimal field for asking price, and a seperate logical and/or character field to indicate whether that asking price is negotiable or not.

Yeah it's not a very good design. I thought that it would sort like I wanted it to if it was a character field, but I'm a noob when it comes to this.

I'll have to re-do the design but it shouldn't be too bad. Thanks for your help.

 

[Zugzwang152]

In PHP, you could use sort with the SORT_NUMERIC flag.

You'll have to load the results into an array first though, by using the method you're most comfotable with.

This is probably less efficient than doing it in your query, but you didn't really specify whether performance is an issue.

 

[BoberFett]

Not too familiar with MySQL, but can you cast the field before sorting on it? Something along the lines of:

... ORDER BY cast(price as INT) DESC LIMIT 5000

 

[Snapster]

Originally posted by: BoberFett
Not too familiar with MySQL, but can you cast the field before sorting on it? Something along the lines of:

... ORDER BY cast(price as INT) DESC LIMIT 5000

You can, but only if that field had numeric value in which case you might has well had the proper data type in the first place for the field. In this case it's mixed type and the only option left to him is either have a price field with dollars on it to sort numerically, or trim the field at the first space and then cast it (which is huge overhead).

 

[BoberFett]

I was just curious. It's been quite a while since I've done much with SQL at all, but I seem to recall SQL Server having the ability to cast text as numeric and have it filter out the non-numeric characters, and wasn't sure if MySQL could do something similar.

Yes, there's extra overhead involved, but it's probably faster to have optimized, compiled code do it on the server rather than sorting it in a scripting language.