How can multiple user ratings be efficiently stored and calculated in a PHP and MySQL setup?

To efficiently store and calculate multiple user ratings in a PHP and MySQL setup, you can create a ratings table in your database with columns for the user ID, item ID, and rating value. When a user submits a rating, you can insert a new row into the ratings table. To calculate the average rating for an item, you can use a SQL query to retrieve all ratings for that item and then calculate the average in PHP. 

// Insert a new rating into the ratings table
$user_id = 1;
$item_id = 123;
$rating_value = 4;

$query = "INSERT INTO ratings (user_id, item_id, rating_value) VALUES ($user_id, $item_id, $rating_value)";
$result = mysqli_query($conn, $query);

if ($result) {
    echo "Rating added successfully";
} else {
    echo "Error adding rating";
}

// Calculate the average rating for an item
$item_id = 123;

$query = "SELECT AVG(rating_value) AS avg_rating FROM ratings WHERE item_id = $item_id";
$result = mysqli_query($conn, $query);
$row = mysqli_fetch_assoc($result);

$average_rating = $row['avg_rating'];

echo "Average rating for item $item_id: $average_rating";

Keywords

PHP MySQL user ratings efficient storage calculation

Related Questions