What are the best practices for storing and calculating averages of user ratings in a MySQL database using PHP?
When storing and calculating averages of user ratings in a MySQL database using PHP, it is important to properly structure your database tables to store ratings for each user and item. To calculate the average rating, you can use SQL queries to retrieve the ratings for a specific item, calculate the average using PHP, and then update the item's average rating in the database.
// Retrieve ratings for a specific item from the database
$query = "SELECT rating FROM ratings WHERE item_id = :item_id";
$stmt = $pdo->prepare($query);
$stmt->bindParam(':item_id', $item_id, PDO::PARAM_INT);
$stmt->execute();
$ratings = $stmt->fetchAll(PDO::FETCH_COLUMN);
// Calculate the average rating
$total_ratings = count($ratings);
$total_sum = array_sum($ratings);
$average_rating = $total_ratings > 0 ? $total_sum / $total_ratings : 0;
// Update the item's average rating in the database
$update_query = "UPDATE items SET average_rating = :average_rating WHERE id = :item_id";
$update_stmt = $pdo->prepare($update_query);
$update_stmt->bindParam(':average_rating', $average_rating, PDO::PARAM_INT);
$update_stmt->bindParam(':item_id', $item_id, PDO::PARAM_INT);
$update_stmt->execute();