menu system
Moderator: General Moderators
menu system
I'm coding a new content management system for my site right now. I'm using an MySQL database to store the different sections/pages. The CMS should have the feature to allow an unlimited number of child-pages from the current page. So i thought that i should use one MySQL table called "contents", which i insert the different sections/pages into.
This is how my table looks like:
pos - Integer - position of section/page in menu
id - String - id for section/page (something like 'start')
name - String - name of sections/page (text to be displayed in menu)
file - String - should the item be linked to an external file?
desc - String - description of section/page
parent - String - the parent sections/pages id
menu - Integer - should the section/page be visible in the menu (1-yes, 0-no)
menu_link - Integer - should the section/page have a link to itself on the menu (if not, it could act like an placeholder (section))?
content - Text - contents of the section/page (BBCode formatted)
Now onto the problem:
I've tried different solutions for showing the different sections/pages in the menu by their parent, but they're messed up, some things are not there, some items are in the wrong place, etc. Does anyone have an idea on how to make an working menu out of this?
This is how my table looks like:
pos - Integer - position of section/page in menu
id - String - id for section/page (something like 'start')
name - String - name of sections/page (text to be displayed in menu)
file - String - should the item be linked to an external file?
desc - String - description of section/page
parent - String - the parent sections/pages id
menu - Integer - should the section/page be visible in the menu (1-yes, 0-no)
menu_link - Integer - should the section/page have a link to itself on the menu (if not, it could act like an placeholder (section))?
content - Text - contents of the section/page (BBCode formatted)
Now onto the problem:
I've tried different solutions for showing the different sections/pages in the menu by their parent, but they're messed up, some things are not there, some items are in the wrong place, etc. Does anyone have an idea on how to make an working menu out of this?
Each page has a one to many relationship with its child pages: hence, child pages should probably have their own table, which looks up their parent page based on parent_id.
Sorry I don't have my bookmarks with me right now but try google for "java database best practices" (O'Reilly) - an excellent article on db design, normalisation etc.
With separate tables, you can find all the childs of a particular page simply by doing a find on parent_id - ORDER BY whatever you are ordering by.
If parents can themselves be childs I'd need to think about that a bit more than I'm afraid I have time for right now.
Sorry I don't have my bookmarks with me right now but try google for "java database best practices" (O'Reilly) - an excellent article on db design, normalisation etc.
With separate tables, you can find all the childs of a particular page simply by doing a find on parent_id - ORDER BY whatever you are ordering by.
If parents can themselves be childs I'd need to think about that a bit more than I'm afraid I have time for right now.
-
d3ad1ysp0rk
- Forum Donator
- Posts: 1661
- Joined: Mon Oct 20, 2003 8:31 pm
- Location: Maine, USA
Ok, what I have made myself and it does work, is something like the following:
Ok, for your categories table, you should have a column for id, name, location, public/private, and if it will contain subpages. Then most of those can be 1 or 0 so you can just make them int.
Then you will need the pages table. This will consist of id, name, curl, and anything else you would like.
Categories:
To explain what some of these are for.
The location could mean if you have a left and right side navigation, 0 could be left and 1 right. public/private would mean if you have other staff members who you would want to see a category so they can edit pages, categories, etc. Having subpages would just mean will it have links in that category, or will it have like a login form.
Pages:
id is obvious, so is name, and for curl, that would mean custom url. If you would want the page to link to something other than ?c=1&p=5 or something like that. Then for the actually mySQL query, you would just SELECT certain rows depending on what the user is, and then you would get the pages in the category while statement.
Ummmm.... that is just the basics of it, but if you would like some help or something just IM me or PM me. IM's will probably be answered a lot faster.
Ok, for your categories table, you should have a column for id, name, location, public/private, and if it will contain subpages. Then most of those can be 1 or 0 so you can just make them int.
Then you will need the pages table. This will consist of id, name, curl, and anything else you would like.
Categories:
To explain what some of these are for.
The location could mean if you have a left and right side navigation, 0 could be left and 1 right. public/private would mean if you have other staff members who you would want to see a category so they can edit pages, categories, etc. Having subpages would just mean will it have links in that category, or will it have like a login form.
Pages:
id is obvious, so is name, and for curl, that would mean custom url. If you would want the page to link to something other than ?c=1&p=5 or something like that. Then for the actually mySQL query, you would just SELECT certain rows depending on what the user is, and then you would get the pages in the category while statement.
Ummmm.... that is just the basics of it, but if you would like some help or something just IM me or PM me. IM's will probably be answered a lot faster.
Maybe one table and a self-join could do the trick. Sorry I don't have much time to really think about this but it is very important to start with a properly normalised db.
Scorpious: of course you can discuss this by pm if you like but if you keep it all in this topic others can possibly learn and comment too.
Scorpious: of course you can discuss this by pm if you like but if you keep it all in this topic others can possibly learn and comment too.
i can't figure how to do it with 2 and 3 tables? from what i could think of that would only allow the admins to create categories, sections and then pages. I'm looking for a way to allow as many childrens as possible.
McGruff: self-join? if you have the time, could you describe it a little more?
I'll check out the google query
Edit: Thanks for the answers BTW
McGruff: self-join? if you have the time, could you describe it a little more?
I'll check out the google query
Edit: Thanks for the answers BTW
If you create a parent_id col in, say, an articles table, any article can be both a parent and a child. To find the children just search (in parent_id col) for a given article id.
I'd really need more time to think about what exactly you are trying to achieve however. Modified pre-order tree traversal might be what you're after (I'll be home tomorrow and will dig out some bookmarks if you can't find some info on google).
I'd really need more time to think about what exactly you are trying to achieve however. Modified pre-order tree traversal might be what you're after (I'll be home tomorrow and will dig out some bookmarks if you can't find some info on google).
there are some solutions to implement such a tree structure in a RDBMS. It is a question that arises from time to time in comp.databases. So i'm pretty sure google knows where to find more info 
http://www.intelligententerprise.com/00 ... estid=2727
http://www.dbazine.com/tropashko4.shtml
http://www.intelligententerprise.com/00 ... estid=2727
http://www.dbazine.com/tropashko4.shtml
I managed to do a two-leveled menu with this code:
I changed my mind, this is what i want. the only thing i've not implented yet is that if a user is on a page that have got sub-page(s), they should be visible under the current page. i'll try to implent it. In the meantime, if anyone finds something that could have been done more efficiently, could you tell me?

