2

I have a table having hierarchical menus like

"id" "parent_id" "name"
1 0 menu
2 1 item1
3 2 item1_1
4 1 item2
5 4 item2_1
...
...

and I have 100s of menu items here. In order to get all items in an array I have to write a recursive function like this

getmenu function(parent_id = 1)
{
  $items = mysql_query("SELECT id FROM table WHERE parent_id = " + parent_id);
  while ($item = msyql_Fetch_assoc($items)) {
    ...here I put them in array and call recursive function again to get sub items...
    getmenu($item['id']);
  }   
}

but this executes 100s of queries. Is this the best way to do this, to get hierarchical menus from database? Does this way loads mysql much?

Ergec
  • 11,608
  • 7
  • 52
  • 62

2 Answers2

4
$stmt = "SELECT id, parent_id FROM table";
$items = Array();
$result = mysql_query($stmt);

while ($line = mysql_fetch_assoc($result)) {
    $items[] = $line;
}

$hierarchy = Array();

foreach($items as $item) {
    $parentID = empty($item['parent_id']) ? 0 : $item['parent_id'];

    if(!isset($hierarchy[$parentID])) {
        $hierarchy[$parentID] = Array();
    }

    $hierarchy[$parentID][] = $item;
}

The root level will be $hierarchy[0]. Keys are items ids and values are all direct children.

TheTechGuy
  • 16,560
  • 16
  • 115
  • 136
Serty Oan
  • 1,737
  • 12
  • 19
  • Definitely this is a much much better solution than mine. Thanks a lot. But finally I decided to do something way different. To execute less SQLs I created a cron job that generates all menus in
    • format and dump them in static php files every 5 minutes. Then I just include them and apply css. Since it's not that time critical 5 minutes is enough, even 1 minute is fine too. Server load dropped from 80% to 4% . Nice :)
     – Ergec Jun 25 '10 at 07:26
  • You are welcome. For your solution, this is a kind of "cache management". And indeed it helps a lot :) . You could have use [APC](http://www.php.net/manual/en/book.apc.php) too to store the hierarchy directly in memory. – Serty Oan Jun 25 '10 at 08:47
4

Take a look at Nested Sets if you don't mind a little more complex solution. Nested Sets have a very good SELECT performance and I assume that selecting is more important here.

With the help of Nested Sets, complex hierarchical data can be managed in a very fashionable and elegant way.

Paŭlo Ebermann
  • 73,284
  • 20
  • 146
  • 210
DrColossos
  • 12,656
  • 3
  • 46
  • 67