nested list using nested foreach in php pdo

Question

<ul>
      <li>Main Menu 1
        <ul>
          <li>Sub Menu 1.1
          </li>
          <li>Sub Menu 1.2
          </li>
        </ul>
      </li>
      <li>Main Menu 2
        <ul>
          <li>Sub Menu 2.1
          </li>
          <li>Sub Menu 2.2
          </li>
        </ul>
      </li>
    </ul>

Above is a representation of an unorderlist I am trying to make. I am able to make this kind of list using foreach, one function for getting the main menu and one function for getting the sub menu. I would like to solicit for suggestion on how to make a condition of some sort to determine which sub menu is for which menu because as of now all the sub menu are showing for all the menu at the same time. There should be a specific menu for a specific sub menu here is the code I work on

Server side

function RetrieveAllMenu() {
  global $dbh;
  $stmt = $dbh - > prepare("SELECT * FROM userlist_tbl WHERE username = ?");
  $stmt - > bindValue(1, $_SESSION['login_user']);
  $stmt - > execute();
  $selected_row = $stmt - > fetch(PDO::FETCH_ASSOC);
  $mem_id = $selected_row['user_id'];
  if ($stmt - > execute()) {
    if ($stmt - > rowCount() > 0) {
      $stmt = $dbh - > prepare("SELECT * FROM rolemapping_tbl WHERE user_id = ?");
      $stmt - > bindValue(1, $mem_id);
      $stmt - > execute();
      $selected_row = $stmt - > fetch(PDO::FETCH_ASSOC);
      $rolelist_id = $selected_row['rolelist_id'];
      if ($stmt - > execute()) {
        $stmt = $dbh - > prepare("SELECT * FROM roledetails_tbl WHERE rolelist_id = ?");
        $stmt - > bindValue(1, $rolelist_id);
        $stmt - > execute();
        $selected_row = $stmt - > fetch(PDO::FETCH_ASSOC);
        if ($stmt - > execute()) {
          if ($stmt - > rowCount() > 0) {
            $menu_id = array();
            while ($selected_row = $stmt - > fetch(PDO::FETCH_ASSOC)) {
              $menu_id[] = array('menuid' => $selected_row['menulist_id'], );
            }
            $stmt = $dbh - > prepare("SELECT * FROM menulist_tbl WHERE menulist_id = :menuid");
            $menu_name = array();
            foreach($menu_id as $row) {
              $stmt - > execute(array(':menuid' => $row['menuid']));
              //while ($selected_row =$stmt->fetch(PDO::FETCH_COLUMN, 0)){
              while ($selected_row = $stmt - > fetch(PDO::FETCH_ASSOC)) {
                $menu_name[] = array('menuname' => $selected_row['menu_name'], 'menuurl' => $selected_row['menu_url'], 'menuflag' => $selected_row['menu_flag'], 'menuid' => $selected_row['menulist_id']);
              }
            }
            return $menu_name;
          }
        }
      }
    }
  }
}

function RetrieveAllSubMenu() {
  global $dbh;
  $menu_name = RetrieveAllMenu();
  $stmt = $dbh - > prepare("SELECT * FROM submenulist_tbl WHERE parent_id = :menuid");
  $submenu_name = array();
  foreach($menu_name as $row) {
      //$stmt->execute(array(':menuid' => $row['menuid']));
      $stmt - > bindValue(':menuid', $row['menuid'], PDO::PARAM_STR);
      $stmt - > execute();
      while ($selected_row = $stmt - > fetch(PDO::FETCH_ASSOC)) {
        $submenu_name[] = array('submenuname' => $selected_row['submenulist_name'], 'submenuurl' => $selected_row['submenulist_url'], 'submenuflag' => $selected_row['submenulist_flag']);
      }
    }
    //print_r($submenu_name);
  return $submenu_name;
  //return in_array($menuid, $submenu_name);
}

This is html side

<?php
echo'<ul>';   
foreach (RetrieveAllMenu() as $value){
//echo'<input  type="submit" value="'.$value['menuname'].'" name="'.$value['menuname'].'"/>';
echo'<li>';
echo '<a href="'.$value['menuurl'].'" id=""'.$value['menuname'].'"">'.$value['menuname'].'</a>';
echo'<ul>';
foreach (RetrieveAllSubMenu() as $value){
//if( $value['parentid'] === $value['menuid'] ){    
echo'<li><a href="'.$value['submenuurl'].'" id=""'.$value['submenuname'].'"">'.$value['submenuname'].'</a></li>';
//}
}
echo'</ul>';  
echo'</li>';         
}   
echo'</ul>';    
?>

This is how my code looks it should look like the one above.

<ul>
          <li>Main Menu 1
            <ul>
              <li>Sub Menu 1.1
              </li>
              <li>Sub Menu 1.2
              </li>
               <li>Sub Menu 2.1
              </li>
              <li>Sub Menu 2.2
              </li>
            </ul>
          </li>
          <li>Main Menu 2
            <ul>
              <li>Sub Menu 1.1
              </li>
              <li>Sub Menu 1.2
              </li>
              <li>Sub Menu 2.1
              </li>
              <li>Sub Menu 2.2
              </li>
            </ul>
          </li>
        </ul>

Answer 1

Make your RetrieveAllSubMenu() function so that it returns only the submenus of a given menu by it's id and not for all menus

function RetrieveAllSubMenu($menu_id) {
  // your code
  $stmt = $dbh - > prepare("SELECT * FROM submenulist_tbl WHERE parent_id = :menuid");
      $stmt - > bindValue(':menuid', $menu_id, PDO::PARAM_STR);
 // more code
}

Giving you just the logic behind it but this will return the submenus for only one menu based on passed $menu_id.

So when trying to get the submenus your foreach should look something similar to this:

foreach (RetrieveAllSubMenu($value['menuid']) as $value){

I would also suggest you name your returned results for $value a bit better like:

foreach (RetrieveAllSubMenu($value['menuid']) as $submenu){

since you are also using $value for the menus and submenus this could lead to problems if you are not fully understanding when $value is overriden by the inner foreach statement

EDIT

Your foreach statements should look similar to:

foreach (RetrieveAllMenu() as $menu){
    echo'<li>';
    echo '<a href="'.$menu['menuurl'].'" id=""'.$menu['menuname'].'"">'.$menu['menuname'].'</a>';
    echo'<ul>';
    foreach (RetrieveAllSubMenu($menu['menuid']) as $submenu){
        echo'<li><a href="'.$submenu['submenuurl'].'" id=""'.$submenu['submenuname'].'"">'.$submenu['submenuname'].'</a></li>';
    }
}