php和mysql目录样式数据库中的子菜单

Question

I've been trying to make a menu with submenus on each main item and then sub_sub_menus on each sub menu through php and mysql. I feel like i'm so nearly there but i'm new to php and mysql so i've come here for your help. I have been searching for hours for an answer, i've found examples of dropdown menus but none like this.

Not sure how to add an sql database here but will try to add if requested.

My php code below so far.

$result = mysqli_query($link, 'SELECT * FROM library WHERE parent_id =' . $parent_id = 0);

$subResult = mysqli_query($link, 'SELECT * FROM library WHERE parent_id >'. $parent_id);
    if(mysqli_num_rows($result) > 0) {
                echo '<ul class="nav list">';

                        while($row = mysqli_fetch_array($result))
                        {
                            echo '<li class="nav-item">'. '<a href="#">'.$row['mainfolder'] .'</a>'.
                              '<ul class="dropdown-content nav">';
                            while($sub = mysqli_fetch_array($subResult))
                            {
                                if($row['id']===$sub['parent_id'])
                                {

                                        echo $sub['parent_id'];

                                    $submenuItems = mysqli_query($link, 'SELECT mainfolder FROM library WHERE parent_id ='. $sub['parent_id']);
                                    $items = mysqli_fetch_array($submenuItems);
                                    foreach ($items as $eachItem){
                                       echo' <li class="submenu">'.'<a class="sub" href="#">'.htmlspecialchars($eachItem, ENT_QUOTES, 'UTF-8').'</li>'.'</a>'.'</ul>';
                                    }
                                }
                            }

                        }
                            echo '</ul>'.'</li>'; 


                echo '</ul>';
            }

Answer 1

Because you asked nicely, and because I like the challenge of recursive functions I decided to go ahead and help you out.

My original answer was getting a bit out of hand, so I cleaned it up. I also did a ton of debugging, and fixed a few "bugs" that I found in my original post.

echo "<pre>"; //preserve whitespace

//I mocked up some arrays ('canned data') to make things easier to show and test
//order of the parents matter ( should be in the order left to right of the menu )
$parents = [
   ['id' => 1, 'title' => 'item1', 'link' => 'http://www.example.com/item1', 'parent_id' => 0], 
   ['id' => 8, 'title' => 'item8', 'link' => 'http://www.example.com/item8', 'parent_id' => 0],
];

var_export( $parents );
echo "

";

//you can order children any way you want, they will still be grouped under the right parent
$children = [
    ['id' => 2, 'title' => 'item2', 'link' => 'http://www.example.com/item2', 'parent_id' => 1],        
    ['id' => 3, 'title' => 'item3', 'link' => 'http://www.example.com/item3', 'parent_id' => 2],
    ['id' => 4, 'title' => 'item4', 'link' => 'http://www.example.com/item4', 'parent_id' => 1], //not ordered
    ['id' => 5, 'title' => 'item5', 'link' => 'http://www.example.com/item5', 'parent_id' => 2, "target" => null, "css_class" => null],
    ['id' => 6, 'title' => 'item6', 'link' => 'http://www.example.com/item6', 'parent_id' => 1, "target" => "_blank"], 
    ['id' => 7, 'title' => 'item7', 'link' => 'http://www.example.com/item7', 'parent_id' => 8, "css_class"=>"test"],  
];

var_export($children);
echo "

";

$lv = 1;

$html = "<ul class=\"menu menu_lv0\" >
";

foreach( $parents AS &$parent ){
    $html = buildMenuTree( $parent, $children, $html, $lv, $lv );   //pass by refrence so no return needed
}

$html .= "</ul>
";

var_export( $parents );
echo "

";

echo htmlspecialchars( $html )."

"; //htmlspecialchars for display reasons


//=========================================
//            DEFINE FUNCTION
//=========================================

//$parent is passed by refrence
function buildMenuTree( &$parent, $children, $html='', $lv=0, $indent=0 ){

    if( $lv == 1 || $lv == 2) $mode = true;

    if( !$children ) return;
    reset( $children ); //reset the array

    $t0 = getIndent( $indent );
    $t1 = getIndent( ($indent+1) );

    //css class for menu item
    $css_class = (isset($parent['css_class']) && $parent['css_class']) ? " {$parent['css_class']}" : "";
    //link target
    $target = (isset($parent['target']) &&  $parent['target']) ? "target=\"{$parent['target']}\" " : "";

    $id = $parent['id'];

    $html .= "{$t0}<li id=\"menu_item_{$id}\" class=\"menu_item item_lv{$lv}{$css_class}\">
";
    $html .= "{$t1}<a class=\"menu_link\" href=\"{$parent['link']}\" {$target}>{$parent['title']}</a>";

    while( false !== ( $child = current( $children ) ) ){           
        //if the parents id, matches the childs parent id, then add the current child
        //as a child of parent and check it ( current child ) for any children it may have
        // add  if( ... && $limit == $lv) and pass in a $limit param to the function to limit the depth
        if( $parent['id'] == $child['parent_id'] ){
            $key = key( $children );
            //remove - to reduce array and our processing time, we can remove the current item
            //also the current item is the parent in the recusive function and therefore we
            //know it cannot be a child of itself and we wont need it in that branch
            unset( $children[$key] ); 

            //make a recursive call ( call this method again
            //&$parent, $children, $html='', $lv=1, $indent=1
            $r_html = buildMenuTree($child, $children, "", ($lv + 1), ($indent+2));

            //creante the children array if not exists
            if( !isset( $parent['children'] ) ){
                $parent['children'] = []; 

                $html .= "
{$t1}<ul class=\"sub_menu menu_lv{$lv} parent_{$parent['id']}\" >
";
                $html .= $r_html;
            }else{
                $html .= $r_html;
            }

            ///store the child
            $parent['children'][] = $child;

        }else{
            $next = next( $children );

        };
    }

    if( !isset( $parent['children'] ) )
       $html .= "
";
    else 
       $html .= "{$t1}</ul>
";

    $html .= "{$t0}</li>
";

    return $html;
}

