How to query the hierarchical information of the classification directory as efficiently as possible?

I have a simple article classification table, the main structure is as follows

id	父id	标题
1	0	所有项目
2	1	PHP
3	1	Mysql
4	2	ThinkPHP

...
etc. It should be a very common directory structure.
means that 所有项目 is the first level, PHP and Mysql are the second level, and ThinkPHP is the third level directory of PHP.

Then the question is, assuming I only know the data id=4, how can I list the breadcrumb navigation such as
所有项目 >> PHP >> ThinkPHP
?

Of course I know that I can list it using a loop, but it always feels like a waste of performance. Is there a faster way?
No answers to similar questions were found. Maybe I didn’t use the keywords reasonably enough. Finally I have no choice but to ask a question, thank you in advance for your generous advice

Reply content:

I have a simple article classification table, the main structure is as follows

id	父id	标题
1	0	所有项目
2	1	PHP
3	1	Mysql
4	2	ThinkPHP

...
etc. It should be a very common directory structure.
means that 所有项目 is the first level, PHP and Mysql are the second level, and ThinkPHP is the third level directory of PHP.

Then the question is, assuming I only know the data id=4, how can I list the breadcrumb navigation such as
所有项目 >> PHP >> ThinkPHP
?

Of course I know that I can list it using a loop, but it always feels like a waste of performance. Is there a faster way?
No answers to similar questions were found. Maybe I didn’t use the keywords reasonably enough. Finally I have no choice but to ask a question, thank you in advance for your generous advice

Add another field to find all parent categories and generate breadcrumbs when adding a new subcategory. Of course, the data stored in this field can be stored flexibly, and fixed breadcrumbs do not have to be generated. You can generate an array, josn or serialize it and save it, and then take it out to generate breadcrumbs in real time to cope with the flexible generation and change of breadcrumbs.

The most important idea is to store the parent class data when adding a new subclass, which is similar to the principle of static caching.

There is no other way, because since you need to use breadcrumbs (from 4 to 2 to 1), you must have a query and extraction process.

It’s just a matter of recursion or iteration

If your category has a fixed number of levels, you can write a specified SQL for each level to reduce the number of requests. Loop queries at the code level waste performance, but the code implementation is simple and highly flexible

First of all, you can determine the hierarchical directory data of a website, which will not be very much.
You can completely take out all the data from the db.
Then recursively traverse.
The consumption Time operations are placed at the code level to reduce database queries.

There are not many efficient methods, cache should be one

Thanks for the invitation. Generally, the changes to this kind of classification are very small. You can use foreach to traverse it and cache it for later use. This is how I usually use it