Php recursive function optimization

Question

My recursive php function looks like that. It generates menu from db table based on parent-child structure

function generateMenu($parent, $level, $menu, $db){
  $q = $db->query("select id, name FROM menu WHERE parent = '$parent' AND showinmenu='$menu'");
  if($level > 0 && $q->num_rows > 0){
    echo "\n<ul>\n";
  }
while($row=$q->fetch_object()){
    echo "<li>";
    echo '<a href="?page=' . $row->id . '">' . $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";
  }
}

It works but i feel that it does bunch of work for nothing. Is there anything that needs to be optimized?

instead of calling the function generateMenu several times, why dont you just call it once with an array of ids and perform the query once. you just have to rewrite the query to get as input an array and crawl from the db all the records for that array — Don Gorgon, Sep 30 '11 at 16:57
@TT13: Check this question: http://stackoverflow.com/questions/4048151/what-are-the-options-for-storing-hierarchical-data-in-a-relational-database for some other approaches to storing hierarchical data in a database. — ypercubeᵀᴹ, Oct 01 '11 at 11:31

score 0 · Answer 1 · answered Sep 30 '11 at 16:59

I would get rid of some of that code like this:

function generateMenu($parent, $level, $menu, $db){

$q = $db->query("select id, name FROM menu WHERE parent = '$parent' AND     showinmenu='$menu'");

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

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

echo "</ul>\n";
  }
}

score 0 · Answer 2 · answered Sep 30 '11 at 23:09

I would save on the many database queries and instead do it in one like this. This will definitely result in better performance:

function generateMenu($parent, $level, $menu, $db){
  $q = $db->query("select parent, id, name FROM menu WHERE showinmenu='$menu'");
  $elements = array();
  while($row=$q->fetch_object()){
    $elements[$row->parent][] = $row;
  }
  _generateMenu($parent, $level, $elements);
}
function _generateMenu($parent, $level, $elements){
  if (!array_key_exists($parent, $elements)){
    return;
  }
  if($level > 0){
    echo "\n<ul>\n";
  }

  foreach($elements[$parent] as $row){
      echo "<li>";
      echo '<a href="?page=' . $row->id . '">' . $row->name . '</a>';
      //display this level's children
      _generateMenu($row->id, $level+1, $elements);
      echo "</li>\n\n";
  }

  if($level > 0){
    echo "</ul>\n";
  }
}

Php recursive function optimization

2 Answers2