如何根据平均评分显示这些数据库项目以及评分所依据的评论数量

Question

I have this database table called items.

I am displaying these items with the following code. But I want to display top 5 items ordered by their overall average rating, whilst also displaying the number of reviews the score is based on.

Can anybody please help me with this?

$sql = "SELECT itme_id, name, description, rating, item_type, no_of_reviews FROM itmes";
$items = mysqli_query($con, $sql) or die(mysqli_error());

while($row = mysqli_fetch_array($itmes))
{
    $html = "";
    if ($row['item_type'] == ITEM_HIDDEN)
    {
        continue;
    }
    else
    {
        $name = $row['name'];
        $description = $row['description'];
        $rating = $row['rating'];
        $html .= "<tr><td>";
        $html .= "<h3>" . $name . "</h3>";
        $html .= "<p>" . $description . "</p>";
        $html .= "<b>Rating: " . $rating . "</b> ";
        $html .= "(Reviews: " . $row['no_of_reviews'] . ")";
        $html .= "</td></tr>";
    }
    echo $html;
} 

Answer 1

Try putting your query like this:

SELECT item_id, name, description, AVG(`rating`) as `avg_rating`, 
item_type, SUM(`no_of_reviews`) as reviews 
FROM items
GROUP BY name
LIMIT 5

Hope this helps.

Check the example and results here:

http://sqlfiddle.com/#!9/37b495/2