PHP 遞歸實現無限級分類：資料庫設計、樹構建、下拉選單生成

資料庫設計

CREATE TABLE `category` (
    `id`        INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `name`      VARCHAR(50)  NOT NULL DEFAULT '' COMMENT '分類名稱',
    `parent_id` INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '父級ID，0=頂級',
    `sort`      INT UNSIGNED NOT NULL DEFAULT 0 COMMENT '同級排序',
    `level`     TINYINT UNSIGNED NOT NULL DEFAULT 1 COMMENT '層級(冗余字段，便於查詢)',
    `path`      VARCHAR(255) NOT NULL DEFAULT '' COMMENT '路徑 如 0,1,5 (冗余，便於查詢子孫)',
    KEY `idx_parent` (`parent_id`),
    KEY `idx_path` (`path`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='無限級分類';

遞歸構建樹 (PHP 8.1+)

<?php
/**
 * 獲取完整分類樹 (遞歸)
 * @param PDO $pdo
 * @param int $parentId
 * @return array
 */
function getCategoryTree(PDO $pdo, int $parentId = 0): array
{
    $stmt = $pdo->prepare('SELECT * FROM category WHERE parent_id = ? ORDER BY sort, id');
    $stmt->execute([$parentId]);
    $children = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    foreach ($children as &$child) {
        $child['children'] = getCategoryTree($pdo, $child['id']);
    }
    return $children;
}

/** 生成下拉選單 (遞歸，帶縮進) */
function buildSelectOptions(array $tree, int $level = 0): string
{
    $html = '';
    $prefix = str_repeat('└─', $level);
    foreach ($tree as $node) {
        $html .= "{$prefix}{$node['name']}\n";
        if ($node['children']) {
            $html .= buildSelectOptions($node['children'], $level + 1);
        }
    }
    return $html;
}

// 使用
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', '');
$tree = getCategoryTree($pdo);
echo '' . buildSelectOptions($tree) . '';

非遞歸 (單次查詢 + 陣列組裝) - 大數據量優選

<?php
/**
 * 一次查詢所有，PHP 組裝樹 (O(N))
 */
function buildTreeSingleQuery(PDO $pdo): array
{
    $stmt = $pdo->query('SELECT * FROM category ORDER BY parent_id, sort, id');
    $all = $stmt->fetchAll(PDO::FETCH_ASSOC);
    
    $tree = [];
    $map = []; // id => node reference
    
    foreach ($all as $node) {
        $node['children'] = [];
        $map[$node['id']] = &$node; // 引用賦值
        
        if ($node['parent_id'] === 0) {
            $tree[] = &$node;
        } else {
            if (isset($map[$node['parent_id']])) {
                $map[$node['parent_id']]['children'][] = &$node;
            }
        }
    }
    unset($node); // 釋放引用
    return $tree;
}

/** 獲取某節點所有子孫 ID (利用 path 字段，極快) */
function getDescendantIds(PDO $pdo, int $id): array
{
    $stmt = $pdo->prepare('SELECT id FROM category WHERE path LIKE ?');
    $stmt->execute(["%,{$id},%"]); // path 格式: 0,1,5,
    return $stmt->fetchAll(PDO::FETCH_COLUMN);
}

/** 獲取某節點所有祖先 (利用 path) */
function getAncestors(PDO $pdo, int $id): array
{
    $stmt = $pdo->prepare('SELECT * FROM category WHERE id IN (SELECT TRIM(BOTH "," FROM REPLACE(path, ",", ",")) FROM category WHERE id = ?)');
    // 更簡單：先查 path，再解析
    $path = $pdo->query("SELECT path FROM category WHERE id = $id")->fetchColumn();
    $ids = array_filter(explode(',', trim($path, ',')));
    if (!$ids) return [];
    $placeholders = implode(',', array_fill(0, count($ids), '?'));
    $stmt = $pdo->prepare("SELECT * FROM category WHERE id IN ($placeholders) ORDER BY level");
    $stmt->execute($ids);
    return $stmt->fetchAll(PDO::FETCH_ASSOC);
}

前端交互：懶加載樹 (zTree / jstree)

// 簡單 AJAX 載入子節點
$('#tree').jstree({
    core: {
        data: function (node, cb) {
            if (node.id === '#') {
                // 根節點
                $.get('/api/category/children', {parent_id: 0}, function (data) {
                    cb(data.map(d => ({id: d.id, text: d.name, children: d.children_count > 0})));
                });
            } else {
                $.get('/api/category/children', {parent_id: node.id}, function (data) {
                    cb(data.map(d => ({id: d.id, text: d.name, children: d.children_count > 0})));
                });
            }
        }
    }
});

常見業務操作

總結

遞歸版 代碼簡潔，適合層級淺 (≤5)、數據量小 (≤1000)；單次查詢組裝版 適合大數據、高併發；path 冗余字段 是查詢子孫/祖先的神器。根據業務體量選型即可。