Re: [PHP] Better way of doing this? (menu and submenus)
Quoting Ryan A [EMAIL PROTECTED]: Hello, First off, I admit this is not going to be as interesting as the helping someone thread, and hopefully there is not going to be any talk of DDOS or flaming :-p Ok, here goes, I have never done anything _exactly_ like this but since typo 3 has this kind of functionality (and i have never used typo 3 myself) I'm guessing some of you would be able to help me out, or guide me...If i'm wrong, just ignore this thread. Heres what i have; I have a table with the main (parent) categories and another table with the sub-categories (children) which will/is supposed to be compiled into a navigation menu and then be displayed on the left side of each and every page on the site. There are around 15 parents and X children under each parent, children content have upto 10 links on their own pages. I was thinking of passing three valiables on each page for the above: parent_id (int) display_children (1=yes 0=no) active_sub-menu (string or int, depending on the DB structure) and accordingly build and display the menu. Am I on the right track or missing something? Advise, links and suggestions welcome. Thanks! Ryan Ryan, I have run into a similar problem with one of the sites I'm designing. Here's the approach I took. You can see if it fits your needs. I have only 1 database table of menu items. That table is defined as such: menu_item_id name parent_id location By doing it this way, you are able to have a dynamic number of menu items and your menu structure is very flexible. Here's an example of what I have: MID NAMEPARENT_ID LOCATION 1Homenull index.php 2Productsnull products.php 3Information null info.php 10 Profile 1 profile.php 11 Account 1 account.php 20 Hardware2 hardware.php 21 Software2 software.php 210 M$ Word 21 msword.php 211 iLife 21 ilife.php And I think you get the drift. The first 3 are the main menu items. Then Home has 2 children and Products has 2 children. Then Software (under Products) has two children. You can keep getting deeper in the tree structure and not have to worry about adding more tables. Notice that the menu_item_id (MID) actually has some organization to it, but this is completely optional. As the menu items grow, this will probably be more difficult to keep track of. Anyway, hope that helps! ~Philip -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Better way of doing this? (menu and submenus)
Hello, There is an EXCELLENT article at MySQL.com that talks about storing hierarchal data, which is exactly what you want to do. The theory is not MySQL specific, so you can apply it to any database system. It covers two techniques, one of which Philip Thompson covered, and another that I prefer. Well written and a recommended read for everyone. http://dev.mysql.com/tech-resources/articles/hierarchical-data.html -K. Bear -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Better way of doing this? (menu and submenus)
Ryan, I have run into a similar problem with one of the sites I'm designing. Here's the approach I took. You can see if it fits your needs. Here's an example of what I have: MID NAMEPARENT_ID LOCATION 1Homenull index.php 2Productsnull products.php 3Information null info.php 10 Profile 1 profile.php 11 Account 1 account.php 20 Hardware2 hardware.php 21 Software2 software.php 210 M$ Word 21 msword.php 211 iLife 21 ilife.php And I think you get the drift. You can keep getting deeper in the tree structure and not have to worry about adding more tables. Notice that the menu_item_id (MID) actually has some organization to it, but this is completely optional. As the menu items grow, this will probably be more difficult to keep track of. Anyway, hope that helps! @Phillip, Thanks for replying. Because (like you pointed out) the MID can get a little crazy I was thinking of using two tables, one for parent and the other for the children. Also, I forgot to mention in my first post, I am using a p_position (parent position) and c_position (child) for each of the menu items, that way I can do a ORDER BY in my query and it should still look good and changing positions shouldnt be a problem (ask anyone who's married, being flexable in changing positions is VERY important :-D ) Doing an ORDER BY on two tables in also a bit more simple than one (IMHO, feel free to correct me) I really appreciate your input on this as I am in the theory stage while you have actually made something like this, thanks again. @K. Bear - Thanks for the link, I'll check it out as soon as i get a little time. Cheers! Ryan -- - The faulty interface lies between the chair and the keyboard. - Creativity is great, but plagiarism is faster! - Smile, everyone loves a moron. :-) __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Better way of doing this? (menu and submenus)
You could use a recursive function and keep it all in one table. My Table is similar to the one below: ID NAMEPID DISPLAY_ORDER Here's crude recursive function to display this in a list typical of what you used to style a menu. The great thing about this is that you could have unlimited submenus if you wanted ... function displayMenu( $pid ) { global $db; $res = $db-query( SELECT * FROM tblmenu WHERE pid = '.$pid.' ORDER BY display_order ); mysqlErrorCheck( $res ); while( $res-fetchInto( $objData ) ) { $html .= li.$objData['name']; $html .= ul.displayMenu( $objData['id'] )./ul; $html .= /li; } return $html; } echo ul.displayMenu('0')./ul; -Original Message- From: Ryan A [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 21, 2006 10:51 AM To: Philip Thompson; php-general@lists.php.net Subject: Re: [PHP] Better way of doing this? (menu and submenus) Ryan, I have run into a similar problem with one of the sites I'm designing. Here's the approach I took. You can see if it fits your needs. Here's an example of what I have: MID NAMEPARENT_ID LOCATION 1Homenull index.php 2Productsnull products.php 3Information null info.php 10 Profile 1 profile.php 11 Account 1 account.php 20 Hardware2 hardware.php 21 Software2 software.php 210 M$ Word 21 msword.php 211 iLife 21 ilife.php And I think you get the drift. You can keep getting deeper in the tree structure and not have to worry about adding more tables. Notice that the menu_item_id (MID) actually has some organization to it, but this is completely optional. As the menu items grow, this will probably be more difficult to keep track of. Anyway, hope that helps! @Phillip, Thanks for replying. Because (like you pointed out) the MID can get a little crazy I was thinking of using two tables, one for parent and the other for the children. Also, I forgot to mention in my first post, I am using a p_position (parent position) and c_position (child) for each of the menu items, that way I can do a ORDER BY in my query and it should still look good and changing positions shouldnt be a problem (ask anyone who's married, being flexable in changing positions is VERY important :-D ) Doing an ORDER BY on two tables in also a bit more simple than one (IMHO, feel free to correct me) I really appreciate your input on this as I am in the theory stage while you have actually made something like this, thanks again. @K. Bear - Thanks for the link, I'll check it out as soon as i get a little time. Cheers! Ryan -- - The faulty interface lies between the chair and the keyboard. - Creativity is great, but plagiarism is faster! - Smile, everyone loves a moron. :-) __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Better way of doing this? (menu and submenus)
hi Ryan, (btw: if this thread needs spicing up with DoS threats give me a shout and I'll see if can come up with something ;-) with regard to your original question/comments - do not go the route of using 2 tables... you will very quickly find it unwieldy and limiting (e.g. one table for each level in your menu? or if a element is a parent and a child in which table does it belong?) Mark Steudel wrote: You could use a recursive function and keep it all in one table. My Table is similar to the one below: IDNAMEPID DISPLAY_ORDER I would recommend this basic structure as well (there are other ways of specifying a tree structures but I can't recommend them as I'm still learning about them) as it is the most obvious/simplest form of hierarchical data structure. Here's crude recursive function to display this in a list typical of what you used to style a menu. The great thing about this is that you could have unlimited submenus if you wanted ... using Marks example as a starting point - I would suggest spliting the functionality into [atleast] 2/3 parts (trying to make the functionality generic so that it is flexible): 1. grab all the data rows you need in a single query (or as little queries as possible) 2. use the retrieved data to build a mutlidimensional array that reflects/models the structure of the menu you want to output. (this could also be a collection of objects) 3. use the m-dimensional array to generate the html that will be outputted. basically it comes down to minimizing the number of calls to the DB, making it possible to generate different menu structures (e.g. the whole tree just a certain level of the tree, only the siblings of the selected node and all the relevant parent, etc) and giving yourself the ability to using different types of actual output (even if in practice that means different kinds of html, e.g. nested ULs, a table, simple horizonal list, etc) function displayMenu( $pid ) { global $db; $res = $db-query( SELECT * FROM tblmenu WHERE pid = '.$pid.' ORDER BY display_order ); mysqlErrorCheck( $res ); while( $res-fetchInto( $objData ) ) { $html .= li.$objData['name']; $html .= ul.displayMenu( $objData['id'] )./ul; $html .= /li; } return $html; } echo ul.displayMenu('0')./ul; -Original Message- ... -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Better way of doing this? (menu and submenus)
Ryan A mailto:[EMAIL PROTECTED] on Wednesday, June 21, 2006 10:51 AM said: @K. Bear - Thanks for the link, I'll check it out as soon as i get a little time. Read that article. I personally like the Nested Set (also called Modified Preorder Tree Traversal) method. It may at first be a little daunting but once you understand how it works, it all makes sense. Only one table is needed and you can have as many children, grand children, etc. as want/need. Chris. p.s. I found out about it originally here at Sitepoint: http://www.sitepoint.com/article/hierarchical-data-database/2 The diagram at Sitepoint is better than the original article (though the original article seems to be more indepth). -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP] Better way of doing this? (menu and submenus)
Hey Chris, It may at first be a little daunting but once you understand how it works, it all makes sense. Only one table is needed and you can have as many children, grand children, etc. as want/need. Sounds good, I will def have a look at the article, I came here asking for help, will look at the help given :-) Just juggling a few balls in the air right..should be better soon. Thanks! Ryan -- - The faulty interface lies between the chair and the keyboard. - Creativity is great, but plagiarism is faster! - Smile, everyone loves a moron. :-) __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Better way of doing this? (menu and submenus)
If you are using PostgreSQL, there is a 'connectby' function (part of contrib) which will recursively join hierarchical data where you have PID -- ID recursive joins. Oracle also has CONNECT BY built into the SQL language. I do not know if MySQL natively supports anything similar but maybe someone on this list knows the answer to that. Having a CONNECT BY clause avoids having to do round trip selects for each of your sub categories and greatly simplifies queries, IMHO. Dante Mark Steudel wrote: You could use a recursive function and keep it all in one table. My Table is similar to the one below: ID NAMEPID DISPLAY_ORDER Here's crude recursive function to display this in a list typical of what you used to style a menu. The great thing about this is that you could have unlimited submenus if you wanted ... function displayMenu( $pid ) { global $db; $res = $db-query( SELECT * FROM tblmenu WHERE pid = '.$pid.' ORDER BY display_order ); mysqlErrorCheck( $res ); while( $res-fetchInto( $objData ) ) { $html .= li.$objData['name']; $html .= ul.displayMenu( $objData['id'] )./ul; $html .= /li; } return $html; } echo ul.displayMenu('0')./ul; -Original Message- From: Ryan A [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 21, 2006 10:51 AM To: Philip Thompson; php-general@lists.php.net Subject: Re: [PHP] Better way of doing this? (menu and submenus) Ryan, I have run into a similar problem with one of the sites I'm designing. Here's the approach I took. You can see if it fits your needs. Here's an example of what I have: MID NAMEPARENT_ID LOCATION 1Homenull index.php 2Productsnull products.php 3Information null info.php 10 Profile 1 profile.php 11 Account 1 account.php 20 Hardware2 hardware.php 21 Software2 software.php 210 M$ Word 21 msword.php 211 iLife 21 ilife.php And I think you get the drift. You can keep getting deeper in the tree structure and not have to worry about adding more tables. Notice that the menu_item_id (MID) actually has some organization to it, but this is completely optional. As the menu items grow, this will probably be more difficult to keep track of. Anyway, hope that helps! @Phillip, Thanks for replying. Because (like you pointed out) the MID can get a little crazy I was thinking of using two tables, one for parent and the other for the children. Also, I forgot to mention in my first post, I am using a p_position (parent position) and c_position (child) for each of the menu items, that way I can do a ORDER BY in my query and it should still look good and changing positions shouldnt be a problem (ask anyone who's married, being flexable in changing positions is VERY important :-D ) Doing an ORDER BY on two tables in also a bit more simple than one (IMHO, feel free to correct me) I really appreciate your input on this as I am in the theory stage while you have actually made something like this, thanks again. @K. Bear - Thanks for the link, I'll check it out as soon as i get a little time. Cheers! Ryan -- - The faulty interface lies between the chair and the keyboard. - Creativity is great, but plagiarism is faster! - Smile, everyone loves a moron. :-) __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP] Better way of doing this? (menu and submenus)
Hey, If you are using PostgreSQL, there is a 'connectby' function (part of contrib) which will recursively join hierarchical data where you have PID -- ID recursive joins. Oracle also has CONNECT BY built into the SQL language. I do not know if MySQL natively supports anything similar but maybe someone on this list knows the answer to that. Nope, using MySql. I have not checked but I doubt MySql has that in 3.23, pretty sure it does not actually. (Could be wrong) Having a CONNECT BY clause avoids having to do round trip selects for each of your sub categories and greatly simplifies queries, IMHO. Sure sounds like it. Thanks! Ryan -- - The faulty interface lies between the chair and the keyboard. - Creativity is great, but plagiarism is faster! - Smile, everyone loves a moron. :-) __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- PHP General Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php