php - 使用两个mysql表创建动态菜单

我有两个mysql表，叫做“parents”和“childs”
在我的父表中，我有4列（id、link、labe、have_childs）
在childs表中还有4列（id、c_link、c_lable、parent_id）
我用这样的查询得到值

"SELECT parents.*, childs.* FROM parents, childs WHERE parents.id = childs.p_id;"

然后用foreach循环得到这个结果

array(7) { ["id"]=> string(1) "1" ["link"]=> string(3) "veg" ["lable"]=> string(9) "Vegitable" ["childs"]=> string(1) "1" ["c_link"]=> string(6) "carrot" ["c_lable"]=> string(6) "carrot" ["p_id"]=> string(1) "1" }
array(7) { ["id"]=> string(1) "2" ["link"]=> string(3) "Fru" ["lable"]=> string(6) "Fruits" ["childs"]=> string(1) "1" ["c_link"]=> string(6) "grapes" ["c_lable"]=> string(6) "grapes" ["p_id"]=> string(1) "2" }
array(7) { ["id"]=> string(1) "3" ["link"]=> string(3) "veg" ["lable"]=> string(9) "Vegitable" ["childs"]=> string(1) "1" ["c_link"]=> string(5) "beeat" ["c_lable"]=> string(5) "beeat" ["p_id"]=> string(1) "1" }

然后我做了这个

<?php
foreach($result as $myresult){ ?>
    <ul>
    <li><a href="<?php echo $myresult['link']; ?>"><?php echo $myresult['lable']; ?></a>
        <?php
            if($myresult['childs'] == 1){
                echo '<div><ul>';
                echo '<li><a href="'.$myresult['c_link'].'">'.$myresult['c_lable'].'</a></li>';
                echo '</div></ul>';
            }
        ?>

<?php
}
?>

然后我得到了这个结果

.Vegitable
   carrot
.Fruits
   grapes
.Vegitable
   beet

但这不是我想要的结果
我需要胡萝卜和甜菜都放在蔬菜下面。
有办法吗？

最佳答案

我已经创建了以下表格。
父母

+----+------+-----------+
| id | link | label     |
+----+------+-----------+
|  1 | veg  | Vegetable |
|  2 | fru  | Fruit     |
+----+------+-----------+

儿童

+----+-----------+--------+---------+
| id | parent_id | link   | label   |
+----+-----------+--------+---------+
|  1 |         1 | beets  | Beets   |
|  2 |         1 | carrot | carrots |
|  3 |         2 | apple  | Apples  |
+----+-----------+--------+---------+

一种方法是选择所有的父母，并循环通过每个人得到他们的孩子。这是一些伪代码。我可能把ul/li嵌套搞砸了，但你应该知道。

// Start by getting all the parents.
$parents = // SELECT * FROM PARENTS;

// Loop through all the parents.
@foreach ($parents as $parent) {
    echo "<ul>";
    echo "<li>" . $parent['label'];

    // Get all the childs of the current parent.
    $childs = // SELECT * FROM CHILDS WHERE parent_id = $parent['id'];

    // Loop through all the childs of the current parent.
    @if ($childs has results) {
        echo "<ul">;

        @foreach ($childs as $child) {
            echo "<li>" . $child['label'] . "</li>";
        @endforeach

        echo "</ul>";
        echo "</li>";
    @else
        echo "</li>";
    @endif

    echo "</ul">;

@endforeach;