zq29 Posted March 20, 2006 Share Posted March 20, 2006 I have a field set as a varchar datatype, but on occasion I would need to treat it as if it were a float field in a php script, sometimes it may have a price in there that I wish to order from ascending to descending, and ofcourse, it doesn't work as expected when its a varchar field.I have tried something like this, with no luck, can this even be done?[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] `price`[color=green]AS[/color] FLOAT() [color=green]FROM[/color] [color=orange]`table`[/color] [color=green]ORDER BY[/color] `price` [color=green]ASC[/color] [!--sql2--][/div][!--sql3--] Quote Link to comment https://forums.phpfreaks.com/topic/5335-select-a-field-as-a-different-datatype/ Share on other sites More sharing options...
wickning1 Posted March 20, 2006 Share Posted March 20, 2006 [code]SELECT CAST(`price`AS DECIMAL) AS myprice FROM table ORDER BY myprice ASC[/code]This will NOT use any index you may have on `price`, so it will have to sort it every time you make the query. The best way to handle this is to change the column in mysql to a DECIMAL or FLOAT type. Quote Link to comment https://forums.phpfreaks.com/topic/5335-select-a-field-as-a-different-datatype/#findComment-19085 Share on other sites More sharing options...
zq29 Posted March 20, 2006 Author Share Posted March 20, 2006 Ok, I'll look into that. Thank you for your advice. Quote Link to comment https://forums.phpfreaks.com/topic/5335-select-a-field-as-a-different-datatype/#findComment-19148 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.