Rating system sql code
 

whats a good sql example on how to make a good rating system?

Why do people avoid my topics.. this is sad..

Could you give us an example, perhaps? A little more detail would be lovely. What do you have at the moment? Have you made an attempt at it?

What type of rating system? A five star style? Ebay style?

We are also more compelled to help when the person asking the question shows that they cared enough to work on it. Please show us any work/thought you have given to it so far.

What is Ebay Style?

I've tried a thousand times, the mysql code doesn't like MAX / AVG, so that way I can find out which rating is used more out of 1 2 3 4 5

So.. you don't want to post what you've done so far? :-P

Since i don't really have something "so far". I made this example. I hope this is a good example. ;P


The problem is, if I have an average of for example 4 "5" ratings, while as theres 2 "1" ratings.. It usually returns the 1 rating. But you have more "5" ratings.

Just .. lol hehe xD
Take (4*5) + (2*1) + (if you have 3 "2" ratings= 3*2, etc.)/ (4+2+3) <- the total amount of ppl who have rated

Then you'll get an avarage of say 4,3 or w.e it is(didn't check). Then you just round up(which in this case will round down to 4).
Get it?

Hmm, well if formulas are required, why not just do baysien estimates? but yes i do get it, thank you. ;D

Yah :) I have no idea what the estimates was.
But the basic idea here is that you have a table "ratings".

id, productid(or w.e id you have on the thing that is being rated), 1, 2, 3, 4, 5

Then when someone rates 2, add one to the "2" column
And when getting the actual rating of the product(or w.e), you do the formula that I posted in my previous post :-)

For a rating system like that, use this code to get an ordered ranking list:
To get a particular user:
This assumes a databse of:
int userID (The ID of the person being rated)
float rating (The rating given to the person)

Note: The code was tested in MSSQL, not MySQL, but it should work.

@up- So.. how do you add rating to the user? If I rate someone as 3 of 5, then what will be inserted into db?

And also, how do you count how many ppl have rated?

1. How do you add rating to the user
Insert a row with the user ID being the ID of the user being rated and the rating as an integer ranging from 1-5. Individual database structures will vary, but that is the basic idea.

2. If I rate someone as 3 of 5, then what will be inserted into db?
A value of 3 and the ID of the user

3. And also, how do you count how many ppl have rated?
How many people have submitted a rating? You would have to add a column that contains the ID of the person who rated the person and write a query to count the distinct user IDs.

Hm, ye okey. Your idea is good, but say you have 1000 users, and everyone rates everyone, then you'd have 1000*1000 entries in your ratings db, which is.. quite alot. If you would do it my way, you would just have 1000 entries in the db, and all the ratings could be counted quite easy, while it's really simple to calculate the avarage ratings that someone has got

I tried this, and I got 23.

That cannot be correct.

4*5 = 20
2*1 = 2
3*2 = 6

20+2+6 = 28

4+2+3 = 9

28 / 9 = 2,9(around.. 27 / 9 = 3, so 28 / 9 should be slightly less than 3).

Final algorythm:
(4*5) + (2*1) + (3*2) / (4+2+3)

It would only be a million rows, which would not be terrible if you had that much traffic. But you would have to update ratings every so often to a smaller table and stuff like that. There would be so simple way to make a script for a site like that. I'm not going to get into large database stuff right now since I don't have a ton of experience with it.

While you could not keep records of who rated who (meaning one person could rate someone to whatever they wanted), you could have it keep adding the numbers (a 5 and a 2 would make the value 7) and divide it by the number of ratings given.

So you would have on your users table:
ID
rating
rater_count

Then keep adding rating by each number given and increment rater_count by one. Dividing the two would give you the average.

I just copied it onto a variable, and outputted it. :P gave me 23

Edit:
Okay, PHP can't do math well, I did it in the calculator, and I got 3.1

@village- Hm, yea.. well. There's probably TONS of ways of doing a rating system :-)

@orc- Heh, ohye, stupid me.. slightly MORE than 3 ofc, not slightly less >.<
But it's weird that PHP can't do that math :S