php - 优化/简化多层分类查询

Question

我有一个具有以下架构的 MySQL 表(名称:item_categories):

id                  INT(11)           PRIMARY KEY, AUTO INCREMENT
category_name       VARCHAR(100)      NOT NULL
parent_id           INT(11)           NOT NULL, Default 0

示例数据:

id  category_name   parent_id
=============================
1   Fruit           0
2   Animal          0
3   Furniture       0
4   Apple           1
5   Orange          1
6   Cat             2
7   Dog             2
8   Black Dog       7
9   White Dog       7

如果我必须列出类别及其父类别，我必须多次循环访问数据库表。

我的问题是:如何简化循环任务？

当前循环任务查询:

// 1st query
$sql = "SELECT * FROM item_categories";
$rs = $db->query($sql);
while($row = $db->result($rs)) {
  if((int)$row['parent_id'] === 0) {
    // it is parent
    echo $row['category_name'] . PHP_EOL;
  } else {
    $category = $row['category_name'];

    // it has parent
    $sql = "SELECT * FROM item_categories WHERE id=" . $row['parent_id'];
    $rs2 = $db->query($sql);
    $row2 = $db->result($rs);
    $parent_id = (int)$row2['parent_id'];
    while($parent_id != 0) {
      $sql = "SELECT * FROM item_categories WHERE id=" . $row['parent_id'];
      $rs2 = $db->query($sql);
      $row2 = $db->result($rs);
      $parent_id = (int)$row2['parent_id'];
      $category = $row2['category_name'] . ' > ' . $category;
    }
    echo $category;
  }
}

将输出:

Fruit
Animal
Furniture
Fruit > Apple
Fruit > Orange
Animal > Cat
Animal > Dog
Animal > Dog > Black Dog
Animal > Dog > White Dog

Answer 1

根据我在评论中给您的链接 - 试试这个:

function renderBreadcrumbs($db, $id, $path = null) {
    $res = $db->query("SELECT id, parent_id, category_name FROM item_categories WHERE id = " . $id);
    $row = $db->result($res);

    $path = $row['category_name'] . ' > ' . $path;

    if($row['parent_id'])
        $path = renderBreadcrumbs($db, $row['parent_id'], $path);

    return $path;
}

$res = $db->query("SELECT id, parent_id, category_name FROM item_categories ORDER BY parent_id ASC");

while($row = $db->result($res)) {
    $breadcrumb = null;

    if(!$row['parent_id'])
        $breadcrumb .= $row['category_name'];
    else
        $breadcrumb .= renderBreadcrumbs($db, $row['parent_id'], $row['category_name']);

    echo $breadcrumb . PHP_EOL;
}