Rating System PHP SQL

[megachip04]

I'm trying to set up a simple little rating system for videos on a website. If you push + it adds 1 and if you push - it subtracts 1. I've got it working just fine. However, the way i'm checking to see if you've rated it already is by searching for it inside a column called 'rated' that holds the usernames of each user that has rated it. I can see it becoming an issue if someone has a similar username as someone else. Is there a better way to go about this?

<?php

include("db_config.php");
include("approve.php");

$id=$_GET['id'];
$path=$_GET['path'];

$sql3="SELECT COUNT(*) as num FROM videos WHERE rated LIKE '%$_SESSION[username]%' and path1 = '$path'";
$result3=mysql_fetch_array(mysql_query($sql3));
$result3=$result3[num];

if($result3==0){

$sql1="SELECT * FROM videos WHERE path1 = '$path'";

$result1=mysql_query($sql1);
while($rows=mysql_fetch_array($result1)){
	$rating=$rows['rating'];
	$rated=$rows['rated'];
	$rated=$rated.'.'.$_SESSION['username'];
}

if($id=="minus") {$rating = $rating - 1;}
if($id=="plus") {$rating = $rating + 1;}
	
$sql2 = "UPDATE videos SET rating='$rating', rated='$rated'
 WHERE path1 = '$path'";
$result2 = mysql_query($sql2);

if($result2) {
header("location:view_video.php?path=$path");	
}
}
else {
	header("location:view_video.php?path=$path&id=1");
}
?>

[urtrivedi]

You will always find that username or userid or loginid are always unique for that particular website.
If username of your site is not unique (I am sure it is unique) then then you should find another unique column to rate it distinctly.

do not use

rated  LIKE '%$_SESSION[username]%'

instead use

rated  = '$_SESSION[username]'

[IIM]

you must have a primary key in the table to uniquely identify the user.
Add a field in your table containing user data ,userid and mark it primary key....
urtrivedi has explained it nicely...how to use while rating ....

[megachip04]

You will always find that username or userid or loginid are always unique for that particular website.
If username of your site is not unique (I am sure it is unique) then then you should find another unique column to rate it distinctly.

do not use

rated  LIKE '%$_SESSION[username]%'

instead use

rated  = '$_SESSION[username]'

Ok, that makes sense. But what if one user has a username "user_name" and one person has username "user_name45"

if user_name45 rates first, i think that when user_name tries to rate, it will tell them they already have. Will that be a problem?

[urtrivedi]

as I said you can not use LIKE keyword, as you know that once "user_name" votes, then all having starting with "user_name23", "user_name64", "user_name4tg", will not able to vote.

so best way is use = sign

rated  = '$_SESSION[username]'

LIKE is used only when you are searching something that you are not much sure.

[karthik_ppts]

better storing the id of the user instead of name while rating or use the = operator instead of LIKE as Urtrivedi said.

[diafol]

How are you actually storing this data? It looks as if it's all in 'videos' table. That doesn't seem able to cope with ratings and users. I would think this:

ratings table:

user_id [FK]
video_id [FK]
rating [1 or -1]

[Stefano Mtangoo]

scrolling down you will find exact tutorial for rating system
http://phpacademy.org/videos/index.php?all

[megachip04]

that tutorial page is awesome. i can't believe i haven't come across it on google.

I have the rating system working fine. It's a simple +1 or -1 and it compounds (ex. If 3 users vote -1, then it's rating -3). The issue was how to make it where each user can only rate each video 1 time. I think i figured it out. I got a separate table going called "ratings". It stores the username, the video's unique id, and the rating (+ or -). I just have to check if it's in there already. And they can be easily removed when the video is removed.

Thanks for all the help

[Stefano Mtangoo]

Mark it solved then :)