Hello,
I have been searching the internet trying to find an appropriate tutorial on how to build a menu tree using mysql and php. Now i have made one before but what is becoming difficult for me is the creating of the mysql information structure to accomodate for menus and sub menus.
My methodology for doing recursive functions is totaly lackking and i need some guidance on this part. Does anyone know of any good tutorial on this that can help me out
Kendall
Creating Menu Tree using PHP and MYSQL
Moderator: General Moderators
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
Don't know of any tutorials off-hand. This is similar to a breadcrumbing and sitemap set ups. Here's the basics:
the following will generate the tree informationproduces:
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? 
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);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_idCode: 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)
Last edited by feyd on Fri Aug 26, 2005 11:53 pm, edited 1 time in total.
- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
- CoderGoblin
- DevNet Resident
- Posts: 1425
- Joined: Tue Mar 16, 2004 10:03 am
- Location: Aachen, Germany
There are actually two methods of storing trees in a database, the adjacency list model, and the modified preorder tree traversal algorithm. They are both listed with some examples on the following page:
http://www.sitepoint.com/print/1105
For menus I would keep with the adjacency model (should not be too deep and so little recursion). If dealing with a complex multilevel structure I tend to use the other.
http://www.sitepoint.com/print/1105
For menus I would keep with the adjacency model (should not be too deep and so little recursion). If dealing with a complex multilevel structure I tend to use the other.
This is the solution i prefer:
http://www.intelligententerprise.com/00 ... estid=2727
And there is even a PHP implementation:
http://pear.php.net/package/DB_NestedSet
http://www.intelligententerprise.com/00 ... estid=2727
And there is even a PHP implementation:
http://pear.php.net/package/DB_NestedSet
-
jakobdoppler
- Forum Commoner
- Posts: 46
- Joined: Wed May 21, 2003 6:16 pm
As you have mentioned menuTrees. There is a very beautiful JS menu class, that helped me a lot building a tree [1]. Have a look !
Perhaps its of some use, (i.e. I wrote some very ugly recursive function to built a PHP File Explorer, that lists all dirs and files in a root dir) [2]
_yak
[1] http://www.destroydrop.com/javascripts/tree/
[2] http://upload.digidop.net/dirlist.php
Perhaps its of some use, (i.e. I wrote some very ugly recursive function to built a PHP File Explorer, that lists all dirs and files in a root dir) [2]
_yak
[1] http://www.destroydrop.com/javascripts/tree/
[2] http://upload.digidop.net/dirlist.php
- kendall
- Forum Regular
- Posts: 852
- Joined: Tue Jul 30, 2002 10:21 am
- Location: Trinidad, West Indies
- Contact:
Creating Menu Tree using PHP and MYSQL
feyd,
OK...well i don't want to have the same links at different positions i think my menu structure wont be so elaborate
Heres what i did so far
As you can see i have both main menus sub menus and sub sub menus now what i did before was kept the top level menus at 0 which meant that these had no links and was used to just identify the sub menu.
i created my php script to first get all the top level menus = 0 and did an inner join to get all the menus underneath it.
this worked fine but now i have submenus within submenus. I tawt it wud have been a simple procedure. I also want to be able to link the top level menus so wheather or not all menus had a link
Kendall
OK...well i don't want to have the same links at different positions i think my menu structure wont be so elaborate
Heres what i did so far
Code: Select all
Table structure
---MENUID---MENUNAME---PARENTMENU---
---1---menu1---0---
---2---menu2---0---
---3---menu3---1---
---4---menu4---3---i created my php script to first get all the top level menus = 0 and did an inner join to get all the menus underneath it.
this worked fine but now i have submenus within submenus. I tawt it wud have been a simple procedure. I also want to be able to link the top level menus so wheather or not all menus had a link
Kendall