Page 1 of 1

menu system

Posted: Thu May 20, 2004 11:18 am
by vigge89
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?

Posted: Thu May 20, 2004 6:44 pm
by McGruff
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.

Posted: Thu May 20, 2004 7:33 pm
by d3ad1ysp0rk
3 tables?
almost like a forum->board->topic relationship?

Posted: Thu May 20, 2004 8:24 pm
by Scorpius
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.

Posted: Thu May 20, 2004 9:52 pm
by McGruff
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.

Posted: Fri May 21, 2004 3:28 am
by vigge89
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 :)

Posted: Fri May 21, 2004 4:07 am
by vigge89
found this tutorial:
http://www.webclass.ru/eng/Tutorials/My ... cture.html
I'm gonna check it out :)

Posted: Fri May 21, 2004 4:36 am
by vigge89
found out that it only allowed one children, didn't work with more :?

Posted: Fri May 21, 2004 2:49 pm
by McGruff
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).

Posted: Sun May 23, 2004 2:52 am
by timvw
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

Posted: Sun May 23, 2004 4:28 am
by vigge89
I managed to do a two-leveled menu with this code:

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 #########################

?>
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? :) :wink:

Posted: Sun May 23, 2004 5:40 am
by Weirdan
timvw 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 :)
Ahh, the good old preordered tree :)

2 vigge
Algorithm described there would serve you best if INSERTs are rare, but SELECTs are frequent.

Posted: Tue May 25, 2004 3:28 am
by timvw