php - 如何循环表中的每个值并找到平均值

Question

我是一名初级程序员，我正在尝试查找供应商 2 的平均用户评分。
我的步骤如下:
1. 用户“Jimmy”给供应商 2 评分 3(评分满分 5)
2.TABLE vendoratings 已更新
3. ratingstot.php 然后计算 所有 供应商的评分 和响应数 的总和 < strong>更新表vendortotalratings如下所示

4.用户'Jimmy'点击'查看平均用户评分'
5. totalratings 和 totalno 的值在 Javascript 中被检索和划分

var average= totalratings/totalno

6. totalno 显示给用户 Jimmy。结束

问题
1. 我需要帮助在 ratingstot.php 中形成 for 或 while 循环来计算收视率和否。在将属于供应商 X 的响应插入每个供应商的 vendortotalratings 表之前。
ratingstot.php

<?php
header("Access-Control-Allow-Origin: *");
header("Content-Type: application/json; charset=UTF-8");
error_reporting(E_ERROR);
try{
    //Database connection
    $conn = new mysqli("localhost", "XXXXXXXX_XXX", "XXXXXXXX", "XXXXXXXX");

    //Unsure how to loop this to make vendor new value every loop
    for($i=0; $i<=6; $i++){
    $vendor = ??

        //Calculate sum of ratings from table ratings
        $result = $conn->query("SELECT SUM(ratings) FROM ratings WHERE vendorid = '".$vendorid."' ");
        $row = mysqli_fetch_array($result);
        $totalratings = $row[0];

        //Calculate no. of responses (by counting no. of rows)
        $result1 = $conn->query("SELECT * FROM ratings WHERE vendorid = '" . $vendorid."' ");
        $totalno = mysqli_num_rows($result);

        //inserting the results into the table
        $query  = " UPDATE vendortotalratings SET ";
        $query .= " totalratings = '". $totalratings ."', totalno='".$totalno."' ";
        $query .= " WHERE vendorid = '". $vendorid ."'";
        $result2 = $conn->query($query);

    }
    echo($outp);
}

catch(Exception $e) {
    $json_out =  "[".json_encode(array("result"=>0))."]";
    echo $json_out; 
}
?>

1. 我不知道如何循环这个，有没有更简单的步骤来计算每个供应商的平均评分？

Answer 1

您可以像这样简化所需的解决方案，而不是所有这些复杂的事情:(假设:我假设 vendorid、ratings、totalratings 和 totalno 列的类型为 INT)

• 使用下面的语句/查询获取每个vendorid 对应的totalratings 和totalno。

  $result = $conn->query("SELECT vendorid, SUM(ratings) as totalratings, COUNT(userid) as totalno FROM vendorratings GROUP BY vendorid");
  

• 现在使用 while() 循环遍历 $result 结果集。

  while($row = $result->fetch_array()){
      ...
  }
  

• 在上述while() 循环的每次迭代中，检查vendorid 值是否已经存在。如果存在，则更新具有新totalratings 和totalno 的行，否则INSERT 包含< em>vendorid、totalratings 和 totalno。

  while($row = $result->fetch_array()){
      $res = $conn->query("SELECT vendorid FROM vendortotalratings WHERE vendorid = " . $row['vendorid']);
      if($res->num_rows){
          // Update the existing row
          $conn->query("UPDATE vendortotalratings SET totalratings = ".$row['totalratings'].", totalno = ".$row['totalno']." WHERE vendorid = ".$row['vendorid']);
          echo "Affected rows: " . $conn->affected_rows . '<br />';
      }else{
          // Insert a new row
          $res = $conn->query("INSERT INTO vendortotalratings VALUES(".$row['vendorid'].", ".$row['totalratings'].",".$row['totalno'].")");
          if($res) echo "New row inserted <br />";
      }
  }
  

所以 try-catch block 的完整代码应该是这样的:

// your code

try{
    //Database connection
    $conn = new mysqli("localhost", "XXXXXXXX_XXX", "XXXXXXXX", "XXXXXXXX");

    $result = $conn->query("SELECT vendorid, SUM(ratings) as totalratings, COUNT(userid) as totalno FROM vendorratings GROUP BY vendorid");
    while($row = $result->fetch_array()){

        $res = $conn->query("SELECT vendorid FROM vendortotalratings WHERE vendorid = " . $row['vendorid']);
        if($res->num_rows){
            // Update the existing row
            $conn->query("UPDATE vendortotalratings SET totalratings = ".$row['totalratings'].", totalno = ".$row['totalno']." WHERE vendorid = ".$row['vendorid']);
            echo "Affected rows: " . $conn->affected_rows . '<br />';
        }else{
            // Insert a new row
            $res = $conn->query("INSERT INTO vendortotalratings VALUES(".$row['vendorid'].", ".$row['totalratings'].",".$row['totalno'].")");
            if($res) echo "New row inserted <br />";
        }
    }
}catch(Exception $e) {
    $json_out =  json_encode(array("result"=>0));
    echo $json_out; 
}