How can I use recursion to retrieve parent nodes?

Question

I have created a recursive function to get parent products of a product. I'm almost sure I'm nearly there, but need help snapping out of recursion.

The result I am looking for is something like:

Product 1 (id:1, parent:none)

Product 2 (id:2, parent:1)

--- --- Product 3 (id:3, parent:2)

--- --- Product 4 (id:4, parent:2)

--- Product 5 (id:5, parent:1)

--- --- Product 6 (id:6, parent:5)

--- --- Product 7 (id:7, parent:5)

My updated function is as follows:

function get_parents ($pid, $found = array()) {
    array_push ($found, $pid);

    $sql = "SELECT * FROM products WHERE child_id = '$pid'";
    $result = mysql_query($sql) or die ($sql);

    if(mysql_num_rows($result)){
        while($row = mysql_fetch_assoc($result)){
            $found[] = get_parents($row['pid'], $found);
        }
    }
    return $found;
}

I call it using a simple:

$parents = get_parents($pid);

The problem I am having is that when I run it, it creates an infinite loop, which doesn't break.

I don't want to get done for spamming so I have saved the result of my array to a text file, which can be seen here http://vasa.co/array.txt

Any help would be seriously appreciated :-)

I don't see the infinite loop. The while loop would end when you ran out of rows. See a database has a finite number of rows...you can see where I am going with that. Fix your code, your using variables that do not exist in your posted code, and provide more information. — Security Hound, Sep 07 '11 at 12:54
The code doesn't look quite right, but are you sure you are having infinite recursion? Infinite recursion would cause PHP to [crash catastrophically](http://stackoverflow.com/questions/7327393/why-does-an-infinitely-recursive-function-in-php-cause-a-segfault), so you might be having a different problem. One way you could check this is by printing out the `$pid` and the `$found` array every time you enter the function body. — NullUserException, Sep 07 '11 at 12:57
can you post your table content (at least the rows with id 1 & 2) — thumbmunkeys, Sep 07 '11 at 13:29

score 2 · Accepted Answer · answered Sep 08 '11 at 15:13

Hmm.. judging by your structure of your DB, it would seem that something is amiss unless I'm missing something

The statement

$sql = "SELECT * FROM products WHERE child_id = '$pid'";

Tells me that for each product, you are storing the ID of the child. Typically, in a tree based structure, it's the reverse, you store the parent ID not the child - unless you want a child node to have many parents. If that is the case, then the function could easily run into problems. Consider the following:

| ID | Child_ID |
+----+----------+
| 1  | 2        |
| 2  | 1        |

This would cause an infinite loop. If you store the parent_id, then by that nature, you are encoding the graph to be hierarchical. Since every product has A parent, then the logic can be written recursively.

The could then be written as such?

function get_parents ($pid, $found = array()) {
    array_push ($found, $pid);

    $sql = "SELECT * FROM products WHERE id = '$pid'";
    $result = mysql_query($sql) or die ($sql);

    if(mysql_num_rows($result)){
        while($row = mysql_fetch_assoc($result)){
            $found[] = get_parents($row['parent_id'], $found);
        }
    }
    return $found;
}

How can I use recursion to retrieve parent nodes?

1 Answers1