PHP Developers Network

A community of PHP developers offering assistance, advice, discussion, and friendship.
 
Loading
It is currently Sun Apr 21, 2019 6:09 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 7 posts ] 
Author Message
PostPosted: Fri Sep 03, 2004 11:09 am 
Offline
Forum Regular
User avatar

Joined: Tue Jul 30, 2002 10:21 am
Posts: 852
Location: Trinidad, West Indies
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


Top
 Profile  
 
 Post subject:
PostPosted: Fri Sep 03, 2004 12:13 pm 
Offline
Neighborhood Spidermoddy
User avatar

Joined: Mon Mar 29, 2004 4:24 pm
Posts: 31559
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:
Syntax: [ Download ] [ Hide ]
#
# 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
Syntax: [ Download ] [ Hide ]
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:
Syntax: [ Download ] [ Hide ]
+----------------+--------+----------------------+------------+-----------------------------------+
| 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.

Top
 Profile  
 
 Post subject:
PostPosted: Sun Sep 05, 2004 4:56 pm 
Offline
Site Administrator
User avatar

Joined: Wed Aug 25, 2004 7:54 pm
Posts: 13590
Location: New York, NY, US
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)


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 06, 2004 2:58 am 
Offline
DevNet Resident
User avatar

Joined: Tue Mar 16, 2004 11:03 am
Posts: 1425
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.


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 06, 2004 5:03 am 
Offline
DevNet Master

Joined: Tue Jan 20, 2004 12:11 am
Posts: 4897
Location: Leuven, Belgium
This is the solution i prefer:
http://www.intelligententerprise.com/001020/celko.jhtml?_requestid=2727

And there is even a PHP implementation:
http://pear.php.net/package/DB_NestedSet


Top
 Profile  
 
 Post subject:
PostPosted: Mon Sep 06, 2004 6:45 am 
Offline
Forum Commoner

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


Top
 Profile  
 
PostPosted: Mon Sep 06, 2004 9:47 am 
Offline
Forum Regular
User avatar

Joined: Tue Jul 30, 2002 10:21 am
Posts: 852
Location: Trinidad, West Indies
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

Syntax: [ Download ] [ Hide ]
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 7 posts ] 

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Jump to:  
cron
Powered by phpBB® Forum Software © phpBB Group