-2

On my site when a user is logged in, they can give a 1-5 rating on a movie.

What I want to do is make it so that database knows which user gave the rating and to update there rating if they click it again rather than creating a new entry, so each user can not have more than one rating per movie.

Tables:

login - id, user, password

movies - id, movie_name, movie_year

user_movie_ratings - id, user_id, movie_id, rating

At the moment when you login, you're taken to a members page the session is checked to ensure you're logged in, and then the list of all the movies is displayed and then when you click the move name you get take to a rating page where you can give it a rating of 1-5, then you are taken back to the movie page and the avg total rating is displayed beside the move name.

The user can just keep doing this over and incorrectly changing the avg rating.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<link rel="stylesheet" type="text/css" href="styles.css" />

<?php

require_once 'sessionCheck.php';
require_once 'init.php';
$query = $con ->query
("
SELECT movies.id, movies.movie_name, AVG(user_movie_ratings.rating) AS rating
FROM movies
LEFT JOIN user_movie_ratings
ON movies.id = user_movie_ratings.movie_id
GROUP BY movies.id
");
$movieTitle = array();
while($row = $query->fetch_object())  {
$movieTitle[] = $row;
}
?>
<head>
<title>Members</title>
</head>
<body>
<div id="wrapper">
<div id="header">                       
<div id="login">
<?php include 'loginCheck.php';?>
</div>
</div>
<div class="content"> 
<?php foreach($movieTitle as $movie):?>    
<div class = "movies">
<h3> 
<a href="movie.php?id=<?php echo $movie->id; ?>"> 
<?php echo $movie->movie_name; ?></a> 
</h3>      
<div class="movie-rating">
Rating: <?php echo round($movie->rating); ?>
</div>                  
</div>
<?php endforeach; ?>
</div>
</div>
<div id="footer"> This is the Footer</div>
</div>
</body>
</html>
halfer
  • 18,701
  • 13
  • 79
  • 158
McNoodles
  • 23
  • 9

1 Answers1

2

I would make a separate table with both user_id and movie_id.

Table - users
id
username
...

Table - movies
id
title
...

Table - ratings
id (auto)
movie_id
user_id


[edit]
If you make an unique index of both movie_id and user_id, the database will only accept each user to vote once/movie. If the same user tries to vote on the same movie more than once, a SQL-error will be created, unless you use INSERT IGNORE or ON DUPLICATE KEY UPDATE (if you want to accept that the user changes the rating).

[edit again - IMPORTANT]
You need to make both id:s in the same unique-index, if you create one index/id, each user ID will only be accepted once, and the same thing goes for the movies. (you don't want that)

(correct me if I'm wrong anyone?)

SebHallin
  • 863
  • 5
  • 11
  • You can also add the rating to the `rating`-table, in this way you can `SELECT sum(rating) FROM ratings WHERE movie_id = ?`. – SebHallin Mar 08 '15 at 16:42
  • thanks for the reply I have those tables already I was just wondering in code how I would go about doing it – McNoodles Mar 08 '15 at 16:46
  • Thanks I will look up how I go about doing this and give it ago – McNoodles Mar 08 '15 at 17:16
  • Give it a try and write again if you fail. Here is some help [unique index](http://stackoverflow.com/questions/635937/how-do-i-specify-unique-constraint-for-multiple-columns-in-mysql), [insert ignore or duplicate key](http://stackoverflow.com/questions/548541/insert-ignore-vs-insert-on-duplicate-key-update) – SebHallin Mar 08 '15 at 18:57
  • Thanks again quite new to php start like 3 weeks ago : P – McNoodles Mar 08 '15 at 19:03
  • Hey Sebastian just want to say "LOVE YOUR FACE" got it so the user can only enter one rating per movie now just need to get the updating rating to work : D – McNoodles Mar 09 '15 at 16:03
  • You can read more about [on duplicate key update here](http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html) – SebHallin Mar 09 '15 at 16:14
  • I just got it work from that link thank you very much, now I just need to suggest movies to users based on the ratings of the other users FUN FUN FUN – McNoodles Mar 09 '15 at 16:19