MySQL与PHP实现多级父子关系中根节点查找教程

SELECT child.id, child.name, child.parent_id, parent.name AS ParentName
FROM test child
JOIN test parent ON child.parent_id = parent.id
WHERE child.id = 6;

CREATE TABLE test (
    id INT,
    name VARCHAR(255),
    parent_id INT
);

INSERT INTO test VALUES
(1, 'mike', 0),
(2, 'jeff', 0),
(3, 'bill', 2),
(4, 'sara', 1),
(5, 'sam', 4),
(6, 'shai', 5);

DELIMITER //

CREATE FUNCTION get_most_parent (input_id INT)
RETURNS VARCHAR(255)
READS SQL DATA
BEGIN
    DECLARE current_id INT;
    DECLARE parent_name VARCHAR(255);
    DECLARE next_parent_id INT;

    SET current_id = input_id;

    -- 循环向上查找，直到parent_id为0
    REPEAT
        SELECT name, parent_id
        INTO parent_name, next_parent_id
        FROM test
        WHERE id = current_id;

        -- 如果当前节点就是根节点，或者没有父节点了，则停止
        IF next_parent_id = 0 OR next_parent_id IS NULL THEN
            RETURN parent_name;
        END IF;

        -- 更新当前ID为父ID，继续向上查找
        SET current_id = next_parent_id;

    UNTIL FALSE END REPEAT; -- 理论上会通过IF条件提前返回

    -- 如果输入ID不存在或发生其他意外，返回NULL
    RETURN NULL;
END //

DELIMITER ;

SELECT
    t.id,
    t.name,
    t.parent_id,
    get_most_parent(t.id) AS TopParentName
FROM
    test t
WHERE
    t.id IN (3, 6);

<?php

// 假设这是您的数据库连接
$mysqli = new mysqli("localhost", "username", "password", "database_name");

if ($mysqli->connect_errno) {
    echo "Failed to connect to MySQL: " . $mysqli->connect_error;
    exit();
}

/**
 * 查找给定ID的最顶层父节点
 *
 * @param mysqli $mysqli 数据库连接对象
 * @param int $childId 要查找的子节点ID
 * @return array|null 包含 'id' 和 'name' 的父节点信息，如果未找到则返回 null
 */
function findTopParent(mysqli $mysqli, int $childId): ?array
{
    $currentId = $childId;
    $topParent = null;

    while (true) {
        $stmt = $mysqli->prepare("SELECT id, name, parent_id FROM test WHERE id = ?");
        $stmt->bind_param("i", $currentId);
        $stmt->execute();
        $result = $stmt->get_result();

        if ($row = $result->fetch_assoc()) {
            // 如果当前节点就是根节点
            if ($row['parent_id'] == 0) {
                $topParent = ['id' => $row['id'], 'name' => $row['name']];
                break; // 找到根节点，退出循环
            } else {
                // 继续向上追溯
                $currentId = $row['parent_id'];
            }
        } else {
            // 如果当前ID在数据库中不存在，或者追溯到某个不存在的父节点
            // 这通常意味着原始childId无效或数据存在问题
            $topParent = null;
            break;
        }
    }
    $stmt->close();
    return $topParent;
}

// 示例使用
$childIdToFind = 6; // shai
$topParentInfo = findTopParent($mysqli, $childIdToFind);

if ($topParentInfo) {
    echo "子节点ID " . $childIdToFind . " 的最顶层父节点是: " . $topParentInfo['name'] . " (ID: " . $topParentInfo['id'] . ")\n";
} else {
    echo "未找到子节点ID " . $childIdToFind . " 的最顶层父节点。\n";
}

$childIdToFind = 3; // bill
$topParentInfo = findTopParent($mysqli, $childIdToFind);

if ($topParentInfo) {
    echo "子节点ID " . $childIdToFind . " 的最顶层父节点是: " . $topParentInfo['name'] . " (ID: " . $topParentInfo['id'] . ")\n";
} else {
    echo "未找到子节点ID " . $childIdToFind . " 的最顶层父节点。\n";
}

$mysqli->close();

?>