Don't know of any tutorials off-hand. This is similar to a breadcrumbing and sitemap set ups. Here's the basics:
Code: Select all
#
# Table structure for table `tree_data`
#
DROP TABLE IF EXISTS `tree_data`;
CREATE TABLE `tree_data` (
`tree_id` int(10) unsigned NOT NULL auto_increment,
`tree_parent_id` int(10) unsigned default NULL,
`tree_order` int(10) unsigned default NULL,
`tree_doc_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`tree_id`),
KEY `tree_parent_id` (`tree_parent_id`,`tree_doc_id`)
) TYPE=MyISAM COMMENT='Site tree structure' AUTO_INCREMENT=7 ;
#
# Dumping data for table `tree_data`
#
INSERT INTO `tree_data` (`tree_id`, `tree_parent_id`, `tree_order`, `tree_doc_id`) VALUES (1, NULL, NULL, 1);
INSERT INTO `tree_data` (`tree_id`, `tree_parent_id`, `tree_order`, `tree_doc_id`) VALUES (2, NULL, NULL, 2);
INSERT INTO `tree_data` (`tree_id`, `tree_parent_id`, `tree_order`, `tree_doc_id`) VALUES (3, 1, 1, 3);
INSERT INTO `tree_data` (`tree_id`, `tree_parent_id`, `tree_order`, `tree_doc_id`) VALUES (4, NULL, 2, 4);
INSERT INTO `tree_data` (`tree_id`, `tree_parent_id`, `tree_order`, `tree_doc_id`) VALUES (5, 4, 4, 2);
INSERT INTO `tree_data` (`tree_id`, `tree_parent_id`, `tree_order`, `tree_doc_id`) VALUES (6, 4, 3, 5);
# --------------------------------------------------------
#
# Table structure for table `tree_docs`
#
DROP TABLE IF EXISTS `tree_docs`;
CREATE TABLE `tree_docs` (
`doc_id` int(10) unsigned NOT NULL auto_increment,
`doc_url` varchar(200) default NULL,
`doc_name` varchar(100) NOT NULL default '',
`doc_description` text,
PRIMARY KEY (`doc_id`)
) TYPE=MyISAM COMMENT='documents for site tree' AUTO_INCREMENT=6 ;
#
# Dumping data for table `tree_docs`
#
INSERT INTO `tree_docs` (`doc_id`, `doc_url`, `doc_name`, `doc_description`) VALUES (1, '/foo/bar.php', 'Document 1', 'This is document 1');
INSERT INTO `tree_docs` (`doc_id`, `doc_url`, `doc_name`, `doc_description`) VALUES (2, '/bar/foo.php?page=10', 'Document 2', 'This is document 2');
INSERT INTO `tree_docs` (`doc_id`, `doc_url`, `doc_name`, `doc_description`) VALUES (3, '/doc/foo.php?page=3', 'Document 3', NULL);
INSERT INTO `tree_docs` (`doc_id`, `doc_url`, `doc_name`, `doc_description`) VALUES (4, 'http://google.com', 'Google', 'External site link to: google.com');
INSERT INTO `tree_docs` (`doc_id`, `doc_url`, `doc_name`, `doc_description`) VALUES (5, NULL, 'Document 5', NULL);
the following will generate the tree information
Code: Select all
SELECT tree_parent_id, d.*
FROM tree_data
INNER JOIN tree_docs d ON tree_doc_id = doc_id
ORDER BY tree_parent_id, tree_order, tree_id
produces:
Code: Select all
+----------------+--------+----------------------+------------+-----------------------------------+
| tree_parent_id | doc_id | doc_url | doc_name | doc_description |
+----------------+--------+----------------------+------------+-----------------------------------+
| NULL | 1 | /foo/bar.php | Document 1 | This is document 1 |
| NULL | 2 | /bar/foo.php?page=10 | Document 2 | This is document 2 |
| NULL | 4 | http://google.com | Google | External site link to: google.com |
| 1 | 3 | /doc/foo.php?page=3 | Document 3 | NULL |
| 4 | 5 | NULL | Document 5 | NULL |
| 4 | 2 | /bar/foo.php?page=10 | Document 2 | This is document 2 |
+----------------+--------+----------------------+------------+-----------------------------------+
6 rows in set (0.00 sec)
The reason why there are 2 tables is mostly because you may want to stick the same link and such under two or more different spots. Now, if you don't ever want to do that, you can dump them all into 1 table. doc_url and doc_description have defaults of
NULL. Why? Because you may want to just put some text in there. If the name itself is descriptive enough, there's no need for description, now is there?
