结合 mysql 结果

Question

>我有 3 张桌子

菜单：

+----+--------------+
| id |     name     |
+----+--------------+
|  1 | Salad Salmon |
|  2 | Tomato Soup  |
+----+--------------+

猫：

+----+--------------+
| id |    catname   |
+----+--------------+
|  1 |    Salads    |
|  2 |    soups     |
|  3 |    Fish      |
+----+--------------+

关系：

+--------+-------+
| menuid | catid |
+--------+-------+
|   1    |   1   |
|   1    |   3   |
|   2    |   2   |
+--------+-------+
$menu       = "menu";
$cats       = "cats";
$relation   = "relation";

$sql = "
SELECT $menu.id mi, $menu.name, $cats.id, $cats.catname
FROM $menu
INNER JOIN $relation ON $menu.id = $relation.menuid
INNER JOIN $cats ON $cats = $relation.groupid
";
$result = mysqli_query($connection, $sql); 
while($row = mysqli_fetch_assoc($result)){
echo '<div>'.$row[‘mi'].' '.$row['name'].' '.$row['catname'].'</div>';
}

输出显示此

1/沙拉三文鱼/沙拉

2/沙拉三文鱼/鱼

3/番茄汤/汤

如何像这样组合相同的 ID 和输出

1/沙拉三文鱼/沙拉， 鱼

2/番茄汤/汤

Answer 1

MySQL执行此操作的方式

SELECT menu.name, GROUP_CONCAT(catname) 
FROM relation
JOIN menu ON menuid = menu.id 
JOIN cats ON catid = cats.id
GROUP BY menuid

在此处阅读有关group_concat的更多信息

如果你想用 php 来做，

SELECT menu.id AS mid, menu.name, catname 
FROM relation
JOIN menu ON menuid = menu.id 
JOIN cats ON catid = cats.id

然后在 PHP 中

$items = [];
$result = mysqli_query($connection, $sql); 
while($row = mysqli_fetch_assoc($result)){
if (!array_key_exists($row['mid'], $items) {
$items[$row['mid']] = $row['name'] . " " . $menu['catname'];
} else {
$items[$row['mid']] .= ", " . $menu['catname'];
}
}

$num = 0;
foreach ($items as $item) {
echo "<div>{++$num}. {$item}</div>";
}

Answer 2

您可以使用聚合函数GROUP_CONCAT()：

SELECT 
$menu.id, 
$menu.name, 
group_concat($cats.catname order by $cats.id separator ', ') catnames
FROM $menu
INNER JOIN $relation ON $menu.id = $relation.menuid
INNER JOIN $cats ON $cats = $relation.groupid
GROUP BY $menu.id, $menu.name