例如,我有以下MySQL表:
parent_id | child_id
---------------------
1 | 4
1 | 3
1 | 5
2 | 8
3 | 7
我想以如下格式打印出父项及其所有子项:
parent | child
---------------------
| 4
1 | 3
| 5
---------------------
2 | 8
---------------------
3 | 7
基本上我只想显示父级 ONCE(Distinct) 并使用 PHP 列出它的所有子项。是否可以仅使用一个SQL查询即可检索上述结果?如果我首先查询父项,然后使用父级 id 递归查询子项,我可以得到上述结果,但这将是更多 SQL 查询命中数据库。
或者,我是否检索包含每个parent_id和children_id的结果,并使用数组在PHP中实现上述结果。如果是这样,请告诉我怎么做。
是的。正常选择并使用父项作为数组中的键。
//Query normally
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
$result[$row["parent_id"]][] = $row["child_id"];
}
或类似的东西。
编辑
显示部分如下所示:
<?php
$result = array(
1 => array(4, 3, 5),
2 => array(8),
3 => array(7)
); //Assuming you get a resultset like this.
$rowIsOpened = false; //Indicates whether a row is currently opened.
//I'm using $rowIsOpened because the row immediately after the rowspanned cell shouldn't be closed.
echo <<<HTML
<table>
<thead>
<tr>
<th>Parent</th>
<th>Children</th>
</tr>
</thead>
<tbody>
HTML;
//Echo a bunch of HTML before actually looping
foreach ($result as $parent => $children) {
echo "<tr>";
echo "<td rowspan=";
echo count($children); //Span over <how many children are> rows
echo ">$parent</td>";
$rowIsOpened = true; //Row is opened
foreach ($children as $child) {
if (!$rowIsOpened) {
echo "<tr>";
} //Only open a row if row is not opened
echo "<td>$child</td>";
echo "</tr>";
$rowIsOpened = false; //Row is now closed. Ready for next iteration.
}
}
//Close the table tags etc.
echo <<<HTML
</tbody>
</table>
HTML;