How to display all parents of a subcategory to the main category?

Question

PHP

Good afternoon.
There are categories and subcategories, nesting can be up to 4 levels of subcategories.
It is necessary to display breadcrumbs on the subcategory page in order to go to its page by clicking on any ancestor.
The id of the current subcategory $subcategory_id = 4 is known, and it is necessary to find out only its ancestors from it, up to the parent with parent_id = 0.
How the database is arranged:


As I think:
- you need to form an array with all previous subcategories and the main category
- display this array in this form, i.e. from the main parent to the current subcategory


What is done:
I tried to make a function to form an array, but it gives an error
"Uncaught TypeError: Cannot access offset of type string on string" on line " $sub_array['text'] = $row['title'];"

As I think, I need to put in array "title" and "id" of each subcategory and main category.
Then use recursion to process any amount of nesting.

function get_node_data($subcategory_id)
{
    global $connection;
    $query = "SELECT * FROM category WHERE id = $subcategory_id";
    $res = mysqli_query($connection, $query);
    $result = mysqli_fetch_assoc($res);
            $output = array();
            foreach ($result as $row) {
                $sub_array = array();
                $sub_array['title'] = $row['title'];
                $sub_array['id'] = $row['id'];
                $sub_array['nodes'] = array_values(get_node_data($row['parent_id']));
                $output[] = $sub_array;
            }
            return $output;
}

Here is the part of the code where everything needs to be output

<ul class="breadcrumb">
        <li class="breadcrumb-item"><a href="<?= PATH ?>">Главная</a></li>
       <li class="breadcrumb-item">Категория</li>
 </ul>

Actually, please help me understand and correct the code, what am I doing wrong or not thinking correctly?

Answer 1

WITH RECURSIVE
cte AS ( SELECT *, 1 level
         FROM category 
         WHERE id = $category_id
         UNION ALL
         SELECT cat.*, cte.level + 1
         FROM category cat
         JOIN cte ON cat.id = cte.parent_id )
SELECT *
FROM cte
ORDER BY level;

For older versions:

SELECT CONCAT_WS('=>', c1.id, c2.id, c3.id, c4.id, c5.id) path
FROM category c1
LEFT JOIN category c2 ON c1.parent_id = c2.id
LEFT JOIN category c3 ON c2.parent_id = c3.id
LEFT JOIN category c4 ON c3.parent_id = c4.id
LEFT JOIN category c5 ON c4.parent_id = c5.id
WHERE c1.id = $category_id

Well, accordingly, adjust to the desired type of output set.

Answer 2

In MySQL 8, solved with a single query:

WITH RECURSIVE `cte` (`id`, `parent_id`, `title`, `n`) AS (
  SELECT `id`, `parent_id`, `title`, 0
    FROM `table`
    WHERE `id` = :categoryId
  UNION
  SELECT `t`.`id`, `t`.`parent_id`, `t`.`title`, `n`+1
    FROM `cte`
    JOIN `table` AS `t` ON `t`.`id` = `cte`.`parent_id`
)
SELECT `id`, `title`
  FROM `cte`
  ORDER BY `n` DESC

It remains only to select all the lines from the answer and output them in a loop.