menu system

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
vigge89
Forum Regular
Posts: 875
Joined: Wed Jul 30, 2003 3:29 am
Location: Sweden

menu system

Post 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?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
d3ad1ysp0rk
Forum Donator
Posts: 1661
Joined: Mon Oct 20, 2003 8:31 pm
Location: Maine, USA

Post by d3ad1ysp0rk »

3 tables?
almost like a forum->board->topic relationship?
Scorpius
Forum Newbie
Posts: 1
Joined: Thu May 20, 2004 8:24 pm
Location: Scorpion Hole
Contact:

Post 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.
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

Post 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.
User avatar
vigge89
Forum Regular
Posts: 875
Joined: Wed Jul 30, 2003 3:29 am
Location: Sweden

Post 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 :)
User avatar
vigge89
Forum Regular
Posts: 875
Joined: Wed Jul 30, 2003 3:29 am
Location: Sweden

Post by vigge89 »

found this tutorial:
http://www.webclass.ru/eng/Tutorials/My ... cture.html
I'm gonna check it out :)
User avatar
vigge89
Forum Regular
Posts: 875
Joined: Wed Jul 30, 2003 3:29 am
Location: Sweden

Post by vigge89 »

found out that it only allowed one children, didn't work with more :?
McGruff
DevNet Master
Posts: 2893
Joined: Thu Jan 30, 2003 8:26 pm
Location: Glasgow, Scotland

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

Post 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
User avatar
vigge89
Forum Regular
Posts: 875
Joined: Wed Jul 30, 2003 3:29 am
Location: Sweden

Post 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:
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

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

Post by timvw »

Post Reply