Mysql一个父多个子结果没有重复的父行 - Mysql one Parent multiple Child result without repeating Parent rows

我正在用Codeigniter和MYSQL构建一个CMS。有一张这样的桌子

CREATE TABLE parent
(`parentId` int, `parentName` varchar(50));
INSERT INTO parent
(`parentId`, `parentName`)
VALUES
(1, 'Movie'),
(2, 'Food'),
(3, 'Sports');
CREATE TABLE child
(`childId` int, `parentId` int,`childName` varchar(50));
INSERT INTO child
(`childId`, `parentId`, `childName`)
VALUES
(1, 1, 'Harry Potter'),
(2, 3, 'Foot Ball'),
(3, 2, 'Pizza'),
(4, 2, 'Burger'),
(5, 1, 'Avengers'),
(6, 1, 'Fury'),
(7, 3, 'Cycling');

并希望输出像这样的输出

foreach($parent as $parentData):
echo"<h4>".$parentData->parentName."</h4>";
echo"<ul>";
 foreach($child as childData):
 echo"<li>".$childData->childName."</li>";
 endforeach;
echo"</ul>"
endforeach;

产生这个

<h4>Movie</h4>
<ul>
<li>Harry Potter</li>
<li>Avengers</li>
<li>Fury</li>
</ul>
<h4>Food</h4>
<ul>
<li>Pizza</li>
<li>Burger</li>
</ul>
<h4>Sports</h4>
<ul>
<li>Foot Ball</li>
<li>Cycling</li>
</ul>

欢迎对代码进行任何调整。因为我在过去的几个小时里一直在尝试这个。提前谢谢。

更新:我按照CodeGodie的建议实现了我的代码的答案，并添加了这样的视图。

foreach ($result as $arr) {
if (!isset($final[$arr["parentName"]])) {
    $final[$arr["parentName"]] = array();
}
array_push($final[$arr["parentName"]], $arr);
}
foreach ($final as $parent=> $childs) {
echo $parent."<br>";
foreach ($childs as $child) {
    print_r($child) ;
}
}

现在我得到了我所期望的结果

有多种方法可以做到这一点。但是我会使用以下方法:

您需要创建以下SQL查询:

SELECT * FROM parent p
JOIN child c ON c.parentId = p.parentId
ORDER BY parentName

或在Codeigniter中:

$this->db->select("*");
$this->db->from("parent p");
$this->db->join("child c", "c.parentId = p.parentId ");
$this->db->order_by("parentName");
$q = $this->db->get();
return $q->result_array();

，它会给你类似这样的结果:

$result = array(
    array(
       "parentId" => 2,
       "parentName" => "Food",
       "childId" => 3,
       "childName" => "Pizza",
    ),
    array(
       "parentId" => 2,
       "parentName" => "Food",
       "childId" => 4,
       "childName" => "Burger",
    ),
    array(
       "parentId" => 1,
       "parentName" => "Movie",
       "childId" => 1,
       "childName" => "Harry Potter",
    ),
    array(
       "parentId" => 1,
       "parentName" => "Movie",
       "childId" => 5,
       "childName" => "Avengers",
    )
);

遍历结果:

foreach ($result as $arr) {
    if (!isset($final[$arr["parentName"]])) {
        $final[$arr["parentName"]] = array();
    }
    array_push($final[$arr["parentName"]], $arr);
}
var_dump($final);

这将给你一个这样的结果，然后你可以迭代构建你的HTML:

array (size=2)
  'Food' => 
    array (size=2)
      0 => 
        array (size=4)
          'parentId' => int 2
          'parentName' => string 'Food' (length=4)
          'childId' => int 3
          'childName' => string 'Pizza' (length=5)
      1 => 
        array (size=4)
          'parentId' => int 2
          'parentName' => string 'Food' (length=4)
          'childId' => int 4
          'childName' => string 'Burger' (length=6)
  'Movie' => 
    array (size=2)
      0 => 
        array (size=4)
          'parentId' => int 1
          'parentName' => string 'Movie' (length=5)
          'childId' => int 1
          'childName' => string 'Harry Potter' (length=12)
      1 => 
        array (size=4)
          'parentId' => int 1
          'parentName' => string 'Movie' (length=5)
          'childId' => int 5
          'childName' => string 'Avengers' (length=8)

希望能有所帮助

我想你是在寻找组concat

https://www.percona.com/blog/2013/10/22/the-power-of-mysqls-group_concat/

使用此选项，您可以选择以逗号分隔的子值列表。然后在PHP中，你只需要在逗号处爆炸，然后循环。

请记住，默认的group_concat限制通常相当低，因此您可能需要增加它- https://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_group_concat_max_len

这个查询将得到所有的数据:

SELECT parent.parentName, child.childName 
FROM parent 
INNER JOIN child on parent.parentId = child.parentId;

并返回

------------------------------
parentname    |    childName
------------------------------
Movie         |    Harry Potter
Movie         |    Avengers
Movie         |    Fury
et cetera...