php - 为什么使用 SQL AVG() 函数时 mysqli_num_rows 总是返回 1？

Question

我正在开发一个投票系统，并且遇到 SELECT AVG 函数和 mysqli_num_rows 之间的不兼容问题。

使用 AVG() 时，mysqli_num_rows 始终返回数字 1。但是，我需要 if 语句的实际行数。我怎样才能做到这一点？

$consulta_nota_geral = "SELECT * , avg(comp1) as competencia1 , avg(comp2) as competencia2 , avg(comp3) as competencia3 , avg(comp4) as competencia4 , avg(comp5) as competencia5  FROM votos WHERE topic_id = '$topic_id'";
$consulta_nota_geral_conect = mysqli_query($conn,$consulta_nota_geral) or die (mysqli_error($conn));

if(mysqli_num_rows($consulta_nota_geral_conect) > 0) {
    //other code here
}

Answer 1

我认为不兼容是指 mysqli_num_rows() 返回 1。这是因为 AVG() 将行分组为一组，然后计算平均值。

如果想获取真实的行数，可以使用COUNT(*)。

因此，您的查询将是:

SELECT COUNT(*) as number_of_rows, avg(comp1) as competencia1 , avg(comp2) as competencia2 , avg(comp3) as competencia3 , avg(comp4) as competencia4 , avg(comp5) as competencia5  FROM votos WHERE topic_id = '$topic_id'

然后，您可以使用mysqli_fetch_row()或mysqli_fetch_assoc()来获取数据。

$consulta_nota_geral = "SELECT COUNT(*) as number_of_rows, avg(comp1) as competencia1 , avg(comp2) as competencia2 , avg(comp3) as competencia3 , avg(comp4) as competencia4 , avg(comp5) as competencia5  FROM votos WHERE topic_id = '$topic_id'";
$consulta_nota_geral_conect = mysqli_query($conn,$consulta_nota_geral) or die (mysqli_error($conn));

$data = mysqli_fetch_assoc($consulta_nota_geral_conect);
if($data["number_of_rows"] > 0) {
    //your code
}