Problem with SQL syntax not doing what it's supposed to.

synergy

On my inventory page I've been referencing records like this:

"SELECT * FROM inventory WHERE Sold = '0' AND Type = 'Viper' ORDER BY Year DESC, Miles ASC"

Now in theory this should order each record of Vipers by year high-low first, and then by mileage low-high.. correct? Well this isn't working. For instance I currently have two 2003 Dodge Viper cars, one has 20 miles and the other has 125. They are extactly the same except for miles. The years are grouped together correctly, however, the one with 125 miles is displayed first over the car with 20 miles. Any ideas?

craigh

what type of field is miles? If it is not a INT field, then it is being compared in ASCII values and that is always unpredictable. :-)

synergy

ooo hadn't thought of that. I'll check it out real quick. brb

synergy

Both are VarChar fields, which would be ASCII.

Can I change this field type without affecting data? Also would the comma in the mileage affect the integer value?

craigh

I believe you can change the type and values that fit the new type will be converted. It is always good to back up though. The comma will affect the INT value, because it is a CHAR. It has to go. :-) you can always display it however you want though. I think sprintf will format stuff like that - f not that, then there is SOMETHING that will.

synergy

"I think sprintf will format stuff like that - f not that, then there is SOMETHING that will."

sprintf? Can you elaborate how take a variable and format it with a comma? Thanks.

craigh

RTM

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

synergy

Thanks!