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>
