How to fetch menu and sub-menu from database using nested do while loop?

Question

I kindly would like someone to help me on php code. What I want to do is to fetch Menu and Sub-menu from database using nested do while loop from two tables so it shows a drop-down navigation bar. Navigation table holds menu items and category table holds sub-menu. But it stops on first iteration of the loop after it fetch all menu items and the first sub-menu items for the first menu item then it shows nothing but empty result.

-- Table structure for table `category`
--

CREATE TABLE `category` (
  `cat_id` int(11) NOT NULL,
  `nav_id` int(11) NOT NULL,
  `cat_eng` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `category`
--

INSERT INTO `category` (`cat_id`, `nav_id`, `cat_eng`) VALUES
(1, 1, 'Technology'),
(2, 1, 'Science'),
(3, 1, 'Mathemathics'),
(4, 1, 'Computer'),
(5, 2, 'Geography'),
(6, 2, 'Environment'),
(7, 2, 'Weather'),
(8, 2, 'World'),
(9, 3, 'Sport'),
(10, 3, 'Food'),
(11, 3, 'Health'),
(12, 4, 'Mens'),
(13, 4, 'Womens');

-- --------------------------------------------------------

--
-- Table structure for table `navigation`
--

CREATE TABLE `navigation` (
  `nav_id` int(11) NOT NULL,
  `nav_eng` varchar(256) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

--
-- Dumping data for table `navigation`
--

INSERT INTO `navigation` (`nav_id`, `nav_eng`) VALUES
(1, 'Educational'),
(2, 'General-Knowlege'),
(3, 'Life-style'),
(4, 'Fashion');

Connection.php

<?php 
  session_start();

  $host = 'localhost';
  $user = 'root';
  $password = '';
  $dbname = 'test';

  $connection = @mysqli_connect($host, $user, $password, $dbname);

  if (!$connection) {
    die("Connection failed: " . mysqli_connect_error());
  }

  mysqli_set_charset($connection, 'utf8');
 ?>

<div class="collapse navbar-collapse" id="navbarSupportedContent">
          <ul class="navbar-nav mr-auto">

            <!-- FETCHING NAVIGATION LIST ALONG WITH DROPDOWN MENU FROM DATABASE  -->
            <?php
                //SETTING GET PAGES
                if (isset($_GET['nav'])) {
                    $pageid = $_GET['nav'];
                }else {
                    $pageid = 1;
                }

                //NAVIGATION TABLE QUERY
                 $nav_sql = "SELECT * FROM navigation";
                 $nav_query = mysqli_query($connection, $nav_sql);
                 $nav_result = mysqli_fetch_assoc($nav_query);

                 //CATEGORY TABLE QUERY
                  $cat_sql = "SELECT category.*, navigation.nav_id AS id FROM category JOIN navigation ON (category.nav_id = navigation.nav_id) WHERE category.nav_id = " .$pageid;
                  $cat_query = mysqli_query($connection, $cat_sql);
                  $cat_result = mysqli_fetch_assoc($cat_query);
                do {
                  ?> 
                  <li class="nav-item dropdown">
                    <a class="nav-link dropdown-toggle" role="button" data-toggle="dropdown" aria-haspopup="true" aria-expanded="false" href="?nav=<?php echo $nav_result['nav_id'] ?>"> <?php echo $nav_result['nav_eng']; ?></a>
                    <ul class="dropdown-menu">
                      <?php 
                        do { ?>
                          <li>
                            <a class="dropdown-item" href="?nav=<?php echo $cat_result['cat_id']; ?>"><?php echo $cat_result['cat_eng'] ?></a>
                          </li> <?php 
                        } while ($cat_result = mysqli_fetch_assoc($cat_query));
                       ?>
                    </ul>
                  </li> <?php
                } while ($nav_result = mysqli_fetch_assoc($nav_query));

             ?>
          </ul>
         </div>

Answer 1

Using the table definitions and data from the question I had a little play at getting a menu system working without nested queries and using DOMDocument to ensure that elements were written and closed correctly to avoid invalid HTML.

You could substitute your own values for the database credentials to test the following - it ought to work OK. I appreciate this is vastly different to the original and also that there are other ways to accomplish your goal - this was simply me having a playabout but it may be of use.

<?php

    ob_start();
    ob_implicit_flush();


    /* modify as appropriate */
    $dbhost =   'localhost';
    $dbuser =   'root'; 
    $dbpwd  =   'xxx'; 
    $dbname =   'xxx';

    /* create the db connection */
    try{
        mysqli_report( MYSQLI_REPORT_STRICT );
        $db = new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );
        $db->set_charset( 'utf8' );
    }catch( Exception $e ){
        exit( $e->getMessage() );
    }


    /* a utility function that greatly speeds adding DOM elements with attributes and values */
    function create( $dom, $type, $attr=array(), $parent=NULL ){
        if( $dom && is_object( $dom ) && $type && !empty( $type ) && is_array( $attr ) ){
            $node = $dom->createElement( $type );
            foreach( $attr as $key => $value ){
                if( strtolower( $key )=='text' or strtolower( $key )=='nodevalue' or strtolower( $key )=='value' && $node->tagName!='input' ){
                    if( !is_object( $value ) )$node->nodeValue=$value;
                } else {
                    if( !is_null( $value ) && !is_object( $value ) ) $node->setAttribute( $key, $value );
                }
            }
            if( isset( $parent ) && is_object( $parent ) ) $parent->appendChild( $node );
            return $node;
        }
    }   

?>
<!DOCTYPE html>
<html lang='en'>
    <head>
        <meta charset='utf-8' />
        <title>PHP & mySQL Database driven menu</title>
        <style>
            html *{font-family:verdana,arial;}
            a{text-decoration:none;}
            .active_cat{text-decoration:underline;font-style:italic}
            .active_subcat{color:red;font-style:italic}
        </style>
    </head>
    <body>
        <div class='collapse navbar-collapse' id='navbarSupportedContent'>
            <ul class='navbar-nav mr-auto'>
            <!-- this will be populated later -->
            </ul>
        </div>
    </body>
</html>
<?php

    /* capture & filter querystring variables */
    $nav_id=filter_input( INPUT_GET, 'nav', FILTER_SANITIZE_NUMBER_INT );
    $sub_nav_id=filter_input( INPUT_GET, 'subnav', FILTER_SANITIZE_NUMBER_INT );


    /* Create the basic menu of main headings based upon this simple query */
    $sql='select nav_id, nav_eng from navigation order by nav_eng asc';
    $results=$db->query( $sql );



    if( $results ){

        /* Create a new instance of DOMDocument and DOMXPath */
        libxml_use_internal_errors( true );
        $dom=new DOMDocument;
        $dom->loadHTML( ob_get_contents() );
        $xp=new DOMXPath( $dom );
        $col=$xp->query( '//div[ @id="navbarSupportedContent" ]/ul' );

        /* We have loaded the buffer - now clear the old version */
        ob_clean();

        /* We will write new DOM elements to this fragment */
        $fragment=$dom->createDocumentFragment();

        /* Maintain an array of menu items that become parent nodes for child menus */
        $nav=array();


        while( $rs=$results->fetch_object() ){

            /* create the list elemet for this category and add the link and a child `UL` element */
            $li=create( $dom, 'li', array( 'class'=>'nav-item dropdown' ), $fragment );

            /* set the attributes for the hyperlink */
            $options=array( 'class'=>'nav-link dropdown-toggle ', 'text'=>$rs->nav_eng, 'role'=>'button', 'data-toggle'=>'dropdown', 'aria-haspopup'=>'true', 'aria-expanded'=>'false', 'href'=>sprintf( '?nav=%d', $rs->nav_id ) );
            if( $rs->nav_id==$nav_id )$options['class'] .= 'active_cat';
            /* append the hyperlink to the li element */
            $a=create( $dom, 'a', $options, $li );

            /* create the child UL element and assign a reference in the $nav array for later use */
            $ul=create( $dom, 'ul', array( 'data-category'=>$rs->nav_eng, 'data-navid'=>$rs->nav_id, 'class'=>'dropdown-menu' ), $li );

            $nav[ $rs->nav_id ]=$ul;
        }
        /* Add the fragment to the main UL*/
        $col->item(0)->appendChild( $fragment );





        /* If there is a querystring variable `nav` then we query to find child menu items */
        if( $nav_id ){

            $sql='select
                    c.cat_eng, 
                    c.cat_id,
                    c.nav_id
                from category c
                    join navigation n on c.nav_id = n.nav_id
                where c.nav_id=?
                    order by c.cat_eng';
            /* create a prepared statement and bind the nav_id value as input */
            $stmt=$db->prepare( $sql );
            $stmt->bind_param('i', $nav_id );
            $res=$stmt->execute();

            if( $res ){
                /* The query succeeded, generate child menu items */
                $stmt->store_result();
                $stmt->bind_result( $cat, $cid, $nid ); # arbitrary names to correspond to fields in recordset

                /* Find the correct parent node from the array created earlier */
                $parent=$nav[ $nav_id ];


                /* iterate through the recordset & add menu items */
                while( $stmt->fetch() ) {
                    $options=array( 'class'=>'dropdown-item ', 'href'=>sprintf( '?nav=%d&subnav=%d', $nid, $cid ), 'text'=>$cat );
                    if( $cid==$sub_nav_id )$options['class'] .= 'active_subcat';

                    create( $dom, 'a', $options, create( $dom, 'li', array(), $parent ) );
                }

                $stmt->free_result();
                $stmt->close();
            }
        }

        /* render new DOM - the output buffer will now be flushed */
        exit( $dom->saveHTML() );
    }
?>