Code: Select all
<?php
######################### load menu #########################
################## get top section(s) ################
$q_con1 = mysql_query ("SELECT `id`,`pid`,`pos`,`name`,`desc`,`curl`,`visible`,`link` FROM `contents`
WHERE `pid` = '' AND `visible` = 1 ORDER BY `pos`");
while ($con1 = mysql_fetch_array ($q_con1)) {
echo "
<div class='menu'>";
if (!empty ($con1['curl'])) { $item1['link'] = $con1['curl']; } // if link is set to link to a external file
else { $item1['link'] = "index.php?p={$con1['id']}"; } // normal internal page link
if ($con1['visible'] == 1) {
if ($con1['link'] == 1) echo "<a href='{$item1['link']}' title='{$con1['desc']}'>";
echo "» {$con1['name']}";
if ($con1['link'] == 1) echo "</a>";
}
echo "<br />";
################## get child-page(s) #################
$q_con2 = mysql_query ("SELECT `id`,`pid`,`pos`,`name`,`desc`,`curl`,`visible`,`link` FROM `contents` WHERE `pid` = '{$con1['id']}' ORDER BY `pos`");
while ($con2 = mysql_fetch_array ($q_con2)) {
if (!empty ($con2['curl'])) { $item2['link'] = $con2['curl']; } // if link is set to link to a external file
else { $item2['link'] = "index.php?p={$con2['id']}"; } // normal internal page link
if ($con2['visible'] == 1) {
if ($con2['link'] == 1) echo "<a class='menuitem' href='{$item2['link']}' title='{$con2['desc']}'>";
echo "» {$con2['name']}";
if ($con2['link'] == 1) echo "</a><br />";
}
} ############### /get child-page(s) #################
echo "</div>
";
} ############### /get top section(s) ################
######################## /load menu #########################
?>Ahh, the good old preordered treetimvw wrote:there are some solutions to implement such a tree structure in a RDBMS. It is a question that arises from time to time in comp.databases. So i'm pretty sure google knows where to find more info
2 vigge
Algorithm described there would serve you best if INSERTs are rare, but SELECTs are frequent.