php My menu theory

['Ricardo]

Hello everybody,

I having some trouble making my theory working out the way I want it to work out. I have this script that generates a menu from the database, here is the database table:

CREATE TABLE IF NOT EXISTS `menus` (
  `id` int(16) NOT NULL AUTO_INCREMENT,
  `menu` varchar(16) CHARACTER SET utf8 NOT NULL,
  `name` varchar(32) CHARACTER SET utf8 NOT NULL,
  `link` varchar(128) CHARACTER SET utf8 NOT NULL,
  `weight` tinyint(3) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM;
[/CODE]

I have the following code generating the menu:

[CODE]
<?php

class Menu {
public static function render($menu) {
$db = new MySQL;

$db->query("SELECT name, link ".
"FROM menus ".
"WHERE menu = '%s'".
"ORDER BY weight ASC",
$menu);

$result = $db->result();

$output = '<ul>';

foreach ($result as $row) {
$link = Url::get($row['link']);
$output .= '<li><a href="'.$link.'">'.$row['name'].'</a></li>';
}

$output .= '</ul>';

return $output;
}
}[/CODE]

Now I wanted to accomplish the follow menu:

[CODE]
<ul>
<li><a href="test.php">Test</a></li>
<li><a href="about.php">About</a>
<ul>
<li><a href="test2.php">Test 2</a></li>
<li><a href="test3.php">Test 3</a></li>
</ul>
<li><a href="credits.php">Credits</a>
</ul>
[/CODE]

As you can see I want to accomplish a sub menu. Do I need to add a extra row so that (for example) test2.php has the weight 2.1 so it will be a <ul> under the about.php <li>?

I really could use some help. Thank you.

[HeyRatFans]

If you want to use the weight column in your table to order the menu items like you've suggested (i.e. 1.0 for the first top-level menu, 1.1 for it's first child, etc,) you will need to make it a decimal, otherwise you're only going to be able to store whole numbers (1, 2, 3, ... ) in it and not accomplish what you want.

But yes, that will work.

[Calculator]

Do I need to add a extra row so that (for example) test2.php has the weight 2.1 so it will be a <ul> under the about.php <li>?

If you want to use the weight column in your table to order the menu items like you've suggested (i.e. 1.0 for the first top-level menu, 1.1 for it's first child, etc,) you will need to make it a decimal

That sounds like a terrible idea to me.

• What if you want to have more than 9 items in one submenu? You can't distinguish between 2.1 and 2.10 in decimal representation.
  
• What if you want to have submenus in submenus? How will you represent 2.1.1 or 2.1.1.1?
  
• 2.1 can't be precisely represented in when using a floating point representation for the decimal number, so sooner or later you'll get in trouble when you need very precise weights.
  

A more robust way to go about this would be to use a (optional) parent relationship between menu items. Basically, you add a parent attribute which references the key (id) of the parent menu item. The weight attribute can then just be the local weight in the sub menu and doesn't need to be unique for the whole menu.

I'm not entirely sure if and how you can get them back in a fully ordered list, but maybe that's not necessary. You could build a big associative array in PHP of the whole menu hierarchy while iterating over the results and walk through the tree when writing the output.

[Fourjays Veteran]

A decimal weight will only work if you want just a single level. You can't have 2.1.3 for example (unless you store it as VARCHAR but that isn't a good idea).

The way I usually do it is to assign a "parent_id" column. Process them into a temporary array that stores them hierarchically (you have to do this in reverse so start with the highest parent ID, when you find the parent put the children into it, etc), then iterate through that again to build the HTML.

[HeyRatFans]

That sounds like a terrible idea to me.

What if you want to have more than 9 items in one submenu? You can't distinguish between 2.1 and 2.10 in decimal representation.

• What if you want to have submenus in submenus? How will you represent 2.1.1 or 2.1.1.1?
  
• 2.1 can't be precisely represented in when using a floating point representation for the decimal number, so sooner or later you'll get in trouble when you need very precise weights.
  

A more robust way to go about this would be to use a (optional) parent relationship between menu items. Basically, you add a parent attribute which references the key (id) of the parent menu item. The weight attribute can then just be the local weight in the sub menu and doesn't need to be unique for the whole menu.

I'm not entirely sure if and how you can get them back in a fully ordered list, but maybe that's not necessary. You could build a big associative array in PHP of the whole menu hierarchy while iterating over the results and walk through the tree when writing the output.

2.1 is different to 2.10 if you have 2 decimal places, but yes agreed on the rest of it it's far from ideal.

I too would probably do the parent_id thing that Fourjays also suggested, but then even that has some nasty pitfalls to consider especially as MySQL doesn't do recursive queries, which means fetching results from a table with parent_id is very likely to involve executing the SQL queries until you run out of rows, simply because you don't know how deep your tree goes or how many child nodes each node has without asking the database. If you know how deep you nodes go you can use joins to fetch the structure, but not if it's infinitely deep and you don't know how deep it goes.

The other thing to consider is what happens with sub-node removal. i.e. take a tree structure like A > B > C using a schema with a parent_id. Now what happens if I delete B?