How to retrieve tree nodes children (recursive function help)

Question

I have a binary, the database table of relationships looks like this:

+----+----------+---------+-----+
| id | parentID | childID | pos |
+----+----------+---------+-----+
|  1 |        1 |       2 | l   |
|  2 |        1 |       3 | r   |
|  3 |        2 |       4 | l   |
|  4 |        3 |       5 | r   |
|  5 |        4 |       6 | l   |
|  6 |        5 |       7 | r   |
+----+----------+---------+-----+

I am able to extract or children of for example 1 - but I have very clumsy function for that, so I need something that works better.

The output I need should look like this:

Array
(
    [0] => Array
        (
            [id] => 2
            [parentID] => 1
            [pos] => l
        )

    [1] => Array
        (
            [id] => 4
            [parentID] => 2
            [pos] => l
        )

    [2] => Array
        (
            [id] => 6
            [parentID] => 4
            [pos] => l
        )

    [3] => Array
        (
            [id] => 3
            [parentID] => 1
            [pos] => r
        )

    [4] => Array
        (
            [id] => 5
            [parentID] => 3
            [pos] => r
        )

    [5] => Array
        (
            [id] => 7
            [parentID] => 5
            [pos] => r
        )

)

So far I came up with this function, however it returns nested array, I want it flattened ... but whenever I tried it it just fails.

function children($pid) {
    //set sql
    $sql = "SELECT * FROM relationships WHERE parentID = ".$pid; 
    //save query to result
    $result = mysql_query ($sql)
     or die("Bad request " . mysql_error()); 

    while ($item = mysql_fetch_array($result)):
     $topchild["id"] = $item["childID"];
     $topchild["parentID"]= $item["parentID"];
     $topchild["pos"] = $item["pos"];  

     $children[] = $topchild;
     $children[] = children($item["childID"]);  
    endwhile;


     return $children;
}

What do I do wrong there?

Answer 1

I want it flattened

$children[] = children($item["childID"]);

instead add each of the items in the return value separately:

foreach (children($item['childID'] as $child)
    $children[]= $child;

(Also shouldn't $topchild be initialised inside the loop?)

If you are doing a lot of recursive queries like this, a parent-child relation table is not a good choice of data structure. Consider one of the hierarchically-oriented solutions such as nested sets.