php - Layer navigation menu -

i have 3 tables, top_category, bottom_category, products, @ moment have listed top_category, confused how approach listing second category have list knows top_category belongs too, why have foreign keys parent, if top_category example is:

prod1  prod2 prod3 etc 

and want load bottom category these top_categories this:

prod1  -subprod1  -subprod2  -subprod3 prod2  -subprod4  -subprod5 prod3  -subprod6 etc 

this database:

create table top_category (   id int primary key,   name varchar(100)  );  create table bottom_category (   id int primary key,   name varchar(100) ,   top_category_id int references top_category );  create table product (   id int primary key,   name varchar(100) ,   bottom_category_id int references bottom_category ); 

this php & html code:

<?php include('dbconnect.php');   try     {          $result = $pdo->query(" select * top_category order top_name asc; ");     } // end try     catch (pdoexception $e)      {          echo 'there error fetching products.' . $e->getmessage();         exit();      } // end catch  $products = array();  foreach ($result $row) {     $products[] = array('id' => $row['id'],                             'top_name' => $row['top_name']); }  ?>  <div class="sidebar">     <h4 class="sidebar-header">select products</h4>     <form class="nav-search-form">         <input type="search" name="search" placeholder="search products">     </form>     <nav class="sidebar-links">          <ul>             <li><a id="red" href="index.php">new products</a></li>             <?php foreach ($products $product): ?>             <li><a href="#"><?php echo htmlspecialchars($product['top_name']);?></a>              <?php endforeach; ?>                     <ul>                     <li><a href="#"></a></li>                 </ul>             </li>            </ul>     </nav> </div><!-- sidebar --> <div class="main-content"> 

you should combine top_category , bottom_category tables single table, perhaps category. there's no reason these need separated. this:

create table category (   id int primary key,   parent_id int,   name varchar(100)  ); 

for "top" categories, parent_id 0. "bottom" categories, parent_id id of "top" category under falls. in addition cleaning database bit, allows future expansion more 2 layers if desired.

then you'd create recursive function (a function calls itself) loop through , display menu structure, this:

function menus( $parent_id, $pdo ) {     $menu_query = "select * category parent_id = :parent_id";     $menu_result = $pdo->prepare( $menu_query );     $menu_result->execute( array( ":parent_id" => $parent_id ));      $menu = "";     if( $menu_result->rowcount() ) {         $menu .= "<ul>\n";          while( $menu_row = $menu_result->fetch( pdo::fetch_assoc )) {             $menu .= "<li>{$menu_row['name']}\n";             $menu .= menus( $menu_row['id'], $pdo );             $menu .= "</li>\n";         }          $menu .= "</ul>\n";     }      return $menu; } 

then display menu, you'd print results of function:

print menus( 0, $pdo ); 

it'll return structure looks this:

<ul>   <li>prod1     <ul>       <li>subprod1</li>       <li>subprod2</li>       <li>subprod3</li>     </ul>   </li>   <li>prod2     <ul>       <li>subprod4</li>       <li>subprod5</li>     </ul>   </li>   <li>prod3     <ul>       <li>subprod6</li>     </ul>   </li> </ul> 

(indentation added readability, code above not add indentation, that's structure).

here's does. function pulls of top-level entries, loops through each one, calls id of currently-selected entry, pulls of children, , same thing. each time completes layer (i.e. creates structure entries @ given level), returns parent function , concatenates data.

i haven't tested specific code, may contain minor issue here or there, concept solid (i've implemented many times myself).

update (styling)

if want apply styles menu each layer has different set of styles, there 2 ways this. first $level variable function it'd defined this:

function menus( $parent_id, $pdo, $level ) 

start out calling 0 level, within function call $level + 1. way, each successive level called correct value. can create classes based on this, such level-1, define element having class="level-{$level}".

the second wrap entire thing in <div>, , use immediate child selectors reference specific level. div>ul>li first level of <li>, div>ul>li>ul>li second level of <li>, , on.