Simple dynamic menu system - database design

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Simple dynamic menu system - database design

Post by Stryks »

Hey all,

I have a page with a side navigation area which I would like to have built dynamically.

Eventually this might be made collapsible, but for the time being, I just need a straightforward two-tier menu.

Category 1
Option 1
Option 2
Category 2
Option 1
Option 2
Option 3

Each menu option is a link to a page which is matched in the database. So clicking option1 would just give a URL like target.php?option=1.

My question is, what is the most efficient way to store this data in the database. I had thought using one table with items with parent id, level, and sort order, but I'm finding gettig the data the way I would like is very difficult.

The only way I can figure to do it is to query the db for the categories, and then loop though them with a separate query for each category which returns all the options underneath that. That seems a bit like overkill for something so simple though.

Any help anyone? Thanks
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

Something like a linked list would work well. Store the link id, name, and parent - set parent = 0 if the link isnt in a sub-section, if not store the parents id.

Then search for all the links with parents as 0. Print the first, search for any links where the parent = link id of previous link.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Thanks for the reply, but it seems that that process still requires one query per category. And if you were to go three layers deep, then that increases the number of queries required even more.

Like .... one query returns all the level 0 entries, and for each value returned another query returns the entries listed underneath that and so on until all category branches are explored.

But doing it like this, well, it seems that you could leave yourself open to large numbers of running queries.

I mean, I'm just learning this stuff, but wouldnt that be a bad thing?

Or is there an easier way to do this?
kettle_drum
DevNet Resident
Posts: 1150
Joined: Sun Jul 20, 2003 9:25 pm
Location: West Yorkshire, England

Post by kettle_drum »

You can select all the items with one query, but then just arange them into an array as you get them from the database.
User avatar
JAM
DevNet Resident
Posts: 2101
Joined: Fri Aug 08, 2003 6:53 pm
Location: Sweden
Contact:

Post by JAM »

kettle_drum wrote:You can select all the items with one query, but then just arange them into an array as you get them from the database.
Think multidimensional.

Code: Select all

// $array[cat][name] = id
$array['1']['Cars'] = '3';
$array['1']['Bikes'] = '4';
$array['2']['Flowers'] = '5';
$array['2']['GardenTools'] = '6';
Just an example of thoughts. Using multidimensional arrays makes it easy to sort and order. They are also (Imho) easy to work with using foreach().
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Thanks for the excellent feedback guys, it's helped a lot.

Will post back some code in a few days when I get a chance to sit down with this again, in case it might help someone else.

Thanks again. :D
User avatar
vigge89
Forum Regular
Posts: 875
Joined: Wed Jul 30, 2003 3:29 am
Location: Sweden

Post by vigge89 »

I did the following with my site:
have one table for groups, and one for pages.
when a group is added, an access_name for it is assigned (used to identify groups), but also a title and anything else needed. when a page is created, it also, except for its own access-name, title and content, got a group-key, which is set to one of the available groups access-name.

For displaying the menu, i first loaded all info from the groups-table, and used a while-statement to loop trough each, outputting the title and other things. Inside of the while-statement, i also outputted all the pages with the group-key set to the current groups access-name.
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

OK. So this is what I am working with so far. You'll have to excuse the code if its a bit messy or sloppy ... I'm still learning as I go, so if there is a better method, feel free to correct me. :)

Ok ... so we load the data into the array.

Code: Select all

// SQL to get menu data.  
$sql = "SELECT Title, MENU_PARENT, MENU_PK, Image, Image, Style FROM menu_structure ORDER BY MENU_PARENT";

//Load a multi dimensional array with the menu data
if(_select_Data($sql) >= 1) {
	while(!(($DB_DATA = _get_Data()) == false)) {
		$db_entryї$DB_DATAї'MENU_PARENT']]ї$DB_DATAї'Title']] = $DB_DATAї'MENU_PK'] . ',' . $DB_DATAї'Image'] . ',' . $DB_DATAї'Style'];
	}
	_End_Data();
}
In case you're wondering, the database calls are functions I made to try to cut down replicating the same code over and over. The database is left open from the database session handler so it's not opened here.

Code: Select all

function _Select_Data($sql) {
  global $result;
  if(!($result = mysql_query($sql))) {
    //my custom error handler was here - throw error
   return false;
  }
  $rec_count = mysql_num_rows($result);
  return $rec_count;
}

function _Get_Data() {
  global $result;
  return mysql_fetch_assoc($result);
}

function _End_Data() {
  global $result;
  mysql_free_result($result);
}
Then comes the hard bit ... displaying the items. I've hard coded the very simple display code, I'm still trying to figure the best way to output the items as formatted HTML (in a table etc). Embed it in this code or send it somewhere else? Feedback is appreciated.

Some of the data (such as image) is displayed as text, just to see if those values are coming through. Obviously, they would be inserted into an IMG tag.

Anyhow, here is the display code.

Code: Select all

