Building bootstrap nav from mysql

[bravo14]

Hi all

 

Trying to build a multi level nav for bootstrap from a database  below is the code I am using

 

 

<?php
$table = 'tbl_pages_'.$lang;
$sql = "SELECT * FROM $table where parent_id = 0 and visible = 1 order by `order` asc";
//echo $sql;
$result = mysqli_query($dbConn,$sql) or die(mysqli_error($dbConn));
if(mysqli_num_rows($result)>0){
    
    ?>

<nav class="navbar navbar-default navbar-fixed-top">
      <div class="container">
        <div class="navbar-header">
          <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar">
            <span class="sr-only">Toggle navigation</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
          </button>
        </div>
        <div id="navbar" class="navbar-collapse collapse">
          <ul class="nav navbar-nav navbar-right">
          <?php
          while($row = mysqli_fetch_assoc($result)){
              if($row['page'] == 'riders'){
                  $pageId = $row['page_id'];
                  //get rider information
                  $table = 'tbl_riders_'.$lang;
                  $sql2 = "SELECT * FROM $table where active = 1 order by `sort` asc";
                  $result2 = mysqli_query($dbConn,$sql2) or die(mysqli_error($dbConn));
                  if(mysqli_num_rows($result2) >0){
                      if($page == $row['page']){
                          echo '<li class="active dropdown">';
                      }
                      else{
                          echo '<li class="dropdown">';
                      }
                      echo '<a href="'.$shopConfig['url'].$row['link'].'" class="dropdown-toggle " data-hover="dropdown" data-toggle="dropdown">'.$row['menu_display'];
                      echo '<b class="caret"></b></a>';
                      echo '<ul class="dropdown-menu">';
                      while($row2 = mysqli_fetch_assoc($result2)){
                          echo '<li><a href="'.$shopConfig['url'].$row['link'].'/'.$row2['id'].'/'.$row2['identifier'].'">'.$row2['rider_name'].'</a></li>';
                      }
                      $table = 'tbl_pages_'.$lang;
                      $sql3 = "SELECT * FROM $table where parent_id = $pageId and visible = 1 order by `order` asc";
                      //echo $sql3;
                      $result3 = mysqli_query($dbConn,$sql3) or die(mysqli_error($dbConn));
                      if(mysqli_num_rows($result3)>0){
                          while($row3 = mysqli_fetch_assoc($result3)){
                              echo '<li><a href="'.$shopConfig['url'].$row3['link'].'">'.$row3['menu_display'].'</a></li>';
                          }
                      }
                      echo '</ul>';
                      echo '</li>';
                  }
                  echo '';
              }
              if(($row['page'] != 'shop') && ($row['page'] !='riders')){
                  $pageId = $row['page_id'];
                  $pagetable = 'tbl_pages_'.$lang;
                  $dropdown = '';
                  $subSQL = "SELECT * FROM $pagetable where parent_id = $pageId and visible =1 order by `order` asc";
                  //echo $subSQL;
                  $subresult = mysqli_query($dbConn,$subSQL) or die(mysqli_error($dbConn));
                  $subRows = mysqli_num_rows($subresult);
                   if($page == $row['page']){
                       if($subRows >0){
                           $dropdown = ' dropdown';
                       }
                  echo '<li class="active'.$dropdown.'">';
                  }
                  else{
                      if($subRows >0){
                           $dropdown = ' class="dropdown"';
                       }
                      echo '<li'.$dropdown.'>';
                  }
                  if($subRows > 0){
                  echo '<a href="'.$shopConfig['url'].$row['link'].'" class="dropdown-toggle " data-hover="dropdown" data-toggle="dropdown">'.$row['menu_display'].' <b class="caret"></b></a>';
                  echo '<ul class="dropdown-menu">';
                  while($row3 = mysqli_fetch_assoc($result3)){
                      echo '<li><a href="'.$shopConfig['url'].$row3['link'].'">'.$row3['menu_display'].'</a></li>';
                  }
                  echo '</ul>';
                  }
                  else{
                  echo '<a href="'.$shopConfig['url'].$row['link'].'">'.$row['menu_display'].'</a>';
                  }      
                  }
                      
          }
                  echo '</li>';
          }
          else{
          if($row['page'] =='shop' && $shopConfig['shop'] == 1 && $shopConfig['shop_status']=='2'){
              if($page == 'shop'){
                  echo '<li class="active">';
              }
              else{
                  echo '<li>';
              }
            echo '<a href="'.$shopConfig['url'].$row['link'].'">'.$row['menu_display'].'</a></li>';
          }
          ?>
          </ul>
          
        </div><!--/.nav-collapse -->
      </div>
    </nav>
    <?php
}
?>
 
this is the HTML it is rendering
 
