Sorting numbers within a text field 1

[barkadog]

Hi,

On this page, I am sorting each listing by price ascending, but since the field is a text field, the commas are throwing it off. It sorts fine as long as the numbers are less than 1,000,0000. I think it is just sorting until it hits the comma, then it stops.

http://www.roatan-realestate.com/roatan-homes.php

I am using this to do the select and sort:

$temp1 = mysql_query("SELECT a.listing_id, a.field_name, a.field_value, b.field_name, b.field_value FROM listingsDBElements a, listingsDBElements b where a.field_name = 'price' and a.listing_id = b.listing_id and b.field_name = 'property_type' AND b.field_value='$expprop' order by a.field_value +0 ASC", $connection);

Is there something I can add to make it sort properly? If "Inquire" is put as the price, it sorts that at the top, which is what I want, and if a + or something else is added after the number (like for a building lot, 15,000+) it sorts that number correctly, which is also needed.

The dollar sign is not added by the client and is not part of the field.

Thanks!

 

[sleipnir214]

You are right -- MySQL is interpreting the column values as a string until the comma, which is not a valid numeric character.

I strongly recommend that you not store the commas in the field. PHP has a function number_format() (

http://www.php.net/manual/en/function.number-format.php),

which can display a number with whatever thousands separator you choose.

If you are stuck with using numbers as they are, try this:

order by (replace(a.field_value, ',','')+ 0) asc ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[barkadog]

Thanks sleipnir214! It worked like a charm!