1

I tried to comment on a past post, but wasn't allowed as not enough rep :(

I am looking for help in turning my modified preorder tree traversal table into a 

<ul> <li>

menu using PHP.

I have seen this:

Getting a modified preorder tree traversal model (nested set) into a <ul>

..and it pretty much answers all I need to know.. except one problem. The items in my table are not all uniquely named, as the same menu item can appear under different parents (in this case, the same department names under different 'types' of document. -So when I run the SQL code:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM menu AS node
CROSS JOIN menu AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.name
ORDER BY node.lft

'Admin', which is an example of a menu name used twice, gets its 'depth' levels added together (3 and 2) and shows as 5.. this of course will muck up its positioning and only show it once when it comes to creating the menu.

Here is my SQL Table:

CREATE TABLE `menu` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(100) NOT NULL,
`lft` int(11) NOT NULL,
`rgt` int(11) NOT NULL,
PRIMARY KEY (`id`),
)

INSERT INTO `menu` (`id`, `name`, `lft`, `rgt`) VALUES
(21, 'root', 1, 30),
(39, 'Forms', 2, 11),
(40, 'Admin', 3, 6),
(56, 'Domcare', 7, 8),
(58, 'MIS', 9, 10),
(59, 'Documents', 12, 21),
(60, 'Residential', 15, 20),
(61, 'Guides', 22, 29),
(62, 'Hardware', 23, 28),
(63, 'Hosting', 24, 25),
(64, 'Installation', 26, 27),
(65, 'Absence', 4, 5),
(67, 'Admin', 13, 14);

Any help on this would be greatly appreciated!

Community
  • 1
  • 1
James Cat Sprocket
  • 21
  • 5

1 Answers1

4

You can GROUP BY the unique ID:

SELECT node.name, (COUNT(parent.name) - 1) AS depth
FROM menu AS node
CROSS JOIN menu AS parent
WHERE node.lft BETWEEN parent.lft AND parent.rgt
GROUP BY node.id
ORDER BY node.lft

See my fiddle here: SQL Fiddle

superphonic
  • 7,954
  • 6
  • 30
  • 63