[SOLVED] logic problem creating a data-driven nested list

[eluser]Nicholai[/eluser]
Hi, I've seen a couple of posts similar to this but am still having trouble wrapping my mind around the concept. I'd like to build a nested list based on data from three tables. The tables look like this:
products
id
descr

versions
id
product_id
descr

patches
id
version_id
descr

When I'm doing it one piece at a time, it's easy. User chooses a product, ajax call returns a list of relevant versions, process repeats for choosing a version and getting a list of patches. When the patch is chosen, I present the user with compatible products, returned from a CompatRules table.

Administration is what's getting me. I want to present the user with a single, nested list or tree like so:

product1
-version1
--patch1
--patch2
-version2
--patch1
product2
product3
-version8
--patch1

User will check boxes beside these (probably using jsTree or BlueShoes tree control) and I'll update the CompatRules table. I'm sure I need to use nested foreach logic to build the list, but I don't understand the right way to do it.

Any advice would be awesome. Thanks!

[eluser]Hakkam[/eluser]
In PHP you need 3 levels loop in retrieving these datas.
1. You should query product, retrieve them and loop for showing them.
2. In the process of 1st loop, you should query version based on product_id, retrieve them and loop for showing them.
3. In the process of 2nd loop, you should query patch based on version_id, retrieve them and loop for showing them.

[eluser]Nicholai[/eluser]
I realize this code has bad syntax in a lot of places, but does this look like the right concept?

Code:

function index()
    {

    $productquery = get_products();
    <ul>
    foreach($productquery as $productrow)
        {
            $product_filter = $productrow->id;

            <li>$productrow->descr</li>

            <ul>
            $versionquery = get_versions($product_filter);
            foreach($versionquery as $versionrow)
            {
                $version_filter = $versionrow->id;
                
                <li>$versionrow->descr</li>
                <ul>
                $patchquery = get_patches($version_filter);
                foreach($patchquery as $patchrow)
                {
                    <li>$patchrow->descr</li>
                }
                end foreach;
                </ul>
            }
            end foreach;
            </ul>
        }
    end foreach;
    </ul>

    }

[eluser]Evil Wizard[/eluser]
The html in the function would break it, but yeah looks like you got the idea

[eluser]Nicholai[/eluser]
Is it possible to achieve this in a view, or does it make more sense to build the html list in my controller, save it to a variable and just send the whole thing to the view? Since versions are dependent on products (and patches on versions), I don't see how I can do the nested loops in the view.

I apologize if these are noob questions, CodeIgniter is my first foray into PHP.

[eluser]Đaяк Đaηтє[/eluser]
Sure, is posible to achieve this in a view, you just only pass every parameter necesary to the view. I don't undestand what are you doing in sql, but a think that is easier make a join between the tables, don´t you think that?

[eluser]Nicholai[/eluser]
I couldn't figure it out in a view, so I'm building the HTML in the controller. Here is the final code; I'll update the topic to mark it solved.

Code:

function index()
    {
    
    $productquery = $this->Compat_model->get_products(NULL);

    $output = '<ul>';

    foreach($productquery as $productrow)
        {
            $product_filter = $productrow->ID;

            $output .= '<li>'.$productrow->Description.'</li>';

            $output .= '<ul>';

            $versionquery = $this->Compat_model->get_version($product_filter);

            foreach($versionquery as $versionrow)
            {
                $version_filter = $versionrow->ID;
                
                $output .= '<li>'.$versionrow->Description.'</li>';
    
                $output .= '<ul>';

                $patchquery = $this->Compat_model->get_patch($version_filter);

                foreach($patchquery as $patchrow)
                {
                    $output .= '<li>'.$patchrow->Description.'</li>';
                }
                $output .= '</ul>';
            }
            $output .= '</ul>';
        }

    $output .= '</ul>';

    echo $output;
    
    }

[eluser]Đaяк Đaηтє[/eluser]
Sure you can:

Code:

function index()
    {
    
    $data['productquery'] = $this->Compat_model->get_products(NULL);
    $this->load->vars($data);
    $this->load->view('my_result_view');
    
    }

in the view ("my_result_view") you need to put this code

Code:

<?php
$output = '<ul>';

    foreach($productquery as $productrow)
        {
            $product_filter = $productrow->ID;

            $output .= '<li>'.$productrow->Description.'</li>';

            $output .= '<ul>';

            $versionquery = $this->Compat_model->get_version($product_filter);

            foreach($versionquery as $versionrow)
            {
                $version_filter = $versionrow->ID;
                
                $output .= '<li>'.$versionrow->Description.'</li>';
    
                $output .= '<ul>';

                $patchquery = $this->Compat_model->get_patch($version_filter);

                foreach($patchquery as $patchrow)
                {
                    $output .= '<li>'.$patchrow->Description.'</li>';
                }
                $output .= '</ul>';
            }
            $output .= '</ul>';
        }

    $output .= '</ul>';

    echo $output;
?&gt;

[eluser]Evil Wizard[/eluser]
Your view would possibly look better like this

Code:

<ul>
&lt;?foreach($productquery as $productrow):?&gt;
    <li>&lt;?=$productrow->Description;?&gt;</li>
    <ul>
    &lt;?$versionquery = $this->Compat_model->get_version($productrow->ID);?&gt;
    &lt;?foreach($versionquery as $versionrow):?&gt;
        <li>&lt;?=$versionrow->Description;?&gt;</li>
        <ul>
        &lt;?$patchquery = $this->Compat_model->get_patch($versionrow->ID);?&gt;
        &lt;?foreach($patchquery as $patchrow):?&gt;
            <li>&lt;?=$patchrow->Description;?&gt;</li>
        &lt;?endforeach;?&gt;
        </ul>
    &lt;?endforeach;?&gt;
    </ul>
&lt;?endforeach;?&gt;
</ul>

[eluser]Nicholai[/eluser]
That's definitely easier to read, but queries 2 and 3 are dependent on query 1, so I wouldn't have a way to pass that data in, short of moving all the logic from the controller into the view. I've created the menu as xml as well (using an awesome helper from here) and am trying to see if that works better for jsTree or DHTMLX tree. But that's a separate thing so I think I'll start a different thread for it.

Thanks!