How can PHP and SQL be combined to ensure accurate and consistent average ratings for items in a database?

To ensure accurate and consistent average ratings for items in a database, PHP can be used to calculate the average rating based on user inputs and SQL can be used to update the database with the new average rating. This can be achieved by retrieving all the ratings for a specific item, calculating the average using PHP, and then updating the database with the new average rating using SQL. 

// Retrieve all 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 using PHP
$average_rating = array_sum($ratings) / count($ratings);

// Update the database with the new average rating
$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();

Keywords

PHP SQL average rating database consistency

Related Questions