php类别，子类别树

Question

我以前在长而复杂的语句中这样做过，但我正在尝试清理一些代码。

我有以下表结构

parent_categories            child_categories
-------------------          ----------------------
|pid|category_name|          |ID|category_name|lpid|
| 01| Parent cat 1|          |01|subcategory 1| 01 |
| 02| Parent cat 2|          |02|subcategory 2| 01 |
-------------------          |03|subcategory 3| 02 |
                             ----------------------

我正在尝试回显 sql 结果，以便它们采用以下格式:

Parent Cat 1
    subcategory 1
    subcategory 2
Parent cat 2
    subcategory 3

这是我正在使用的代码，但它让位于许多结果，我仍然被循环搞得一团糟。

$get_categories = "SELECT * FROM
                                parent_categories a
                            INNER JOIN
                                child_categories b
                                ON a.pid = b.lpid";

$q_get= $conn->prepare($get_categories);
$q_get->execute();
    while ($rowCategories= $q_get->fetch())
    {
        echo $rowCategories['parent_name']."<br/>";
            foreach ($rowCategories as $parent)
                {
                    echo $parent."<Br?>";
                    echo $rowCategories['category_name']. "<Br/>";
                }

    }   

谢谢

Answer 1

$qry = $conn->prepare('
  SELECT   a.pid, a.parent_name, b.category_name
  FROM     parent_categories a
      JOIN child_categories  b ON a.pid = b.lpid
  ORDER BY a.pid
');

if ($qry->execute()) {
  echo '<ul>';

  $row = $qry->fetch();
  while ($row) {
    $current_pid = $row['pid'];

    echo '<li>', htmlentities($row['parent_name']), '<ul>';
    do {
      echo '<li>', htmlentities($row['category_name']), '</li>';
    } while ($row = $qry->fetch() and $row['pid'] == $current_pid);
    echo '</ul></li>';
  }

  echo '</ul>';
}