pdo php addison与来自同一领域的存储变量[关闭]

Question

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).

Answer 1

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'];