Mysql - Converting varchar field to tinyint - Databases forum at WebmasterWorld

Forum Moderators: open

Message Too Old, No Replies

Mysql - Converting varchar field to tinyint

jaruba

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

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

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 :)