Jump to content


Photo

Select a field as a different datatype?


  • Please log in to reply
2 replies to this topic

#1 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 20 March 2006 - 09:36 AM

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`AS FLOAT() FROM `table` ORDER BY `price` ASC [!--sql2--][/div][!--sql3--]

#2 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 20 March 2006 - 06:39 PM

SELECT CAST(`price`AS DECIMAL) AS myprice FROM table ORDER BY myprice ASC

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.

#3 Kris

Kris
  • Staff Alumni
  • Advanced Member
  • 2,755 posts
  • LocationThe Internet

Posted 20 March 2006 - 09:15 PM

Ok, I'll look into that. Thank you for your advice.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users