Multilevel data output from the database

Let’s say there is a table in the database that has the following fields:

id, name, parent_id

And 5 lines written:

1, value, 0
2, value, 1
3, value, 2
4, value, 3
5, value, 4

Everything seems to be nothing if you know in advance that the tree will have 5 levels.

How to code a request when the end level is unknown?

Z.Y. Please give me an idea, you don’t need to write a solution for me)


Answer 1, authority 100%

There was already something like that, I don’t remember whether it was decided or not. In general, in pure mysql <5.0 it is fundamentally unsolvable without php, in 5.0+ we look here, section ” The classic hierarchy is id and parent_id.”. But this is if you have the right to create stored procedures.

So in php, the essence is simple: we make a recursive function, for example, gettree($pid = 0), which will get all the root sections, then for each $section->children = gettree($section->id);. It can be optimized by first collecting an array of levels: select the first sections, put in $levels[0]an array of the form ($id => $object). Then we collect all the id of the first level and make the query "SELECT * ... WHERE pid IN(".implode(', ', $pids).")". Then for 3, and so on, until the query returns empty. And then again we go through all the levels and put the sections in the children of the previous one.

If there are no selection criteria, we stupidly load everything (“SELECT * FROM sections WHERE 1”) and parse it into php, again, recursively =) starting from $section->pid==0.