PHP recursively get all sections

Question

I am trying to get the sub-sections of sections recursively from the database. Right now, my code only gets the parents but not the children though. What modification do I have to do to this code to accomplish my goal? Thanx

    function getSections() 
{
    $this->connectToDB();

    // get list of sections that has no parents
    $sql ="SELECT * FROM sections WHERE parent = 0 ORDER BY id ASC";
    $result = mysql_query($sql);
    while($row=mysql_fetch_array($result))
    {
        $thisID = $row['id'];

        // recursivly get childeren
        $childeren = $this->recursivlyGetSections($thisID);

        // add to the final result
        $toReturn .= "$thisID<br>$childeren";
    }

    // return final result
    return $toReturn;
}

Have you considered performing a [`JOIN`](http://dev.mysql.com/doc/refman/5.0/en/join.html) rather than running multiple queries? — Sampson, Jun 28 '11 at 15:52
@Jonathan Sampson♦: Wouldn't that limit the maximum depth? — Piskvor left the building, Jun 28 '11 at 15:54
totally agree join is the way forward here make sure you perform the right join though :) good call Jonathan — Deviland, Jun 28 '11 at 15:55
@Piskvor That would depend on how deep the relationships go. If it's a fixed depth, a JOIN would work. — Sampson, Jun 28 '11 at 15:56
What does the `recursivlyGetSections()` method look like? Isn't that where the getting of children would happen? — Wiseguy, Jun 28 '11 at 15:56
There is no easy solution to this: it is the problem of "transitive closure", which relational databases don't handle well. — Colin Fine, Jun 28 '11 at 15:59
@Эџad: Maybe this is interesting to you: http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree — Tomalak, Jun 28 '11 at 16:06

Naftali · Accepted Answer · 2011-06-28T16:18:06.473

4

function getSections() 
{
    $this->connectToDB();

    // get list of sections
    $sql ="SELECT * FROM sections ORDER BY id ASC, parent ASC";
    $result = mysql_query($sql);
    $rezArray = array();
    while($row=mysql_fetch_array($result))
    {
        if(!isset($rezArray[$row['parent']])) 
              $rezArray[$row['parent']] = array();
        $rezArray[$row['parent']][] = $row;
    }

    //.. do something with the array
}

Use $rezArray which has all the results sorted by parent -- instead of doing multiple queries.

edited Jun 28 '11 at 16:18

answered Jun 28 '11 at 15:54

Naftali

144,921
39
244
303

This is what I use for the threaded board I created for my work's intranet site. Unlimited nested threads possible and easy to use. – Jonathan Kuhn Jun 28 '11 at 16:03

score 2 · Answer 2 · answered Jun 28 '11 at 15:56

2

If you're wanting to store a full tree structure in your database, and it is read from more often that it is written to, you might want to consider implementing the storage as a nested set:

http://en.wikipedia.org/wiki/Nested_set_model

answered Jun 28 '11 at 15:56

Steven Dickinson

503
2
7

1

the usual good comment for trees.. but I wouldn't call this a *pragmatic* answer since he can't even write a simple tree retrieval code. – Karoly Horvath Jun 28 '11 at 16:00
@yi_H: Well, gotta start *somewhere*, no? Anyway, the answers are not exclusively for the OP's benefit, so +1 from me. – Piskvor left the building Jun 28 '11 at 16:04
1

Sure... and I will write the answer to store the whole thing in MongoDB and then he can retrieve the whole structure as one document. – Karoly Horvath Jun 28 '11 at 16:08
@yi_H Don't be so negative! ;-) I agree to @Piskvor, sometimes people need a nudge to think outside of their box. – Tomalak Jun 28 '11 at 16:31

PHP recursively get all sections

2 Answers2