Page 1 of 1

Creating Menu Tree using PHP and MYSQL

Posted: Fri Sep 03, 2004 11:09 am
by kendall
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

Posted: Fri Sep 03, 2004 12:13 pm
by feyd
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? :)

Posted: Sun Sep 05, 2004 4:56 pm
by Christopher
I agree with feyd. This is the basic concept for building tree menus.

If you have problems with recursion you can sometimes change how the data is sorted, then track $depth and check for when the parent changes.

Posted: Mon Sep 06, 2004 2:58 am
by CoderGoblin
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.

Posted: Mon Sep 06, 2004 5:03 am
by timvw
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

Posted: Mon Sep 06, 2004 6:45 am
by jakobdoppler
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

Creating Menu Tree using PHP and MYSQL

Posted: Mon Sep 06, 2004 9:47 am
by kendall
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

Code: Select all

Table structure
---MENUID---MENUNAME---PARENTMENU---
---1---menu1---0---
---2---menu2---0---
---3---menu3---1---
---4---menu4---3---
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