define("MENU_DEPTH", 2);						// the number of always visible menu levels
define("SPACER_STEP", "  ");		// the code inserted to space child items
define("EXPAND_MARKER_PRE", "");				// the code added to the start of the item if it has children							
define("EXPAND_MARKER_POST", " >");			// the code added to the end of the item if it has children		


function Show_Menu($parentID = 0, $targetID = 0, $navTree = '0', $depth = 0)
{
	global $db_entry;
	foreach ($db_entry as $parent => $display_info)
	{
		if($parent == $parentID)
		{
			foreach ($display_info as $title => $parameters)
			{
				list($primary_key, $image, $style) = explode(",",$parameters);	
				$spacer = str_repeat(SPACER_STEP, $depth);
				
				$navArray = explode(",",$navTree);
				if(count($navArray) < $depth) 
				&#123;
					$navTree .= ',' . $primary_key;
				&#125; else &#123;
					$navArray&#1111;$depth] = $primary_key;
					$navTree = implode(",", $navArray);
				&#125;
				
				$childVal = Show_Menu($primary_key, $targetID, $navTree, ($depth + 1));
				if(!empty($childVal) && ($depth + 2) > MENU_DEPTH)&#123;
					$prefix = EXPAND_MARKER_PRE; $suffix = EXPAND_MARKER_POST;
				&#125; else &#123;
					unset($prefix); unset($suffix);			
				&#125;
				
				if($style==0)
				&#123;
					$retVal .= $spacer . '<a href="test.php?nav=' . $navTree . '">' . $prefix . $title . $suffix . ' - ' . $primary_key . ' - ' . $image . "</a><br>\n";
				&#125; else &#123;
					$retVal .= $spacer . '<b>' . $prefix . $title . $suffix . "</b><br>\n";
				&#125;

				$targetArray = explode(",", $targetID);
				if(($depth + 1) < MENU_DEPTH || $primary_key == $targetArray&#1111;$depth]) 
				&#123;
					$retVal .= $childVal;
				&#125;
			&#125;
		&#125;
	&#125;
	return($retVal);
&#125;
So, the database build code is loaded when the script is included elsewhere, and then the function is called to return the menu HTML.

Code: Select all

// Break any POST data into friendly variables
	if(count($_POST) > 0)&#123; extract($_POST, EXTR_SKIP); &#125;

	include('navigation.php');
	if(!isset($nav))&#123;
		$menu = Show_Menu();
	&#125; else &#123;
		$menu = Show_Menu(0, $nav);
	&#125;
and then where you want the menu to appear

Code: Select all

echo $menu;
Anyhow ... let me know what you think. Suggestions, code changes etc.

Thanks 8)
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Meaby http://www.dbazine.com/tropashko4.shtml is something you want to read :)
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Ummm ... yeah.

No idea what that means.

I can see what you're trying to suggest, but I cant make heads or tails of that site and so have no real idea how any of it could be applied to a practical problem.

If there is a better way ... maybe some code, maybe some idea of where to start changing or some idea of well .. something ... would be a whole lot more helpful than that site.

Thanks for the feedback though.
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Currently you have modelled your data like the adjanceny list model. That site explains why and how you can improve your model.

Meaby this one is more readable http://www.intelligententerprise.com/00 ... stid=60200

Just see each the employee as a menu, and each boss as a parent_menu
User avatar
Stryks
Forum Regular
Posts: 746
Joined: Wed Jan 14, 2004 5:06 pm

Post by Stryks »

Ok, I think I can understand now what the site was trying to say, and sort of how it works.

I still have two questions however.

How do I add another item to the menu. It seems that adding a branch on the left side (lets say under Bert from the second sites example) would require the entire right branch to be renumbered, as well as the right value of the root to be changed. How can I do this on the fly?

How do I make the returned value apply to a menu display routine? I can see that I can pull values with an indent count or parent list, or something like that, but I am unsure of how to convert those results into the menu display. Any help?

Thanks
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

Ok, here is a general way to add an item.

1-) Determine the numbers for the item. call them a,b
If it is a child of bert (2,3) it would have numbers (3,4)
If it is collega of bert (2,3) it would have numbers (4,5)

2-) The already existing items can be divided in 3 categories
Those with lft < a and rgt < a, they remain the same
Those with lft < a and rgt >= a (it's parents), they have to update b=b+2
Those with lft >= a and rgt >= a , they have to update a=a+2 and b=b+2

3-) now insert the new item

--------------------------------------------------------------------------


function show_menu($data) {
// output some html wrapped around the $data
}

now show all the menustuff
$result = mysql_query("select * from menu order by lft asc");
while ($row = mysql_fetch_assoc($result)) show_menu($row);

or show only the stuff under "menu"
$result = mysql_query("select m1.* from menu as m1, menu as m2 where m1.lft between m2.lft and m2.rgt and m2.name='menu' order by m1.lft asc");
while ($row = mysql_fetch_assoc($result)) show_menu($row);
Post Reply