Ibrahim Al Rosid Ibrahim Al Rosid - 5 months ago 21
MySQL Question

Calculate Average Rating System in PHP

I am a little bit confused how to calculate the average rating system. The user has completely enable to give a rate and will stored in the database as shown in below image

Rating Table

so the idea is averagestar's value comes from average rating table. For an example, prov@gmail has 4 inputted rate with average value = 4 that comes from 16/4. (Total star / Total rate)

From the code shown below, i think my logic is wrong. The UPDATE averagerating SQL is not working.

$sql= "
INSERT INTO rating VALUES
(DEFAULT,'$prov', '$star','$detail', '$user')
";

$sql2="
DELETE FROM orderform
WHERE confirmation = 4
AND provider_username = '$prov'
AND user_username = '$user'
AND confirmation = 4
";

$sql3= "
SELECT AVG(rating)
FROM rating
WHERE provider_username = '$prov'
";

if (mysqli_query($conn, $sql) && mysqli_query($conn, $sql2)) {
$result = mysqli_query($conn, $sql3);
while($row = mysqli_fetch_array($result)) {
$sql= "
UPDATE theprovider
SET averagerating = '$result'
WHERE provider_username = '$prov'";
if (mysqli_query($conn, $sql)) {
echo 'success';
}
}


Any helps?

Answer Source

You cannot SET averagerating='$result' - you should update with something from $row.

Try:

$sql= "INSERT INTO rating VALUES (DEFAULT,'$prov', '$star','$detail', '$user')";
$sql2="DELETE FROM orderform WHERE confirmation=4 AND provider_username='$prov' AND user_username='$user' AND confirmation=4";
$sql3= "SELECT AVG(rating) AS myAvg FROM rating WHERE provider_username='$prov' GROUP BY provider_username";

if (mysqli_query($conn, $sql) && mysqli_query($conn, $sql2)) {
$result = mysqli_query($conn, $sql3);
while($row = mysqli_fetch_array($result)) {
$sql= "UPDATE theprovider SET averagerating='".$row["myAvg"]."' WHERE provider_username='$prov'";
if (mysqli_query($conn, $sql)) {
echo 'success';    
}
}

Edit: Added GROUP BY clause.