effective retrieve for a voting system in PHP and MySQL

I have a system where registered users can vote up/vote down comments for a picture. It's very similar to Stack Overflow's voting system.

I store the votes in a table with values as such:

vote_id | vote_comment_id | vote_user_id | vote_date | vote_type 

Now I have a few questions concerning the speed and efficiency for the following:

PROBLEM: Once a user opens the picture page with comments, I need if that user has already voted UP/DOWN on a comment to show it like; "you voted up" or "you voted down" next to the comment (in Stack Overflow the vote image is highlighted).

MY POSSIBLE SOLUTION: Right n开发者_开发百科ow when I open a picture page I loop through each comment, and I loop through my table of votes as well and check if a user has voted and show the status (I compare the vote_user_id with the user's session).

How efficient is this? Does anyone have a better approach to tackle this kind of problem?

You are looping through the table of votes? Are you reading the entire database into memory and then looping through it?

Have you tried querying the database only for the relevant data?

SELECT vote_comment_id, vote_type
FROM vote
WHERE vote_user_id = 34513
  AND vote_comment_id IN (3443145, 3443256, 3443983)

you don't mention which database you're using but i assume some SQL variant.

so, instead of looping through the entire table of votes, you can do something like

select vote_type from vote_table where vote_comment_id = $commentId and vote_user_id = $userId

or even better, when you're retrieving the actual comments you can do a left join like so

select c.*, v.vote_type from comments c left join (select * from votes where vote_user_id = $userId) v on v.vote_comment_id = c.comment_id

then check if vote_type is null, up, or down in your display loop. this might be less efficient if you have 1000 comments and only show 10 at a time though, in which case the first method should help.

[edited after comment above about vote_type column]

Try to avoid using subselect specially if you are retrieving large number of rows.

select c.*, v.vote_type 
from comments c 
left join vote v 
on v.vote_comment_id = c.comment_id
and v.vote_user_id = $userId

Using CASE statement to display/hide vote_type.

select c.*, CASE v.vote_user_id WHEN $userId
THEN v.vote_type /*compare vote_user_id with the user's session*/
ELSE null END AS 'votetype' /*hide vote_type */
from comments c 
left join vote v 
on v.vote_comment_id = c.comment_id

Wouldn't you need a column for whatever it is the user voted on, ie post_id?

You could do a select query, see if a row for the current post and user exists - if a row is returned, they've voted.

Actually, I just noticed that vote_comment_id isn't what I read it as (vote_comment).

You just need to check if a row exists

I've a site with a similar logic. I do not track individual votes (for this), I only have a posts (images) table, with a vote count and a text field with userid:vote;userid:vote... pairs, where vote is +/-. This way I do not need to select from the huge votes table and I need to load the row belonging to the post anyway. A simple string search for "userid:" will reveal whether the current user voted or not.

ACID transactions are required to keep vote count and the votes text field consistent.

My solution is to fetch all user's votes when he's logging in into a session. Fetch all comments' ids into two arrays:

$_SESSION['votes'] = array(
    'up'   => array(12, 854, 87, 78),
    'down' => array(84, 32, 77)
);

and when user access some page check if its id exists in any of that arrays.