calculate the average for a rating system

Question

I want calculate the average for a rating system, but I have normal table in innodb and one column in json with name: "reviews" with this structure:

{"comments": 
    [
        {"name": "Jonh", "text": "nice phone", "star": 4}, 
        {"name": "igon", "text": "not good", "star": 2}, 
        {"name": "marco", "text": "i not like this", "star": 3},
        {"name": "david", "text": "good product", "stelle": 5}
    ]
}

Now I need calculate the average star. it is in sql or in php? In sql I dont know how, in php I have the problem with the query for extract only the all star, for example:

$reviews_nn = $rowprod["reviews"];
$reviews = json_decode($reviews_nn,true);
$max = 0;
$n = 0;
foreach ($reviews[comments][star] as $rate => $count) {
    echo 'Seen ', $count, ' ratings of ', $rate, "\n";
    $max += $rate * $count;
    $n += $count;
}
echo 'Average rating: ', $max / $n, "\n";

But the result is NAN...Is not integer? It is integer in star...
star=4
not star="4"

I hope you can help me....Tanks!!!

what is this `$reviews_nn = $rowprod["reviews"];`? and also `$reviews['comments']['star'] as $rate => $count`, but still it will not work i think — Alive to die - Anant, Mar 31 '16 at 10:49
As is so often the case, the solution is to normalize your data. — symcbean, Mar 31 '16 at 12:00

score 5 · Accepted Answer · answered Mar 31 '16 at 10:58

Your code is not understandable at all, but i create an example for you and i think it will help to tackle your problem:-

<?php
$data = '{"comments": [{"name": "Jonh", "text": "nice phone", "star": 4}, {"name": "igon", "text": "not good", "star": 2}, {"name": "marco", "text": "i not like this", "star": 3}, {"name": "david", "text": "good product", "stelle": 5}]}'; // your json data

//$reviews_nn = $rowprod["reviews"]; // i don't get from where you are getting this
$reviews = json_decode($data,true); // decode the json data
$max = 0;
$n = count($reviews['comments']); // get the count of comments
echo "<pre/>";print_r($reviews); // print json decoded data
foreach ($reviews['comments'] as $rate => $count) { // iterate through array
$max = $max+$count['star'];
}
echo 'Average rating: ', $max / $n, "\n";
?>

Output:- https://eval.in/545582

Note:- I have taken your json data, but changed the variable name. I hope its easy to understand what's going on in my code.Thanks.

score 0 · Answer 2 · answered Mar 31 '16 at 10:59

$reviews_nn = $rowprod["reviews"];
$reviews = json_decode($reviews_nn,true);
$numberOfReviews = 0;
$totalStars = 0;
foreach($reviews['comments'] as $review)
{
    $numberofReviews++;
    $totalStars += $review['star'];
}
$average = $totalStars/$numberOfReviews;
echo 'Average rating: '.$average;

This should work.

calculate the average for a rating system

2 Answers2