How can I get all children from a parent row in the same table?
- by Johnny Freeman
Let's say I have a table called my_table that looks like this:
id | name | parent_id
1 | Row 1 | NULL
2 | Row 2 | NULL
3 | Row 3 | 1
4 | Row 4 | 1
5 | Row 5 | NULL
6 | Row 6 | NULL
7 | Row 7 | 8
8 | Row 8 | NULL
9 | Row 9 | 4
10 | Row 10 | 4
Basically I want my final array in PHP to look like this:
Array
(
[0] => Array
(
[name] => Row 1
[children] => Array
(
[0] => Array
(
[name] => Row 3
[children] =>
)
[1] => Array
(
[name] => Row 4
[children] => Array
(
[0] => Array
(
[name] => Row 9
[children] =>
)
[1] => Array
(
[name] => Row 10
[children] =>
)
)
)
)
)
[1] => Array
(
[name] => Row 2
[children] =>
)
[2] => Array
(
[name] => Row 5
[children] =>
)
[3] => Array
(
[name] => Row 6
[children] =>
)
[4] => Array
(
[name] => Row 8
[children] => Array
(
[0] => Array
(
[name] => Row 7
[children] =>
)
)
)
)
So, I want it to get all of the rows where parent_id is null, then find all nested children recursively.
Now here's the part that I'm having trouble with:
How can this be done with 1 call to the database?
I'm sure I could do it with a simple select statement and then have PHP make the array look like this but I'm hoping this can be done with some kind of fancy db joining or something like that.
Any takers?