function getIndent( $indent, $pad = " " ){
    return str_pad("", ( $indent * 4 ), $pad);
}

echo "</pre>";

I tested this on Apache/2.4.18 (Win32) PHP/7.0.4) and it 100% works.

OUTPut

Array Tree structure Parent is passed by reference, so it's modified not returned, we need the return for the HTML

$parent = array (
  0 => 
  array (
    'id' => 1,
    'title' => 'item1',
    'link' => 'http://www.example.com/item1',
    'parent_id' => 0,
    'children' => 
    array (
      0 => 
      array (
        'id' => 2,
        'title' => 'item2',
        'link' => 'http://www.example.com/item2',
        'parent_id' => 1,
        'children' => 
        array (
          0 => 
          array (
            'id' => 3,
            'title' => 'item3',
            'link' => 'http://www.example.com/item3',
            'parent_id' => 2,
          ),
          1 => 
          array (
            'id' => 5,
            'title' => 'item5',
            'link' => 'http://www.example.com/item5',
            'parent_id' => 2,
            'target' => NULL,
            'css_class' => NULL,
          ),
        ),
      ),
      1 => 
      array (
        'id' => 4,
        'title' => 'item4',
        'link' => 'http://www.example.com/item4',
        'parent_id' => 1,
      ),
      2 => 
      array (
        'id' => 6,
        'title' => 'item6',
        'link' => 'http://www.example.com/item6',
        'parent_id' => 1,
        'target' => '_blank',
      ),
    ),
  ),
  1 => 
  array (
    'id' => 8,
    'title' => 'item8',
    'link' => 'http://www.example.com/item8',
    'parent_id' => 0,
    'children' => 
    array (
      0 => 
      array (
        'id' => 7,
        'title' => 'item7',
        'link' => 'http://www.example.com/item7',
        'parent_id' => 8,
        'css_class' => 'test',
      ),
    ),
  ),
)

HTML

<ul class="menu menu_lv0" >
    <li id="menu_item_1" class="menu_item item_lv1">
        <a class="menu_link" href="http://www.example.com/item1" >item1</a>
        <ul class="sub_menu menu_lv1 parent_1" >
            <li id="menu_item_2" class="menu_item item_lv2">
                <a class="menu_link" href="http://www.example.com/item2" >item2</a>
                <ul class="sub_menu menu_lv2 parent_2" >
                    <li id="menu_item_3" class="menu_item item_lv3">
                        <a class="menu_link" href="http://www.example.com/item3" >item3</a>
                    </li>
                    <li id="menu_item_5" class="menu_item item_lv3">
                        <a class="menu_link" href="http://www.example.com/item5" >item5</a>
                    </li>
                </ul>
            </li>
            <li id="menu_item_4" class="menu_item item_lv2">
                <a class="menu_link" href="http://www.example.com/item4" >item4</a>
            </li>
            <li id="menu_item_6" class="menu_item item_lv2">
                <a class="menu_link" href="http://www.example.com/item6" target="_blank" >item6</a>
            </li>
        </ul>
    </li>
    <li id="menu_item_8" class="menu_item item_lv1">
        <a class="menu_link" href="http://www.example.com/item8" >item8</a>
        <ul class="sub_menu menu_lv1 parent_8" >
            <li id="menu_item_7" class="menu_item item_lv2 test">
                <a class="menu_link" href="http://www.example.com/item7" >item7</a>
            </li>
        </ul>
    </li>
</ul>

I took the liberty to add a few things that I consider to be commonly implemented features of a navigation menu.

• CSS Class, if a row contains $row ['css_class'] the <li> will add it's value the it's classes, you can set it to false|null or just not have it.
• Target, if an item contains $row ['target'] the <a> tag will have a target attribute with it's value, you can set it to false|null or just not have it.

You can see these test cases:

• most items omit target and css_class I don't like to use the word class, because it a reserved word
• item5 has both target and css_class set to null any false value will omit these tags.
• item6 has a target value of _blank and the link <a target="_blank" .. in the output
• item7 has a css_class of test and has these classes menu_item item_lv2 test

Here is a list of the classes I added to the HTML

UL Classes:

• Top level <ul> will have a class of menu
• Sub level <ul> will have a class of sub_menu
• Sub level <ul> will have a class of parent_{id} where {id} is the id of the parent
• Every <ul> will have a class of menu_lv{lv} where {lv} is the nesting level

UL Classes & Id:

• Every <li> will have an id of menu_item_{id} where {id} is the record id
• Every <li> will have a class of menu_item
• Every <li> will have a class of item_lv{lv}where {lv} is the nesting level

A Classes:

• Every <a> tag will have a class of menu_link

The only catch, is do not set the children key in the record manually, $parent['children'] or $child['children']. I had to use it as a way to detect if children had been added yet. ( although with some work this could be refined, it didn't seem important )

This took quite a bit of work and time, so you are on your own for the CSS. But, I will say I have built many, many menus and this HTML should be really close to what you need (although, I admit, it has been a few years sense I made a nav menu).

Lastly you can test it online in PHPSanbox with this link

Good Luck, and enjoy!