Dynamic Menu in PHP

Question

i have following table of pages;

+---------+------------------+-----------------------+-----------+-------+
| page_id | page_name        | page_link             | parent_id | order |
+---------+------------------+-----------------------+-----------+-------+
|       1 | Main Units       |                       |         0 |     1 |
|       2 | Add Project      | add_project.php       |         1 |     2 |
|       3 | Add Activity     | add_activity.php      |         1 |     3 |
|       4 | Add PO Reference | add_porefrence.php    |         1 |     4 |
|       5 | Department       | department.php        |         0 |     5 |
|       6 | Users            | users.php             |         0 |     6 |
|       7 | Category         | category.php          |         0 |     7 |
|       8 | Item             | item.php              |         0 |     8 |
|       9 | Units            | units.php             |         0 |     9 |
|      10 | Stock            |                       |         0 |    10 |
|      11 | Stock In         | add_inventory.php     |        10 |    11 |
|      12 | Stock Out        | inventory_issue.php   |        10 |    12 |
|      13 | Stock List       | stock_list.php        |        10 |    13 |
|      14 | Items in Stock   | item_list.php         |        10 |    14 |
|      15 | Reports          |                       |         0 |    15 |
|      16 | Stock IN         | stock_in_report.php   |        15 |    16 |
|      17 | Stock Out        | stock_out_report.php  |        15 |    17 |
|      18 | Stock Card       | stock_card_report.php |        15 |    18 |
+---------+------------------+-----------------------+-----------+-------+

and this code to create dropdown menu.

<div class="menu">
  <span>
    <ul id="nav">
      <li><a href="#">Main Units</a>
        <div class="subs">
          <div>
            <ul>
              <li><a href="add_project.php" style="text-decoration:none;"><h3>Add Projects</h3></a></li>
              <li><a href="add_activity.php" style="text-decoration:none;"><h3>Add Activity</h3></a></li>
              <li><a href="add_porefrence.php" style="text-decoration:none;"><h3>Add PO. Reference</h3></a></li>
            </ul>
          </div>
        </div>
      </li>
      <li><a href="department.php">Departments</a></li>
</ul>
</span>
</div>

i want to create dynamic menu from database, i write this code but unable to find when a sub-menu finished loop..

  <div class="menu">
                        <span>
                          <ul id="nav">
                            <?php

                                                      ?>
                    <?php while($rows=mysql_fetch_array($SQL_FETCH_PAGES)){

                      if ($rows['page_link'] != '' & $rows['parent_id'] == 0){
                        ?>

                        <li><a href="<?php echo $rows['page_link']; ?>"><?php echo $rows['page_name']; ?></a></li>
                        <?php
                      }
                      ?>
                      else if ($rows['page_link'] == '' & $rows['parent_id'] == 0){
                      ?>
                      <li><a href="<?php echo $rows['page_link']; ?>"><?php echo $rows['page_name']; ?></a>
                        <div class="subs">
                          <div>
                            <ul>
                              <?php
                            }
                            if($rows['page_link'] != '' & $rows['parent_id'] != 0){
                            <li><a href="<?php echo $rows['page_link']; ?>"><?php echo $rows['page_name']; ?></a></li>
                          }
                          >?
                            </ul>
                          </div>
                        </div>
                      </li>
                          <?php  }
                            ?>
                              <li><a href="logoff.php">Logout</a></li>
                            </ul>
                          </span>
                        </div>

the query to fetch select * from pages order by order;

Answer 1

May be this code can help you.Just use this as it is just replace your Database Table name.

<?php $mainMenu = mysql_query("SELECT * FROM YourTable WHERE parent_id=0 ORDER BY order"); ?>
<div class="menu">
  <span>
    <ul id="nav">
    <?php
        while($mainRes = mysql_fetch_assoc($mainMenu))
        {
    ?>
      <li><a href="#"><?php echo $mainRes['page_name']; ?></a>
        <div class="subs">
          <div>
            <ul>
            <?php
                $subMenu = mysql_query("SELECT * FROM Table WHERE parent_id='".$mainRes['page_id']."' ORDER BY order");
                while($subRes = mysql_fetch_assoc($subMenu))
                {
            ?>      
              <li><a href="<?php echo $subRes['page_link']; ?>" style="text-decoration:none;"><h3><?php echo $subRes['page_name']; ?></h3></a></li>
            <?php } ?>
            </ul>
          </div>
        </div>
      </li>
        <?php } ?>  
</ul>
</span>
</div>

Answer 2

Do not ever do mysql queries inside HTML code! If you don't use any MVC-like structure then do it on the top of the file and cache in some local variable.

However, you're not checking for parenting.

if($rows['page_link'] != '' & $rows['parent_id'] != 0){

You should check your parent_id for dependencies and the best way is to do it by recursion. But if your dropdown menu won't have more than 2 levels then this code should work fine for some time.

<?php
foreach ($rows as $row) {
    if ($row['page_link'] != '' && $row['parent_id'] == 0) {
?>
    <li><a href="<?php echo $row['page_link']; ?>"><?php echo $row['page_name']; ?></a></li>
<?php
    }
    else if ($row['page_link'] == '' && $row['parent_id'] == 0) {
?>
    <li><a href="#"><?php echo $row['page_name']; ?></a>
    <div class="subs">
        <div>
            <ul>
                <?php
                    foreach ($rows as $children) {
                        if ($children['parent_id'] == $row['id']) {
                            ?>
                                <li><a href="<?php echo $children['page_link']; ?>"><?php echo $children['page_name']; ?></a></li>
                            <?php
                        }
                    }
                ?>
            </ul>
        </div>
    </div>
</li>
<?php
    }
?>

But the goal is to build menu by recursion, not the way like above. There are hundrets of examples how to do it.