Select from the result of the same query

Question

I have one table that looks something like this:

id  parent_id    name
1   0            page #1
2   1            Page #2
3   1            Page #3
4   2            Page #4

*parent_id* is realated to id.

page #4 is a child of page #2 and page #2 is a child of page #1 and so is page #3.

I need a mysql query that can get all children to, say id 1. Which would return all these pages since all pages "master parent" (lol) is page #1.

http://stackoverflow.com/questions/990529/mysql-how-to-find-all-ids-of-children-recursively — Brendan Bullen, Sep 05 '11 at 15:40

score 1 · Accepted Answer · answered Sep 05 '11 at 15:47

You basically have two options:

Using recursion, either in your application logic or in the query, if your RDBMS supports that
Storing left/right values for each node in your tree, which lets you easily find all the sub-tree of a node

Both those options are covered in an excellent article at sitepoint, http://www.sitepoint.com/hierarchical-data-database/ (but it doesn't cover RDBMS recursion, which yours probably don't support anyway)

score 0 · Answer 2 · answered Sep 12 '11 at 09:03

Thought I just leave a sollution to this problem. Not just in mysql but with php.

This is a function that calls it self to check if the current page (in the loop) has children. It will return an array with all children (including parent) id's.

public function getPageChildren($parent_id) {
    $result = mysqli_query($con, 'SELECT id FROM pages WHERE parent_id = '.$parent_id);
    while($children = mysqli_fetch_assoc($result)) {
        if($children) {
            $childrenArray[] = $children['id'];
            $childrensChildren = getPageChildren($children['id']);

            foreach($childrensChildren as $childrensChild) {
                $childrenArray[] = $childrensChild;
            }
        }
    }
    return $childrenArray;
}

Select from the result of the same query

2 Answers2