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

Databases Forum

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

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