simple star rating system

[kL]

hey i have a section on my site (outside of my smf forum) and I would like to have a star rating system for something. How would I go about doing this? The main things Im not sure of how I'd do are how I would store the votes in the database, (would I knew a separate field for the number of 1 star votes, the number of 2 star votes, etc.? isnt there an simpler way?) and then the other part is how I would check if a member has already voted (i can use $context['user']['name'] because Im using ssi), but not sure how id store the information.

[DunkinDonuts]

Can you reverborate please?

[kL]

I thought it was clear 
I would like to make a simple star-rating system. a rating system where uses can choose to vote something from 1-5 stars. then the end result will show the average rating. one 1 star rating and one 5 star rating would show an average rating of 3, etc.
Im just not sure what the best way to store the votes in a database would be. would i need a separate field in the db row for the number of one star votes, the number of two star votes, etc. or is there a simpler way.
Another thing Im not sure of is how I would check to make sure a user has not already voted? I will have the username in the $context array from ssi.php so it shouldnt be too hard... /

[CDarklock]

You're planning this the wrong way. Don't put your ratings on the object records. You want to build a separate rating table called, say, "star_rating" with three columns:

- User: the user ID making this rating.
- Object: what they're rating.
- Rating: what they rated it.

All of these should be numeric. Your rating may be an integer or a decimal, it's up to you.

Set your primary key for this table to BOTH Object and User. Also create an index on just the Object column.

Two queries get your result. First of all, what is object X rated?

    select sum(rating)/count(*) as rated from star_rating where object='X'

MySQL will return NULL if you divide by zero, which means the object has not been rated.

Second, has object X been rated by user Y?

    select count(*) from star_rating where object='X' and user='Y'

This returns 1 if true and 0 if false, which is nice and convenient. Since object/user is your primary key, it can't be anything else.

When user Y assigns a rating of Z to object X, use "replace" instead of "insert" to record it:

    replace into star_rating (user,object,rating) values ('Y','X','Z')

This allows people to change their ratings whenever they like, and the math stays correct.

Bonus: what's the average rating user Y gives objects?

    select sum(rating)/count(*) as rated from star_rating where user='Y'

If you intend to use that much, make another index on just the User column.

Fun with statistics: what are the ten highest rated products and what are their ratings?

    select object, sum(rating)/count(*) as rated from star_rating group by object order by rated desc limit 10

There's a lot of benefit in separating the ratings into their own table.

[kL]

Thank you!! I can't believe I was trying to do it so complicated!

[kL]

you can see the finished product here - http://www.kanyetalk.com/index.php?act=links and here - http://www.kanyetalk.com/index.php?act=albums;a=1