PHP and MySQL find all children and grandchildren of parents in database

Question

I'm trying to achieve something like the following: Get all child, grandchild etc nodes under parent using php with mysql query results

But I'm looking to do it without putting everything in an array and without nesting if statments - just go through the first level one by one and: - print the item - if the item has a child find it and print it - if the child has a child find it and print it - if the child has no child move up one level and look for the next child or item and it's childs - and so on and so on ... preferably into a UL LI set of lists and sublists

My database and the output also needs to be ordered so it looks more like this:

id     name       parent_id     orderby
1  Electronics          0         0
2  Televisions          1         10
3  Portable Electronics 1         20
4  Tube                 2         20
5  LCD                  2         10
6  Plasma               2         30
7  Mp3 Players          3         30
8  CD Players           3         20
9  2 Way Radios         3         10
10 Flash                7         10

I can do it using if statements but to do this I need to know how many levels the deepest child is and if there are, say, 7 levels that gets messy. I've seen it (at the link above) put into a multidimensional array but then it seems that you need nested for each statements to get the data out again which is pretty much the same as the nested if solution.

I'm pretty sure the answer is under my nose but haven't been able to find it here or elsewhere....

WordPress seems to have a way of doing it but I haven't been able to uncover the code there.

Any help would be much appreciated!

Answer 1

Use the following code to get the data :

function getChildren($parent) {
    $query = "SELECT * FROM tableName WHERE parent_id = $parent";
    $result = mysql_query($query);
    $children = array();
    $i = 0;
    while($row = mysql_fetch_assoc($result)) {
        $children[$i] = array();
        $children[$i]['name'] = $row['name'];
        $children[$i]['children'] = getChildren($row['id']);
    $i++;
    }
return $children;
}

Call this function using

$finalResult = getChildren('*');

EDIT by James

Just to finish this answer, to print out the results into the list:

<?php
    function printList($array = null) {
        if (count($array)) {
            echo "<ul>";

            foreach ($array as $item) {
                echo "<li>";
                echo $item['name'];
                if (count($item['children'])) {
                    printList($item['children']);
                }
                echo "</li>";
            }

            echo "</ul>";
        }
    }

    printList($finalResult);
?>