Adding togeather all ratings from my results.
Ok I have a table called ratings and in the table it is layed out like this.
ID | UserID | Rating | TMDB | TYPE
-----------------------------------
1 34 6 432 3
-----------------------------------
2 34 9 432 3
My sql is
$sql = "SELECT * FROM `tbl_rating` WHERE `tmdb`='" . $tmdb . "' AND `type`='" . $type . "' ";
ok this pulls up the information I want. I can now use a while or foreach command to call $row['rating']. However I dont know how I can add the results togeather or do any basic math. This is a movie rating database and I am trying to get my script to add togeather all the "ratings" in the results column and then work out the average vote.
I hope this make scence. Any help would be great thank you all!
this script has star rating for Gore, Scare, Acting, Story (this is the where I use the type field).
Add to a variable containing the total.
$total_rating = 0;
$rating_count = 0;
while ($row = $stmt->fetch()) {
$total_rating += $row['rating'];
$rating_count++;
}
if ($rating_count > 0) {
$avg_rating = $total_rating / $rating_count;
} else {
$avg_rating = 0;
}
You could also just do this in the query:
$sql = "SELECT AVG(rating) AS avg_rating FROM `tbl_rating` WHERE `tmdb`= :tmdb AND `type`= :type ";
$stmt = $conn->prepare($sql);
$stmt->bindParam(':tmdb', $tmdb);
$stmt->bindParam(':type', $type);
$stmt->execute();
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$avg_rating = $row['avg_rating'];