Creating Menu Tree using PHP and MYSQL

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
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

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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? :)
Last edited by feyd on Fri Aug 26, 2005 11:53 pm, edited 1 time in total.
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Post 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.
(#10850)
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Post 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.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post 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
jakobdoppler
Forum Commoner
Posts: 46
Joined: Wed May 21, 2003 6:16 pm

Post 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
User avatar
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

Post 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
Post Reply