| Mysql - Converting varchar field to tinyint
|
jaruba
msg:4312725 | 5:37 pm on May 15, 2011 (gmt 0) |
When I first created the database, I made a varchar(4) type field to store ratings in, the ratings can be in a radius of 0.0 to 10.0 (other examples: 8.45, 9.12, 5.20) (this field is called "rating")
The problem is that now I want to create a top, and need to ORDER BY rating DESC, the issue here is that the rows that have 10.0 are obviously not ordered first.
For a solution, at the moment I use 2 queries to get the necessary results. Now I imagine that converting this field from varchar(4) to tinyint would solve my problem and I should get the results with only 1 query, but I would prefer knowing if this can be done and that it wouldn't create any website bugs.
Thank you in advance for any help on my issue.
|
rocknbil
msg:4313141 | 4:01 pm on May 16, 2011 (gmt 0) |
You probably want decimal so you don't lose your fractionals. Decimal is a lot easier to manage than float.
alter table yourtable change rating rating decimal(12,2) not null default '0.00';
That should order them properly, unless I'm missing something.
|
jaruba
msg:4321544 | 1:41 pm on Jun 3, 2011 (gmt 0) |
Sorry for replying so late, but I didn't have time to make the necessary changes to the database until now.
I changed the field to decimal(12,2) as you suggested and it worked out fine, I only had to make some minor changes to the code for the rating to be viewed as it was before.
Thanks for all your help :)
|
|
|
