Simple thumb up / down vote system

[HaLo2FrEeEk]

I'm working on a system where users will submit comments about a topic, and that comment can be voted on with a thumb up or down. I've got a table for comments, each comment will have it's own unique ID, and I've got a table to track votes as well. I'm just wondering what would be the best way to accomplish this. I'm thinking using a boolean as the actual vote, so that a 1 is a thumb up and a 0 is a thumb down. Is there a better way though?

Also, when I'm displaying the votes on the page, should I run two separate queries to count the rows where the votes equal 0 and then 1, or is there a better way to do it so that I only have to do one query?

Edit: Just thought of this. Would a better way be to get rid of the votes table and add two new columns to the comments table: votes_up and votes_down, that way I can get the comment itself, plus the vote status all in one query. With this method, what would be the best way to order the comments, with higher rated comments coming first (the rating being the ratio of votes up to votes down.) Is there a way to do THAT wth MySQL of would it have to be done with PHP?

[dgreenhouse]

I can definitely see the efficiency of adding the votes columns to the comments table.

If you keep the votes table separate, this query should work:

select 
count(case when vote = 1 then 1 else null end) as upvotes,
count(case when vote = 0 then 1 else null end) as dnvotes
from votes
 

[HaLo2FrEeEk]

I actually worded this a little wrong to avoid the obligatory long explanation of what I was trying to do. There are challenges, which people will submit tips for. Others can comment on the tips, or vote on them, but they don't have to do either one together, you can vote without commenting and vice versa. I did get a great query that would get the votes and tips in a single query though, it uses the same CASE WHEN code to count the up and down votes, then subtracts the up fro the down votes to get a rating and sorts it by that, highest first. The comments still have to be gotten separately though, but I figured it all out. Thanks.