[SOLVED] Data Field with Currency Not Sorting Accurately

[phpQuestioner]

I have a datatable with a varchar field know as "price". At this time I have 3 rows in it; they are as follows: $ 18,500 $19,500 and $ 9,500. The problem is that when I try to sort in either ascending or descending order; it does not sort right. I know the problem lies in the $ 9,500, because I added a zero in front of the 9,500 (like this: $ 09,500) and it sorted fine. At first I thought it may have had something to do with the dollar sign, but it didn't because I removed and tested it without dollar sign and still had the same problem. What I want to know is, how I can make datatable sort accurately with putting a zero in front of the 9,500. I tried to use a CSS style to cover up the zero, but when I did that, datatable still would not sort the right way. MySQL code in my web page is accurate, because you can take the $ 9,500 and add a 5 digit number and it works fine.

 

Anyone know what I need to do to fix this?

[Psycho]

Well, the problem is that you are using a text field for a number field. Change the data type to a numeric type entity and then format into a currency format when displayed on the page.

[kenrbnsn]

Are the numbers stored in numeric fields or character fields? They should be stored in numeric fields (unformatted) and you should only format them when printing.

 

Ken

[phpQuestioner]

what do you mean formatted or unformatted; like with dollar signs? Because I took the dollar signs out and I still had the same problem.

 

What should I set my field to be, since it should not be varchar?

[phpQuestioner]

If I would not need to go above $ 100,000 dollar; I could use "MEDIUMINT" - right? This would also fix my sorting issue - right?

[MadTechie]

Sorting fields is based on the field type

so if the field type is a int type (ie medium) the sort should be fine, if your field type is a string ie char then sorting number will look kinda messy ie

1

10

11

12

13

..

..

18

19

2

20

21

[phpQuestioner]

I figured out what to do. I set my field to "int" and did this:

 

<?php
$price2=number_format($price);
echo "$ ";
echo "$price2";
?>

 

This spit out the format and now it sorts accurately.

 

Thanks Guys For All Your Help !!!

[MadTechie]

Remember Click Solved!

 

Bottom Left of this page