Avoiding Rating Twice (like YouTube)

[chaseman]

I notice that the YouTube like/dislike buttons allow only ONE TIME rating even if you browse that website with private browsing on, meaning with no cookies.

 

I would like to implement the same functionality in my own script:

 

- SESSION variables is not an option to me, because that would mean the user is not allowed to vote again on the whole website for as long as the session lasts, PLUS I just don't want to allow the user to vote twice on that specific ROW, he still should be able to vote on other ROWS.  Similar to YouTube where you can vote on other videos but not on one video twice.

 

- using the MySQL database is not an option either, that would mean the protection to vote twice is forever AND for the whole website (again) until the entry gets deleted.

 

- I'm not sure about how good cookies of an option are, I haven't made use of them till yet, I just know they can be deleted/manipulated, and what if the user has them disabled?

 

 

I'm wondering what type of system is YouTube using? Because it works without cookies as well.

 

Is it JavaScript? If yes, in which type of specific area of JavaScript would I have to look at - I'm asking since I don't know anything about JavaScript.

[PaulRyan]

Hello Chaseman.

 

The voting system Youtube uses actually is database orientated.

 

How it works is as follows...

- You vote on a video

- Youtube takes vote and saves a new record with your User ID, vote type (up or down), time of vote etc, they then add 1 vote to the video by updating its record set by 1 up or down.

- If you vote again, they check your User ID and vote against the voting database to see if you have voted or not

- If you've voted they dis-allow the vote and don't save it, unless you're vote is changed from up to down or vice versa

 

This means yuo can vote as many times as you like and you're vote will be saved so you can never have more than 1 vote on a video.

 

Regards, PaulRyan.

[chaseman]

That would mean that you have to create a whole new VOTING table with the following columns.

 

video_id, INT auto_increment

user_id, INT foreign key

vote, INT

time of vote, now()

 

Several questions rise up:

 

1. Isn't that a huge strain on the servers, if constant connections are being made just for a vote?

2. Is the time of vote (like you said) necessary?

3. When and how is the data being deleted? Because as far as I know, you can vote again on one video on YouTube just after a peroid of time.

 

Right now I simply have a like and a dislike column in the table where the contributions are being inserted.

[PaulRyan]

Yeah some thing like that would work...

 

The strains you speak of:

1 - Websites like Youtube can handle these kind of strains, they have the resources and money.

2 - Yes if you wish to allow more than 1 vote ot the same video or whatever they are voting on, you can check current tiem with DB to check if they can vote.

3 - It may not be deleted, just saved so you can vote again every 30-60 minutes or whatever...

 

Another thing when you change you vote, they will probably not delete the vote, just change the vote from up to down so they aren't deleting then adding..or flag the vote for deletion then use a cron to delete old votes or whatever

 

Regards, PaulRyan.

[chaseman]

Another thing when you change you vote, they will probably not delete the vote, just change the vote from up to down so they aren't deleting then adding..or flag the vote for deletion then use a cron to delete old votes or whatever

 

Yeah I've noticed that as well, and I thought they're using JavaScript for that, so they're simply re-updating the database all the time? lol, that sounds like not resource saving at all.

 

Another question that rises up is, how does the LIMITING to ONE vote happen?

 

Are they simply telling the database don't insert another vote if VIDEO_ID + USER_ID already exist?

 

That would mean you'd have to SELECT * FROM table WHERE user_id = $user_id ... first and then check if the combination video_id and user_id already exists. And THEN update if it doesn't.

 

 

BUT how are you bringing the user_id into combination with the video_id, remember you'd have to do that for ALL users?

 

 

I'm confused on this one.

 

 

One solution comes up to my mind. That's JavaScript, you vote and the JavaScript is simply DISABLING the buttons, so you can't vote again, with that particular button. But all other buttons are still click-able, that sounds way more efficient IMO. On the other hand the user could simply disable JavaScript.

 

 

[chaseman]

ADDENDUM: I think to do it with MySQL you'd have to do it like this:

 

video_id, INT foreign_key (from video table)

user_id, INT foreign key (from user table)

 

Voting table:

video_iduser_id

23

31

53

31

64

31

25

31

13

31

 

 

The likes or dislikes can be updated to the rows in the video contribution table.

 

That way you'll have to have two queries.

 

One query to check if the user has already voted for that particular video. And another query to update the likes/dislikes of that particular video in the video table.

 

 

You could periodically delete the entries of the voting table to save resources.

 

 

Now the solution sounds much better.

[PaulRyan]

Yeah that looks quite good actually, but you'd have to check when they last voted or whatever...how would you do that?

 

Regards, PaulRyan.

[chaseman]

