By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,664 Members | 1,623 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,664 IT Pros & Developers. It's quick & easy.

MySQL Sorting Question

P: 28
Hello everyone ! ,
I am working on a rating field in my database. Basically , my rating formula goes like this
Expand|Select|Wrap|Line Numbers
  1. RATING = TOTAL POINTS / TOTAL RATINGS
Ofcourse , its the average rating. I store the total points and total ratings in a single field called 'catrating' (like 4/2 i.e points=4 ratings=2) and explode it with php to get required values. What am looking for is , is there an easy MySQL inbuilt function to sort the ratings ( Like sorting numbers) like these directly in the query with some REGEX and AVG , so that all the top rated stuff gets sorted in the DESC order ?

Thanks so much !
Oct 20 '08 #1
Share this Question
Share on Google+
4 Replies


nathj
Expert 100+
P: 938
Hi,

Why not store the two values in separate fields?

This would then enable you to more accurately use the MySQL 'ORDER BY' clause.

The clause is simple enough ORDER BY tbl.field ASC/DESC. you can even tack orders together thus ordering on multiple columns.

I would have though this was the easiest way to go.

I admit this may involve a change in your existing code but it's going to make this job easier and give you greater flexibility in the future.

If you have a good, technical reason for not doing this then post it here as I can think of one - other than potential headache of updating existing code, and that may not be an issue for you depending on how much code there is.

Cheers
nathj
Oct 20 '08 #2

Atli
Expert 5K+
P: 5,058
I would agree with nathj.

Storing two values in a single field should never be done. It goes against one of the most basic *rules* of relational database design. It does nothing but cause problems (as you are experiencing).

The best way to do this would be to separate the two values and have MySQL sort the values using ORDER BY.
Oct 20 '08 #3

P: 28
ok ty guys , i'll make 2 fields
Oct 21 '08 #4

nathj
Expert 100+
P: 938
ok ty guys , i'll make 2 fields
Smart decision.

If you get stuck or need any further help then just give us a shout.

Cheers
nathj
Oct 21 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.