Create unordered list tree menu from data stored in an table with the adjacency list model...php

Question

I need to create a tree menu of "nth" subcategories. I settled on using the adjacency list model for my table structure, because I won't be updating this table very much and this seemed the easiest to implement for my use.

I want to style the output using "ul" and "li" tags...I already have a css and jquery solution to do the styling. My problem comes from pulling the data out of the database and using a recursive function via PHP to build the list ... the list is a concatenated string that gets parsed to build the tree. I'm really having a hard time getting the closing "ul" and "li" tags to line up just where they need to be.

Is this the best way to do this? Are there other better ways using arrays or something like that to do this? Any examples you can point me to of "best practices" for building a list like this will be appreciated. Thanks.

Here's my table structure:

portfolio_id (int), p_name (varchar), parent_portfolio_id (int) Here's what I want the data to look like when presented:

<ul>
<li>Portfolio Name
    <ul>
        <li>Sub portfolio A
            <ul>
                <li>Sub portfolio A - 1</li>
                <li>Sub portfolio A - 2</li>
                <li>Sub portfolio A - 3</li>
            </ul>
        </li>
        <li>Sub portfolio B</li>
        <li>Sub portfolio C</li>
    </ul>
</li>
</ul>

Here's the current recursive function:

function portf($ndb, $portfolio_id, $space=1, $x="", $level=1) // cat id, space to add "_" degree of categoreis times, list of categories
{
    $sql = "SELECT portfolio_id, p_name, parent_portfolio_id FROM portfolio WHERE parent_portfolio_id = $portfolio_id ORDER BY p_name ASC;";
    $select = $ndb->get_results($sql, 0, ARRAY_A);
    if( !is_null($select) )
    {
        foreach($select as $data)
        {
            $x = $x . $data->portfolio_id . '_' . $data->parent_portfolio_id . '_' . $level . str_repeat('_', $space) . $data->p_name . '-'; 
            $x = $this->portf($ndb, $data->portfolio_id, ($space+1), $x, ($level+1) );  
        }
        return $x; 
    }
    else
    {
        return $x;
    }
}

Answer 1

I have to admit, you kinda lost me on some of your code there. Whats up with all the $spaces and the str_repeat?

At any rate, this is what I'd try.

function portf($ndb, $portfolio_id, $level=1)
{
    $sql = "SELECT portfolio_id, p_name, parent_portfolio_id FROM portfolio WHERE parent_portfolio_id = $portfolio_id ORDER BY p_name ASC;";
    $select = $ndb->get_results($sql, 0, ARRAY_A);

    if( !is_null($select) )
    {
        $li = "";
        foreach($select as $data)
        {
            $sublist = portf($ndb, $data->portfolio_id, $level+1);
            $li .= "<li>{$data->p_name}{$sublist}</li>";
        }
        $ul = "<ul class=\"level_$level\">$li</ul>";
        return $ul;
    }
    else
    {
        return "";
    }
}

Answer 2

When I pass the function the root id it returns the subcategory relationship perfect for a tree that is like this:

<ul class="level_1">
<li>Category A
    <ul class="level_1">
        <li>Sub Category A-1
            <ul class="level_1">
                <li>Sub Category A-1-a</li>
            </ul>
        </li>
    </ul>
</li>
</ul>

However for a tree that has multiple children, but only some of those children have grandchildren, etc. like this code the output is returning only Category C and I can't get the string to concatenate the categories/subcategories before it.

<ul class="level_1">
<li>Category A</li>
<li>Category B
    <ul class="level_2">
        <li>Sub Category B-1</li>
    </ul>
</li>
<li>Category C
    <ul class="level_2">
        <li>Sub Category C-1</li>
    </ul>
</li>
</ul>

Bad Output:

<ul class="level_1">
<li>Category C
    <ul class="level_1">
        <li>Sub Category C-1</li>
    </ul>
</li>
</ul>

How could I change the recursive function to output the unordered list in a way that would support multiple category/subcategory relationships under the same root id?

Thanks for your help.