Yeah that looks quite good actually, but you'd have to check when they last voted or whatever...how would you do that?

 

Regards, PaulRyan.

 

That's simple you'd do it like this:

 

$query = "SELECT * FROM votes WHERE user_id = $user_id AND video_id = $video_id";
$query = mysqli_query ($dbc, $query);
$num_rows = mysqli_num_rows($query);

if ($num_rows == 0) {

// add vote script here

} else {

// do nothing

}

 

 

Not sure how it would work when you click dislike AFTER you clicked likes, just like how it works on YouTube. I think you could take the num_rows and do an UPDATE when it's 1, BUT the opposite button has been clicked. Then you'd have to alter the table like this:

 

video_id user_id vote

23 31    +1

53 31    +1

64 31    -1

25 31    -1

13 31    +1

 

 

Plus one for thumbs up and minus one for thumbs down. So

 

if $num_rows == 1 && opposite vote, then do UPDATE.....

 

if $num_rows == 1 && same vote, then do nothing.

 

that may work.

 

 

 

[chaseman]

Ok I finally wrote the script just like you suggested, thanks by the way for helping me come to this great solution!

 

Here's the script:

 

// RATING SYSTEM

if ($array[0] || $array[1]) {

$query = "SELECT * FROM votes WHERE con_id = $array[2] AND user_id = $user_id";
$query_run = mysqli_query ($dbc, $query) or die (mysqli_error($dbc)); 
$num_rows = mysqli_num_rows ($query_run);
$assoc = mysqli_fetch_assoc ($query_run);
$rating = $assoc['rating'];


if ($num_rows == 0) {

	if ($array[0]) {

	$query = "INSERT INTO votes (con_id, user_id, rating) VALUES ($array[2], $user_id, $array[0])";
	$query = mysqli_query ($dbc, $query) or die (mysqli_error ($dbc));

	$query = "UPDATE con SET likes = likes+1 WHERE con_id = $array[2]";
	$query = mysqli_query ($dbc, $query) or die (mysqli_error ($dbc)); 

	mysqli_close($dbc);


	} elseif ($array[1]) {

	$query = "INSERT INTO votes (con_id, user_id, rating) VALUES ($array[2], $user_id, $array[1])";
	$query = mysqli_query ($dbc, $query) or die (mysqli_error ($dbc));

	$query = "UPDATE con SET dislikes = dislikes+1 WHERE con_id = $array[2]";
	$query_run = mysqli_query ($dbc, $query) or die (mysqli_error ($dbc)); 

	mysqli_close($dbc);

	}

} elseif ($num_rows == 1) {

	if ($array[0] && $array[0] != $rating) {

    $query = "UPDATE con SET likes = likes+1, dislikes = dislikes+1 WHERE con_id = $array[2]";
	$query_run = mysqli_query ($dbc, $query) or die (mysqli_error ($dbc));
	$query = "UPDATE votes SET rating = +1 WHERE con_id = $array[2]";
	$query_run = mysqli_query ($dbc, $query) or die (mysqli_error ($dbc));

	mysqli_close($dbc);

	} elseif ($array[0] && $array[0] == $rating) {

		echo "You have already liked the table: " . $con_id;

	} elseif ($array[1] && $array[1] != $rating) {

	$query = "UPDATE con SET likes = likes-1, dislikes = dislikes-1 WHERE con_id = $array[2]";
	$query_run = mysqli_query ($dbc, $query) or die (mysqli_error ($dbc));
	$query = "UPDATE votes SET rating = -1 WHERE con_id = $array[2]";
	$query_run = mysqli_query ($dbc, $query) or die (mysqli_error ($dbc));

	mysqli_close($dbc);

	} elseif ($array[1] && $array[1] == $rating) {

		echo "You have already disliked for the table: " . $con_id;

		}
	}
}

 

 

I notice how my scripts become more and more professional, and I'm only three month into programming

 

 

This script works just like the rating system on YouTube, it will also UPDATE when you choose the opposite like/dislike.

 

The arrays are taken from the function that I use to display the table.

 

The one thing that it's not doing like YouTube is the instant change of the numbers, I'm guessing they have done that with JavaScript. As of now you'll only see the changes after page refresh, since I have no clue about JavaScript I will not be able to implement that for now.

 

If you have questions let me know.

 

Things to note:

 

$array

0 = likes

1 = dislikes

2 = contribution id (in our example in this thread it would be the video id)

 

Also notice that I have this calculation in the query:

 

likes = likes-1, dislikes = dislikes-1

 

Two times minus, because e.g. -2 - -1 = -3

 

If you have dislikes+1 it will take away on both sides, but you want to take away on one side and add on the other.