[code]
<nav class="navbar navbar-default navbar-fixed-top">
<div class="container">
<div class="navbar-header">
<button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar">
<span class="sr-only">Toggle navigation</span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
<span class="icon-bar"></span>
</button>
</div>
<div id="navbar" class="navbar-collapse collapse">
<ul class="nav navbar-nav navbar-right">
<li><a href="http://www.plymouthdevils.co/">Home</a><li><a href="http://www.plymouthdevils.co/sponsors">Sponsors</a><li class="active"><a href="http://www.plymouthdevils.co/news">News</a><li><a href="http://www.plymouthdevils.co/photos">Photos</a><li class="dropdown"><a href="http://www.plymouthdevils.co/riders" class="dropdown-toggle " data-hover="dropdown" data-toggle="dropdown">Riders<b class="caret"></b></a><ul class="dropdown-menu"><li><a href="http://www.plymouthdevils.co/riders/1/lee-smart">Lee Smart</a></li><li><a href="http://www.plymouthdevils.co/riders/2/henry-atkins">Henry Atkins</a></li><li><a href="http://www.plymouthdevils.co/riders/3/callum-walker">Callum Walker</a></li><li><a href="http://www.plymouthdevils.co/riders/4/richard-andrews">Richard Andrews</a></li><li><a href="http://www.plymouthdevils.co/riders/5/saul-bulley">Saul Bulley</a></li><li><a href="http://www.plymouthdevils.co/riders/6/steve-boxall">Steve Boxall</a></li></ul></li><li><a href="http://www.plymouthdevils.co/fixtures">Fixtures</a><li><a href="http://www.plymouthdevils.co/season-tickets">Season Tickets</a></li>
</div>
</div>
[/code]

 

I't's probably something obvious I've missed, but can't spot it.

[mac_gyver]

you have too many queries, code, and logic, so it's not possible to make any sense from the code you posted what result you actually want from that code. you should not run select queries inside of loops and you have closing html markup in an else{} statement, at about about line 90, that corresponds to opening html markup that's inside the corresponding if(){} statement.

 

start by separating the concerns in your code. you should execute ONE sql query statement that gets all the related data at that you want, in the order that you want it, then fetch that data into a php array variable, then loop over the fetched data to produce the output. you can test each step to make sure it is doing what you want before going onto the next step.

 

edit: you should also use exceptions to handle database statement errors so that you don't have to have error handling logic at each statement that can fail. with exceptions, your main code only has to deal with error free statements.

Edited December 27, 2016 by mac_gyver

[bravo14]

Think part of my problem is on some parts of the menu I will need to get menu options from a different table as well as the pages menu if that makes sense.

[mac_gyver]

see the following example, that separates all the database code out of the presentation logic, making it easier to write, test, and debug just the presenation logic - 

// retrieve and store the data into a structure that supports the output you are producing
// this is made up data, your code would produce this same structure from your actual data
$data = array();
// the main array index is the page name - gets tested against the value in $page for the active attributes
$data['home'] = array('link'=>'not sure if your links include the / or if it is part of the shopConfig url','menu_display'=>'Home','children'=>array()); // parent only, no-empty children

$data['riders'] = array('link'=>'riders','menu_display'=>'Riders','children'=>array(
array('id'=>1,'identifier'=>'lee-smart','rider_name'=>'Lee Smart'), // add an array to the children array for each rider data row
)); // married with children - go Al Bundy


// produce the output from the data
?>
<nav class="navbar navbar-default navbar-fixed-top">
    <div class="container">
        <div class="navbar-header">
            <button type="button" class="navbar-toggle collapsed" data-toggle="collapse" data-target="#navbar" aria-expanded="false" aria-controls="navbar">
            <span class="sr-only">Toggle navigation</span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            <span class="icon-bar"></span>
            </button>
        </div>
        
        <div id="navbar" class="navbar-collapse collapse">
            <ul class="nav navbar-nav navbar-right">
            <?php
            foreach($data as $page_name=>$arr)
            {
                if(empty($arr['children']))
                {
                    // parent only, no children
                    $active = $page == $page_name ? " class='active'" : '';
                    echo "<li{$active}><a href='{$shopConfig['url']}{$arr['link']}'>{$arr['menu_display']}</a></li>
                    ";
                }
                else
                {
                    // w/children
                    $active = $page == $page_name ? "active " : '';
                    echo "<li class='{$active}dropdown'>
                        <a href='{$shopConfig['url']}{$arr['link']}' class='dropdown-toggle' data-hover='dropdown' data-toggle='dropdown'>{$arr['menu_display']}<b class='caret'></b></a>
                        <ul class='dropdown-menu'>
                        ";
                    // loop over the children
                    foreach($arr['children'] as $child)
                    {
                        // define different handling for different data structures
                        switch($page_name)
                        {
                        case 'riders':
                            echo "<li><a href='{$shopConfig['url']}{$arr['link']}/{$child['id']}/{$child['identifier']}'>{$child['rider_name']}</a></li>
                            ";
                        break;
                        default:
                            // child
                            echo "<li><a href='{$shopConfig['url']}{$child['link']}'>{$child['menu_display']}</a></li>
                            ";
                        }
                    }
                    echo "</ul>
                    </li>
                    ";
                }
            }
            ?>
      </ul>
    </div><!-- /.navbar -->
  </div><!-- /.container -->
</nav>

for the example output you posted, this code produces the correct opening and closing elements and should work.