building a navigation with nested sets, from the selected node up

I have the following tree structure using nested sets with lft & rgt values.

node
    node
        node
    node
        node (selected)
        node
node
node
    node

I'd like to build a navigation so that the tree is expanded, only to the path of the selected node, and non relevant nodes are collapsed/hidden.

Using the above method, the tree would be outputted as follows:

node
    node
    node
        node (selected)
        node
node
node

is this possible using php/mysql? If any sql gurus can help build a query I'd be most appreciative.?

I don't mind if I need an extra query per level, its probably only going 开发者_开发知识库to be 4 or 5 levels deep at most...

Overview of the nodes table:

--
-- Table structure for table `exp_node_tree_1`
--

CREATE TABLE `exp_node_tree_1` (
  `node_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
  `lft` mediumint(8) unsigned DEFAULT NULL,
  `rgt` mediumint(8) unsigned DEFAULT NULL,
  `moved` tinyint(1) NOT NULL,
  `label` varchar(255) DEFAULT NULL,
  `entry_id` int(10) DEFAULT NULL,
  `template_path` varchar(255) DEFAULT NULL,
  `custom_url` varchar(250) DEFAULT NULL,
  `extra` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`node_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 AUTO_INCREMENT=18 ;

--
-- Dumping data for table `exp_node_tree_1`
--

INSERT INTO `exp_node_tree_1` VALUES(1, 1, 12, 0, 'Home', 1, '0', '/', '');
INSERT INTO `exp_node_tree_1` VALUES(5, 10, 11, 0, 'About Us', 2, '4', '', '');
INSERT INTO `exp_node_tree_1` VALUES(6, 6, 9, 0, 'Team', 3, '5', '', '');
INSERT INTO `exp_node_tree_1` VALUES(7, 3, 4, 0, 'Contact Us', 4, '4', '', '');
INSERT INTO `exp_node_tree_1` VALUES(8, 7, 8, 0, 'Awards', 5, '5', '', '');
INSERT INTO `exp_node_tree_1` VALUES(10, 2, 5, 0, 'New Page', 6, '4', '', '');

Thank you!

Your sample data makes it hard, given that you don't have any sibling-nodes that aren't direct children of the root node, but I'll work with what's there :)

I'd think you'll need two SQL calls - one to grab all the nodes that contain the left/right values of your selected node, and one to use the 'parent' left/right values from the previous call to grab the siblings of your selected node

e.g. Grab all the nodes that contain the left/right values of your target node

SELECT e.* FROM exp_node_tree_1 as e, (SELECT lft, rgt FROM exp_node_tree_1 WHERE node_id = ?) AS tbl WHERE (e.lft < tbl.lft) and (e.rgt > tbl.rgt) ORDER BY e.lft ASC

Replace the ? with the node_id of the selected node. This returns all ancestors of your selected node, starting at the top-level and working down to the direct parent of your selected node

The second query (siblings) can be done two ways, depending if you want to use the lft / rgt values already returned (e.g. grabbing the values from PHP), or whether you want to do the heavy lifting in SQL. Doing it in SQL means the query is more complex, but you don't need any data other than the selected node's id

Using PHP values from the selected node's parents (returned in the previous query)

SELECT * FROM `exp_node_tree_1` WHERE (lft > ?) AND (rgt < ?) ORDER BY lft ASC

Replace the first ? with the lft value of the parent, and the second ? with the rgt value of the parent

The second method only uses the node_id of the selected node

select s.* FROM exp_node_tree_1 as s, (SELECT e.lft, e.rgt FROM exp_node_tree_1 as e, (SELECT lft, rgt FROM exp_node_tree_1 WHERE node_id = ?) AS tbl WHERE (e.lft < tbl.lft) and (e.rgt > tbl.rgt) ORDER BY e.lft DESC LIMIT 1) as parent WHERE (s.lft > parent.lft) AND (s.rgt < parent.rgt) ORDER BY s.lft ASC

Like I said - a bit more complex. Replace the ? with the selected node's node_id

Hope this helps!