MySQL DB driven menu generator function

Question

Recently I've written recursive PHP function which generates website navigation based on parent-child structure like this

<ul>
  <li>parent
     <li>child</li>
  </li>
</ul>

Code looks like that

function generateMenu($parent, $level, $db){
  $q = $db->query("select id, name FROM menu WHERE parent = '$parent'");

  if($level > 0 && $q->num_rows > 0) echo "\n<ul>\n";

   while($row=$q->fetch_object()){
      echo "<li>";
      echo '<a href="?page=' .$page. '">' . $row->name . '</a>';
      //display this level's children
      generateMenu($row->id, $level++, $menu, $db);
      echo "</li>\n\n";
    }

    if($level > 0 &&  $q->num_rows > 0) echo "</ul>\n";
}

The piece of code above simply echoes <ul><li> structure (like given above example) from db table.

Now the questions is, how to create navigation menu like on this website?

Please take a look at left sidebar.

http://www.smithsdetection.com/continuous_vapour_sampling.php

Now i think that:

1. First of all we need to echo all parents
2. Function must get current pages id as an input value (for ex. $current)
3. Function must echo til' current pages level

I can't figure out how to modify my function, to get output like on given website. PLease help.

BTW

My db table looks like that

NOTE Please don't post answers about sql injection holes, I've already taken care about them: checking with in_array (if variable listed in column names array) and passing through real_escape.

Answer 1

Assuming the current page id is in the var $current and that $db is an open MySQLi DB connection:

// first get your current page's path back to root:
// $stack will be a stack of menus to show
$stack = array();

// always expand current menu:
$stack[] = $current;

// now starting at $current, we go through the `menu` table adding each parent
// menu id to the $stack until we get to 0:
$i = $current;
while ( $i > 0 ) {
  // get parent of $i
  $query = sprintf('SELECT `parent` FROM `menu` WHERE id=%d LIMIT 1', $i);
  $result = $db->query($query);

  if (!$result) {
    // do error handling here
  }

  $row = $result->fetch_assoc();

  // save parent id into $i...
  $i = $row['parent'];

  // ...and push it onto $stack:
  $stack[] = $i;
}

/**
 * @param int $parent the parent ID of the menu to draw.
 * @param array $stack the stack of ids that need to be expanded
 * @param string $indent string for pretty-printing html
 * @param MySQLi $db Open db connection
 */
function generateMenu($parent, $stack, $indent, $db){

  // $next is the next menu id that needs expanding
  $next = array_pop($stack);

  $query = sprintf('SELECT `id`, `name` FROM `menu` WHERE `parent`=%d', $parent);

  $result = $db->query($query);

  if ( ! $result ) {
    // do error handling here
  }

  if ($result->num_rows > 0) {
    echo "\n$indent<ul>\n";

    while($row = $result->fetch_object()){
      echo "$indent  <li>\n";
      echo "$indent    <a href=\"?page={$row->id}\">{$row->name}</a>\n";

      //display this level's children, if it's the $next menu to need to be drawn:
      if ($row->id == $next)
        generateMenu($next, $stack, "$indent    ", $db);

      echo "$indent  </li>\n\n";
    }
    echo "$indent</ul>\n";
  }
  $result->free();
}

$first = array_pop($stack); // should always be 0
generateMenu($first, $stack, '', $db);

Answer 2

Have a look at this: http://www.ferdychristant.com/blog/archive/DOMM-7QJPM7

You should try to fetch the whole hierarchy with one query to fix performance issue.