MySQL:每个分组只打印一次数据 [英] MySQL: printing data just once for each grouping

问题描述

我正在使用 PHP/MySQL 进行编码，并且有以下查询来获取产品和产品组数据:

I'm coding in PHP/MySQL and have the following query to fetch products and product group data:

SELECT products.id,products.name,product_groups.id,product_groups.name
FROM products
INNER JOIN product_groups
ON products.id=product_groups.id
WHERE products.name LIKE '%foobar%'
ORDER by product_groups.id ASC

因此此查询获取产品并按产品组订购.我想要的是为每个产品分组只显示一次 product_groups.name .所以即使我有十个鞋类产品，组名鞋类"也只显示一次.

So this query fetches products and orders them by product group. What I would like to have is to display product_groups.name just once for each product grouping. So even if I have ten shoe products, the group name "Shoes" is only displayed once.

我使用以下 PHP 来打印结果:

I'm using the following PHP to print out the results:

while ($data = mysql_fetch_array($result))

推荐答案

如果你想在 MySQL 查询中完成它，老实说，它比它的价值更麻烦.首先，在每个分组的顶部列出一个组名的语法真的很奇怪(我记得).结果仍然被视为行，因此组名将被视为一行，所有其他列都为 Null，因此您不会在 PHP 脚本中真正节省任何时间或精力，因为它必须执行 if 语句当它击中组名而不是组数据时捕获.

If you want it done in the MySQL query, it is honestly more trouble than it's worth. For one, the syntax is really wonky (as I recall) to have a group name listed at the top of each grouping. And the results are still treated as rows, so the group name will be treated like a row with all the other columns as Null, so you won't really save any time or effort in the PHP script as it has to do an if statement to catch when it hits a group name instead of the group data.

如果您希望通过 PHP while 循环来完成，那么 Johan 走在正确的轨道上.我在类似情况下使用以下内容:

If you want it done by the PHP while loop, Johan is on the right track. I use the following for a similar situation:

$result = $sql->query($query);

$prev_group = "";

while($data = $result->fetch_assoc()){
   $curr_group = $data['group'];
   if ($curr_group !== $prev_group) {
      echo "<h1>$curr_group</h1>";
      $prev_group = $curr_group;
    }
    else {
        echo $data;
        .....
     }

显然，回显数据将被设置为以您想要的方式回显数据的一部分.但是 $prev_group/$curr_group 设置为它们唯一不匹配的时间是当您在一个新组中并且因此想要打印某种标题时.

Obviously the echo data would be set up to echo the parts of the data the way you want. But the $prev_group/$curr_group is set up so that the only time they won't match is when you are on a new group and thus want to print a header of some sort.

这篇关于MySQL:每个分组只打印一次数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！