Ibrahim Al Rosid - 1 year ago 55
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

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 confirmation = 4
";

\$sql3= "
SELECT AVG(rating)
FROM rating
";

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'
if (mysqli_query(\$conn, \$sql)) {
echo 'success';
}
}
``````

Any helps?

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

Try:

``````\$sql= "INSERT INTO rating VALUES (DEFAULT,'\$prov', '\$star','\$detail', '\$user')";
\$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';
}
}
``````