how to save an users vote on a comment to mysql via php?

Question

How to save an users vote on a particular comment? For example if someone likes a comment posted on the site he/she will click the (+) button or other way will click the (-) button. How could i save this information to mysql, so that when the user come back to the site I can show his/her vote on the comment? Something like facebook comment like system. Should i create a new mysql table to record them all? Assuming the user is already registered user.

my current comment table

id | date | userid | page | comment |

I expect something like

$username - $date
$comment <no.of likes> likes, <no.of hates> hates.

Answer 1

You're describing a many-to-many relationship. You will need an intermediate table between users and comments, since any user can vote on any comment, and any comment can have votes from any users. It might be something like this:

user_id | comment_id | direction

If you don't need to store any data about who voted on what, then you can just add a score column to your comments table, that gets either increased or decreased whenever a user votes. You can also do both, if you want to quickly get the score of the comments as they're being displayed, but retain the information of who voted on what for later.

edit:

As an after thought, you might want to avoid the score column. You might run into a situaion where a single user can vote on a comment more than once, if you don't keep track of which comments that user has already voted on.