how to fetch menu from database with its subcategory

Question

This is my menu

              <ul class="sub-menu">
                <li><a href="product-category.html">Product 1</a></li>
                <li><a href="product-category.html">Product 2</a>
                  <ul class="sub-menu">
                    <li><a href="product-category.html">P 21</a></li>
                    <li><a href="product-category.html">P 22</a></li>
                    <li><a href="product-category.html">P 23</a></li>
                    <li><a href="product-category.html">P 24</a></li>
                  </ul>
                </li>
                <li><a href="product-category.html">Product 3</a></li>
                <li><a href="product-category.html">Product 4</a></li>
              </ul>

Product1, Product2, Product3 are categories from category table. P 21, P 22, P 23, P 24 are subcategory of Product 2 in subcategory. All category dont have subcategory. category id saved in subcategory table.

CREATE TABLE IF NOT EXISTS category ( cid int(11) NOT NULL AUTO_INCREMENT, cname varchar(255) NOT NULL, PRIMARY KEY (cid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

CREATE TABLE IF NOT EXISTS subcategory ( sid int(11) NOT NULL AUTO_INCREMENT, sname varchar(255) NOT NULL, cid int(11) NOT NULL, PRIMARY KEY (sid) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

How can i show it in menu by fetctching data from MySql with single query?

It is tricky to help with SQL without knowing the structure of your database beforehand. — chriz

Alexandru Gojinetchi Alexandru Gojinetchi · Accepted Answer · 2015-02-18T12:37:10

please show me architecture of data base.In general it takes 2 cycles with a condition,first for category and second for subcategory. file.php

<ul class="sub-menu">
<?php
 foreach($array_category as $category):?>
   <li><a href="<?php echo $category['link'];?>"><?php echo $category['name'];?></a>
     <?php  if( $exist_subcategory):?>
           <ul>
           <?php foreach($array_subcategory as $subcategory):?>
                 <li><a href="<?php echo $subcategory['link'];?>"><?php echo $subcategory['name'];?></a> 
           <?php endforeach;?>
           </ul>
           <?php endif;?> 
      </li>
      <?php endforeach;?>
    </ul>

The only problem is the condition to enter the correct condition need to know to data base ahritecture.

how to fetch menu from database with its subcategory

2 Answers