homepage Welcome to WebmasterWorld Guest from
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

Mysql - Converting varchar field to tinyint

5+ Year Member

Msg#: 4312723 posted 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.



WebmasterWorld Senior Member rocknbil us a WebmasterWorld Top Contributor of All Time 10+ Year Member

Msg#: 4312723 posted 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.


5+ Year Member

Msg#: 4312723 posted 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 :)

Global Options:
 top home search open messages active posts  

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved