CODEWITHSUNDEEP
phpmysql
Adnan
Adnan

Reputation: 26350

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 now 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?

Upvotes: 10

Views: 797

Answers (6)

Bronson
Bronson

Reputation: 135

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

Upvotes: 1

sibidiba
sibidiba

Reputation: 6360

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.

Upvotes: 0

chris
chris

Reputation: 9993

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]

Upvotes: 2

Mark Byers
Mark Byers

Reputation: 838326

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)

Upvotes: 2

hsz
hsz

Reputation: 152226

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.

Upvotes: -1

Josh
Josh

Reputation: 1281

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

Upvotes: 0

Related Questions