Jump to content


Photo

Ranking/Voting System question


  • Please log in to reply
3 replies to this topic

#1 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 13 September 2006 - 05:38 PM

Hello,

I'm working on voting system for a site of mine. I already have a member database in place where people can post comments and such. But I want them to be able to rank certain items (a la amazon, five stars or whatever). But I was wondering the most space-efficient and time-efficient way of doing this was? I want members ONLY to be able to vote. But not more than once on any particular item. So do I just make a new mysql table with fields like:
user_name, user_id, item_id, rank

and then every time I load that item, i have to query for the ranks with that id, and then average them?
Or is there a better way to do this? Any ideas or suggestions would be helpful. thanks!

#2 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 13 September 2006 - 06:01 PM

Sounds like a reasonable idea.  No need for a user_name column though  ;D

Then you could use the avg() function to get the result.

Regards
Rich
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#3 roopurt18

roopurt18
  • Staff Alumni
  • Advanced Member
  • 3,749 posts
  • LocationCalifornia, southern

Posted 13 September 2006 - 06:48 PM

Assuming that each row of your users table has an id field and that each row of your items table has an id field, I'd create a table:

ratings
id int auto_increment non null primary key
UserID int non null
ItemID int non null
Rating tinyint
UNIQUE(UserID, ItemID)

I'm not sure that tinyint would be appropriate, but if you're storing values 1 - 5 you certainly don't need a regular size int.

The UNIQUE constraint is important IMO because it will prevent your application from inserting duplicate rows.  Thus if you try to insert for a user and the query fails, you'll know to try and update instead, assuming you want people to edit their previous ratings.
PHP Forms : Part I | Part II

JavaScript: Singleton

http://www.rbredlau.com

#4 bpops

bpops
  • Members
  • PipPipPip
  • Advanced Member
  • 232 posts

Posted 13 September 2006 - 06:51 PM

Awesome, thanks for the quick responses, guys :)
I'll be able to get to work on this tonight!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users