php - 如何只检索 1 行而不是 2 行？

Question

<?php
        include "connect.php";
        // Make a MySQL Connection
        $sql = "SELECT COUNT(*) AS comment FROM comment co INNER JOIN item i WHERE co.items= i.items GROUP BY i.items"; 
        $result = $mysqli->query($sql); 
        while ($row = $result->fetch_array(MYSQLI_ASSOC))
        {
        ?>
        No of Comments:&nbsp;<?php echo $row['comment'];?>
        <?php
        }
        ?>

它的 echo 是这样的:示例:

评论数:7 评论数:7

求助!谢谢!

Answer 1

那是因为您使用了 GROUP BY。这意味着您将获得表中每个不同的 i.items 的计数。

您可以通过将这些项目添加到查询结果来检查(我将 WHERE 更改为 ON):

SELECT 
  i.items,
  COUNT(*) AS comment 
FROM 
  comment co 
  INNER JOIN item i ON co.items = i.items 
GROUP BY 
  i.items

解决方案是指定您想要的项目....

SELECT 
  i.items,
  COUNT(*) AS comment 
FROM 
  comment co 
  INNER JOIN item i ON co.items = i.items 
WHERE
  i.items = 'foo'
/* -- Grouping not needed anymore, since you only have 1 item (= 1 group)
GROUP BY 
  i.items*/ 

...或者不分组，所以你得到所有项目的总数:

-- Will fetch the total number of comments (that are linked to an item)
SELECT 
  COUNT(*) AS comment 
FROM 
  comment co 
  INNER JOIN item i ON co.items = i.items 

... 或限制结果此查询只是截断结果，仅返回其中一项的计数。该项目现在或多或少是随机选择的，尽管您可以添加 ORDER BY 来影响它。

SELECT 
  COUNT(*) AS comment 
FROM 
  comment co 
  INNER JOIN item i ON co.items = i.items 
GROUP BY 
  i.items
/* -- Optional order, if you need to influence which of the items is preffered.
ORDER BY 
  i.items*/
LIMIT 1

所以，根据你的问题，我只能说出问题是什么，而不能说哪种解决方案最适合你。但这里有几个选项可供选择。 :)