MySQL Decimal Field Predicament

[bdichiara]

I have a decimal field set to 50,2 in my table. The problem is this field can hold numbers without decimals, for example:

20.20
30.59
5
14
35.70

In my PHP code, i check to see if it has a decimal, and that's how I know that it's money, so I add number_format() to the value. My problem is when I'm sorting the field, it puts 5 before 14. I'm guessing because the 5 is greater than the 1.

Whats the solution to this situation?

_______________
_brian.

 

[sleipnir214]

I don't understand. Are you ordering this data in PHP rather than MySQL?




Want the best answers? Ask the best questions! TANSTAAFL!

 

[bdichiara]

No, I'm ordering it by the decimal field in the MySQL query.

here's my query:

SELECT * FROM poe LEFT JOIN employees ON poe.employee = employees.employeeid LEFT JOIN poe_cats ON poe.category = poe_cats.categoryid ORDER BY poe.category ASC, poe.amount DESC

here is a section of my results:

Most Checking Accounts Opened
1   Some Employee Name           4
2   Some Other Employee Name     25
3   Another Name                 23
4   Name Again                   15
4   Stuff                        15
5   Boom                         14

_______________
_brian.

 

[sleipnir214]

I'm sorry, but I'm still confused. Where is the ordering error?



Want the best answers? Ask the best questions! TANSTAAFL!

 

[bdichiara]

4 is at the top of the list, where it should be at the bottom.

_______________
_brian.

 

[jpadie]

if you remove the category ordering does it then work?

 

[sleipnir214]

Yeah, you've told it to order first by the other columname.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[bdichiara]

CRAP! You know what... I just checked and my table field is a varchar.....

I'm sorry to have wasted your time with that. I guess now my question is, can I change this field to a Decimal field (50,2) and still have values that do not use .00 at the end?

_______________
_brian.

 

[sleipnir214]

Yes.



Want the best answers? Ask the best questions! TANSTAAFL!

 

[bdichiara]

Actually, now that i figured out my problem, i should probably post over in the MySQL forum. Thanks sleipnir for the help.

_______________
_brian.