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>';
}

Answer 2

你需要调整你的循环，foreach循环没有用，只要玩一下如果其他的来检查父类是相同的还是不同的，查询的结果会在每行中有父类的名称，

$get_categories = "SELECT * 
 FROM parent_categories a
  INNER JOIN  child_categories b ON a.pid = b.lpid
 ORDER BY a.pid";
$currentParent = false;
while ($rowCategories = $q_get->fetch())
{
if ($currentParent != $rowCategories['pid'])
{
echo '<h1>'.htmlentities($rowCategories['parent_name']).'</h1>';
$currentParent = $rowCategories['pid'];
}
echo '<p>' . htmlentities($rowCategories['category_name']) . '</p>';
}

上面的代码会像一样输出

<h1>Parent Cat 1</h1>
    <p>subcategory 1</p>
    <p>subcategory 2</p>
<h1>Parent cat 2</h1>
    <p> subcategory 3</p>

编辑@egyal的评论对答案

Answer 3

INNER JOIN的结果是：

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

如果您想将它们格式化为数组中的数组，请使用php