Simple dynamic menu system - database design
Moderator: General Moderators
Simple dynamic menu system - database design
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
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
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?
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
Think multidimensional.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.
Code: Select all
// $array[cat][name] = id
$array['1']['Cars'] = '3';
$array['1']['Bikes'] = '4';
$array['2']['Flowers'] = '5';
$array['2']['GardenTools'] = '6';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.
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.
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.
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.
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.
So, the database build code is loaded when the script is included elsewhere, and then the function is called to return the menu HTML.
and then where you want the menu to appear
Anyhow ... let me know what you think. Suggestions, code changes etc.
Thanks
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();
}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);
}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)
{
$navTree .= ',' . $primary_key;
} else {
$navArrayї$depth] = $primary_key;
$navTree = implode(",", $navArray);
}
$childVal = Show_Menu($primary_key, $targetID, $navTree, ($depth + 1));
if(!empty($childVal) && ($depth + 2) > MENU_DEPTH){
$prefix = EXPAND_MARKER_PRE; $suffix = EXPAND_MARKER_POST;
} else {
unset($prefix); unset($suffix);
}
if($style==0)
{
$retVal .= $spacer . '<a href="test.php?nav=' . $navTree . '">' . $prefix . $title . $suffix . ' - ' . $primary_key . ' - ' . $image . "</a><br>\n";
} else {
$retVal .= $spacer . '<b>' . $prefix . $title . $suffix . "</b><br>\n";
}
$targetArray = explode(",", $targetID);
if(($depth + 1) < MENU_DEPTH || $primary_key == $targetArrayї$depth])
{
$retVal .= $childVal;
}
}
}
}
return($retVal);
}Code: Select all
// Break any POST data into friendly variables
if(count($_POST) > 0){ extract($_POST, EXTR_SKIP); }
include('navigation.php');
if(!isset($nav)){
$menu = Show_Menu();
} else {
$menu = Show_Menu(0, $nav);
}Code: Select all
echo $menu;Thanks
Meaby http://www.dbazine.com/tropashko4.shtml is something you want to read 
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.
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.
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
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
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
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
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);
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);