Nested SQL resulting to Nested List

Question

Any ideas on how can I generate a nested list without the need to recreate a lot of select statements?

I'm currently using this code

<ol>
<?php
$getparents=mysql_query("select id,subject from list");
    while($parent=mysql_fetch_assoc($getparents)){
?>
       <li><?php echo $parent["id"];?></li>
<?php

       $childsparent=$parent["id"];
       $getchild=mysql_query("select id,subject from list where parent_id='".$childsparent."'");
       if (!mysql_num_rows($getchild){
       echo '</ol>';
       }
       else
       {
       echo '<ol>';
       while ($child=mysql_fetch_assoc($getchild)){
       echo '<li>'.$child["subject"].'</li>';
       }
       $childsparent=$child["id"];
       }
?>
</ol>

Is there a way to stop the while from getting all results and check a result first if it has child nests before it moves forward?

The result should be something like

1.

2.

2.1

2.1.1

2.2

3

Could do with your DB schema. To achive a nesting loop, you will need to create an infinite loop to your logic, e.g. create a function to create the ul and li; if there is children, run the function from the function on the children. I use XSL for this. MySQL ested set may also be worth researching. — Kohjah Breese
– Kohjah Breese, Commented Aug 25, 2014 at 20:12
Thanks I think I got it though I need to do some tests. Found that I can do recursive function calls but it's gonna be tricky. I guess my question now is if it would be able to maintain the value of the original function who called the child function — Alexis Zenigata
– Alexis Zenigata, Commented Aug 25, 2014 at 20:17

Kohjah Breese · Accepted Answer · 2014-08-25 21:00:34Z

I found this function I wrote some time ago. I think it is the kind of thing you want. You just need to change the logic to print out rather than store to an array:

function nestify( $arrs, $depth_key = 'depth' )
{
    $nested = array();
    $depths = array();

    foreach( $arrs as $key => $arr ) {
        if( $arr[$depth_key] == 0 ) {
            $nested[$key] = $arr;
            $depths[$arr[$depth_key] + 1] = $key;
        }
        else {
            $parent =& $nested;
            for( $i = 1; $i <= ( $arr[$depth_key] ); $i++ ) {
                $parent =& $parent[$depths[$i]];
            }

            $parent[$key] = $arr;
            $depths[$arr[$depth_key] + 1] = $key;
        }
    }

    return $nested;
}

$arr = array(
    array( 'name' => 'Joe Splogs', 'depth' => 0 ),
    array( 'name' => 'ProSplogger', 'depth' => 0 ),
    array( 'name' => 'Pinky Lyres', 'depth' => 1 ),
    array( 'name' => 'Pseudologia fantastica', 'depth' => 2 ),
    array( 'name' => 'TextLinkBarry', 'depth' => 1 ),
    array( 'name' => 'Foo bar Jones', 'depth' => 0 )
);

$new = nestify( $arr, 'depth' );

#-> Returns

array (
    '0' => array
        (
            'name' => 'Joe Splogs',
            'depth' => 0
        ),
    '1' => array
        (
            'name' => 'ProSplogger',
            'depth' => 0
            '2' => array
                (
                    'name' => 'Pinky Lyres',
                    'depth' => 1
                    '3' => array
                        (
                            'name' => 'Pseudologia fantastica',
                            'depth' => 2
                        ),

                ),
            '4' => array
                (
                    'name' => 'TextLinkBarry',
                    'depth' => 1
                ),

        ),
    '5' => array
        (
            'name' => 'Foo bar Jones',
            'depth' => 0
        ),
);

Collectives™ on Stack Overflow

Nested SQL resulting to Nested List

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related