473,327 Members | 2,069 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Menusystem!

Hi All!

I am trying to put up a menusystem and need some tips on how to approach
this...

Example(This is what I want):
Information for private people (I am a main category and have no parents)
About us (I am a subcategory and have one or more parents)
Information for businesses
About us

Etc...

I have this all stored in a mysql-database in the following tables
Category
id(integer, autonumber)
name(text)
language(integer)

Category_relationships
id(integer, autonumber)
childid(integer)
parentid(integer)
language(integer)

Then my problem. I want to build a menusystem like the one in the example
above but I have no clue about how to attack this...

Any ideas?
--
Jan Morten
http://www.sitconsulting.no
Billig datahjelp i Oslo området.

Feb 8 '07 #1
13 1288
Jan Morten Sørensen wrote:
Hi All!

I am trying to put up a menusystem and need some tips on how to approach
this...


1. SELECT all parents

SELECT
Category.id, Category.name
FROM Category JOIN Category_relationships ON Category.id =
Category_relationship.id
WHERE Category_relationsip.parentid = 0;

2. For each parent, do a select again, this time with parentid = the id
Just a suggestion, your db structure can be optimized in some ways.

Hendri Kurniawan
Feb 8 '07 #2

"Jan Morten Sørensen" <jm*@sensewave.comkirjoitti
viestissä:52**************************@news.chello .no...
I have this all stored in a mysql-database in the following tables
Category
id(integer, autonumber)
name(text)
language(integer)
Category_relationships
id(integer, autonumber)
childid(integer)
parentid(integer)
language(integer)
¨
Hello. Like other person already mentioned, there is some database
optimisation need. Parent_id in category table itself would be sufficient,
becuse table CAN refer to itself, also. Not that it would greatly make thing
so much different though. But then in quiries you can do SOMETHING like:

select * from category as thechild, category as theparent where
thechild.parentid = theparent.id (and then other conditions);
(sorry if there's some typos etc., my mysql syntax is in "passive memory"
:-)

and you can get information from both the child and its parent. Just as the
other person mentioned, in menu building you start from oldest parents, who
have 0 as parentid. And then recursively downwards, using the id of the
child as new parentid. You follow every branch to the very end (but since
its recursive work, once you get it right in FUNCTION, you dont worry so
much):

Grandpapa#1 -Papa#1 -Son#1
Grandpapa#1 -Papa#1 -Son#2
Grandpapa#1 -Papa#2 -Son#3
Grandpapa#1 -Papa#2 -Son#4
Grandpapa#2 -Papa#3 -Son#5
Grandpapa#2 -Papa#3 -Son#6
Grandpapa#2 -Papa#4 -Son#7
Grandpapa#2 -Papa#4 -Son#8

Downside is growing number of queries. There are ways, different database
models for this, etc. but it'is also ok to do this, since website menu us
not an excessive list. In the example above, there are 2+4+8=14 categories
and you would get them without trying anything complicated with 7 queries. I
believe that could optimised though.

BTW, after using this model this far, and even after viewing on the surface
alternate models like nested set model, I feel that either xml or an
associative array stored with serialize()-function, would be the most
natural ways to store category strucures, whicha are basicly
multidimensional arrays. But I think that we get used to love database
because of the fun of it (?) and searchability. But if someone just would
make a class that would have some sql-like search features, i'd love to
store caterories in simple xml.
Feb 8 '07 #3
"P Pulkkinen" <pe*************************@POISTATAMA.elisanet.f iskrev i
meddelandet news:39*****************@reader1.news.saunalahti.f i...
>
"Jan Morten Sørensen" <jm*@sensewave.comkirjoitti
viestissä:52**************************@news.chello .no...
I have this all stored in a mysql-database in the following tables
Category
id(integer, autonumber)
name(text)
language(integer)
Category_relationships
id(integer, autonumber)
childid(integer)
parentid(integer)
language(integer)
¨
Hello. Like other person already mentioned, there is some database
optimisation need. Parent_id in category table itself would be sufficient,
becuse table CAN refer to itself, also. Not that it would greatly make
thing
so much different though. But then in quiries you can do SOMETHING like:

select * from category as thechild, category as theparent where
thechild.parentid = theparent.id (and then other conditions);
(sorry if there's some typos etc., my mysql syntax is in "passive memory"
:-)

and you can get information from both the child and its parent. Just as
the
other person mentioned, in menu building you start from oldest parents,
who
have 0 as parentid. And then recursively downwards, using the id of the
child as new parentid. You follow every branch to the very end (but since
its recursive work, once you get it right in FUNCTION, you dont worry so
much):

Grandpapa#1 -Papa#1 -Son#1
Grandpapa#1 -Papa#1 -Son#2
Grandpapa#1 -Papa#2 -Son#3
Grandpapa#1 -Papa#2 -Son#4
Grandpapa#2 -Papa#3 -Son#5
Grandpapa#2 -Papa#3 -Son#6
Grandpapa#2 -Papa#4 -Son#7
Grandpapa#2 -Papa#4 -Son#8

Downside is growing number of queries. There are ways, different database
models for this, etc. but it'is also ok to do this, since website menu us
not an excessive list. In the example above, there are 2+4+8=14 categories
and you would get them without trying anything complicated with 7 queries.
I
believe that could optimised though.

BTW, after using this model this far, and even after viewing on the
surface
alternate models like nested set model, I feel that either xml or an
associative array stored with serialize()-function, would be the most
natural ways to store category strucures, whicha are basicly
multidimensional arrays. But I think that we get used to love database
because of the fun of it (?) and searchability. But if someone just would
make a class that would have some sql-like search features, i'd love to
store caterories in simple xml.
What then if I wanted to have multiple parents maybe even from different
tree levels?
(Is that possible with your solution?)

I wrote a solution to this last nite after posting this question and it was
smashing. I am thinking about publishing it somewhere because I know others
have the same problem as I do.
--
Jan Morten
http://www.sitconsulting.no
Billig datahjelp i Oslo området.

Feb 8 '07 #4
Rik
Jan Morten Sørensen <jm*@sensewave.comwrote:
"P Pulkkinen" <pe*************************@POISTATAMA.elisanet.f iskrev
i
meddelandet news:39*****************@reader1.news.saunalahti.f i...
>>
"Jan Morten Sørensen" <jm*@sensewave.comkirjoitti
viestissä:52**************************@news.chel lo.no...
I have this all stored in a mysql-database in the following tables
Category
id(integer, autonumber)
name(text)
language(integer)
Category_relationships
id(integer, autonumber)
childid(integer)
parentid(integer)
language(integer)
¨
Hello. Like other person already mentioned, there is some database
optimisation need. Parent_id in category table itself would be
sufficient,
becuse table CAN refer to itself, also. Not that it would greatly make
thing
>so much different though. But then in quiries you can do SOMETHING like:

select * from category as thechild, category as theparent where
thechild.parentid = theparent.id (and then other conditions);
(sorry if there's some typos etc., my mysql syntax is in "passive
memory"
:-)
What then if I wanted to have multiple parents maybe even from different
tree levels?
(Is that possible with your solution?)

I wrote a solution to this last nite after posting this question and it
was
smashing. I am thinking about publishing it somewhere because I know
others
have the same problem as I do.
A relational table isn't that uncommon.

I'd trim it down though:

Category
id(integer, autonumber)
name(text)
language(integer)
Category_categories
parent_id (int, index)
child_id (int, index)
(possibly: order (int, index), for a custom order of subcategories in a
categorie)

Keep in mind that 'main' categories (with no parent) should have an entry
in the category_categories with parent_id either 0 (as this will never be
assigned as autonumber, or NULL). Be very, very aware that this could
result in everlasting loop when for instance id 1 is both a parent and a
child of id 2...

Now, the next bit has some unwanted overhead, but is a rather simple way
to do it (untested, might need some debugging):
<?php
$list = array(0 =array('childs' ='array'));
$list_result = mysql_query('SELECT `id`, `name`,`language` FROM
`category`');
while($row = mysql_fetch_assoc($list_result)){
$list[intval($row['id'])] = $list;
}
$relations_result = mysql_query('SELECT `parent_id`, `child_id` FROM
`category_categories` ORDER BY `parent_id`, `order`');
while($row = mysql_fetch_assoc($relations_result)){
if(!isset($list[$row['parent_id']]['childs']))
$list[$row['parent_id']]['childs'] = array();
$list[$row['parent_id']]['childs'][] =& $list[$row['child_id']];
}
function html_nested_lists($array, $safety = 0){
if($safety == 30){
trigger_error('html_nested_lists: nesting to deep, 30 levels allowed');
return false;
}
$safety++;
if(!is_array($array) || empty($array)) return '';
$return = '<ul>';
foreach($array as $item){
$return .= '<li>'.$item['name'];
if(isset($item['childs'])) $return .=
html_nested_lists($item['childs'],$safety);
$return .= '</li>';
}
return $return;
}
echo html_nested_lists($list[0]['childs']);
?>
--
Rik Wasmus
Feb 8 '07 #5

"Jan Morten Sørensen" <jm*@sensewave.comkirjoitti
viestissä:55***************************@news.chell o.no...
What then if I wanted to have multiple parents maybe even from different
tree levels?
(Is that possible with your solution?)

I wrote a solution to this last nite after posting this question and it
was
smashing. I am thinking about publishing it somewhere because I know
others
have the same problem as I do.
I don't get what you mean. Do you mean that a category would have many
parents, like in a real world we have both father and mother? And those
parents were from different "generation".

Maybe I miss something of your point or original idea. But when i think word
"menu", I just think of a everlastingly classical system in web site or
application where you can access child through its parent, following the
path down to level that you want. (Although you just hover over parent names
with mouse, but anyway).
Feb 8 '07 #6
Rik
P Pulkkinen <pe*************************@POISTATAMA.elisanet.f iwrote:
>
"Jan Morten Sørensen" <jm*@sensewave.comkirjoitti
viestissä:55***************************@news.chell o.no...
>What then if I wanted to have multiple parents maybe even from different
tree levels?
(Is that possible with your solution?)

I wrote a solution to this last nite after posting this question and it
was
smashing. I am thinking about publishing it somewhere because I know
others
have the same problem as I do.

I don't get what you mean. Do you mean that a category would have many
parents, like in a real world we have both father and mother? And those
parents were from different "generation".
He means the data is not really a tree, just relations. An odd structure
for a menu, but possible.
--
Rik Wasmus
Feb 8 '07 #7
Rik
Hmmmz, did some testing just in case... here's the debugged code, and man
can one get huge and weird structures if one's not carefull assigning
what's a child of what :-)

<?php
$list = array(0 =array('childs' =array()));
$list_result = mysql_query('SELECT `id`, `name` FROM `category`');
while($row = mysql_fetch_assoc($list_result)){
$list[intval($row['id'])] = $row;
}
$relations_result = mysql_query('SELECT `parent_id`, `child_id` FROM
`category_categories` ORDER BY `parent_id`');
while($row = mysql_fetch_assoc($relations_result)){
if(!isset($list[$row['parent_id']]['childs']))
$list[$row['parent_id']]['childs'] = array();

$list[$row['parent_id']]['childs'][] =& $list[$row['child_id']];
}
function html_nested_lists($array, $safety = 0){
if($safety == 30){
trigger_error('html_nested_lists: nesting to deep, 30 levels allowed');
return '';
}
$safety++;
if(!is_array($array) || empty($array)) return '';
$return = '<ul>';
foreach($array as $item){
$return .= '<li>'.$item['name'];
if(isset($item['childs'])) $return .=
html_nested_lists($item['childs'],$safety);
$return .= '</li>';
}
return $return;
}
echo html_nested_lists($list[0]['childs']);
?>
--
Rik Wasmus
Feb 8 '07 #8
"Rik" <lu************@hotmail.comskrev i meddelandet
news:op***************@misant.kabel.utwente.nl...
P Pulkkinen <pe*************************@POISTATAMA.elisanet.f iwrote:

"Jan Morten Sørensen" <jm*@sensewave.comkirjoitti
viestissä:55***************************@news.chell o.no...
What then if I wanted to have multiple parents maybe even from
different
tree levels?
(Is that possible with your solution?)

I wrote a solution to this last nite after posting this question and it
was
smashing. I am thinking about publishing it somewhere because I know
others
have the same problem as I do.
I don't get what you mean. Do you mean that a category would have many
parents, like in a real world we have both father and mother? And those
parents were from different "generation".

He means the data is not really a tree, just relations. An odd structure
for a menu, but possible.
I'll put out an example menu for you!

main_category
---subcategory1
------subcategory2
---subcategory2
---subcategory3
main_category
---subcategory1
------subcategory2
---------subcategory3
main_category
main_category
etc...

as you can see the subcategories can be present in many variable levels and
have several parents and children...
I have solved this issue like this:

<?php
class MenuElement {
var $id;
var $text;
var $parents;
var $gotParents = false;
var $gotChildren = false;
var $children;
var $num_par = 0;
var $num_chi = 0;
var $level = '';

public function __construct($id, $text) {
$this->id = $id;
$this->text = $text;
}
public function isParent() {
return $this->gotChildren;
}
public function isChild() {
return $this->gotParents;
}
public function setParent($id) {
$this->parents[$this->num_par] = $id;
$this->num_par++;
}
public function loopChildren($element, &$list, &$k) {
for ($i=0;$i<count($this->children);$i++) {
for ($j=0;$j<count($element);$j++) {
if ($this->children[$i] == $element[$j]->getId()) {
if ($element[$j]->getLevel() == '')
$element[$j]->setLevel($this->level+1);
$list[$k] = $element[$j];
$k++;
if ($this->gotChildren) {
$element[$j]->loopChildren($element, $list, $k);
}
}
}
}
}
public function setChild($id) {
$this->children[$this->num_chi] = $id;
$this->num_chi++;
}
public function setParenthood($gotParent) {
$this->gotParents = $gotParent;
}
public function setChildhood($gotChild) {
$this->gotChildren = $gotChild;
}
public function getId() {
return $this->id;
}
public function getLevel() {
return $this->level;
}
public function setLevel($level) {
$this->level = $level;
}
public function getElementText() {
return $this->text;
}
}
// First fetch categories sorted.
$result = getCategories($link, $language);
$result2 = getAllRelationships($link, $language);
$i = 0;
// Then find main_categories
while ($row = mysql_fetch_assoc($result)) {
$gotParents = false;
$gotChildren = false;
$element[$i] = new MenuElement($row['id'], $row['navn']);
if (mysql_num_rows($result2) 0) {
mysql_data_seek($result2, 0);
}
while ($row2 = mysql_fetch_assoc($result2)) {
if ($row['id'] == $row2['childid']) {
if ($row2['parentid'] != 0) {
$gotParents = true;
$element[$i]->setParent($row2['parentid']);
}
}
if ($row['id'] == $row2['parentid']) {
$gotChildren = true;
$element[$i]->setChild($row2['childid']);
}
}
$element[$i]->setParenthood($gotParents);
$element[$i]->setChildhood($gotChildren);
$i++;
}
$j = 0;
$k = 0;
while ($j < $i) {
if (!$element[$j]->isChild()) { // If main_category
$level = 0;
$element[$j]->setLevel($level);
$list[$k] = $element[$j];
$k++;
$element[$j]->loopChildren($element, $list, $k);
}
$j++;
}
echo '<ul>';
for ($i = 0;$i < count($list);$i++) {
echo '<li>' . str_repeat("--", $list[$i]->getLevel()) .
$list[$i]->getElementText();
}
echo '</ul>';
?>

Now you end up with a list of all the elements in a sorted order and with
all variable data present. Only need for two mysql queries...

--
Jan Morten
http://www.sitconsulting.no
Billig datahjelp i Oslo området.
Feb 8 '07 #9
Rik
Jan Morten Sørensen <jm*@sensewave.comwrote:
I'll put out an example menu for you!

main_category
---subcategory1
------subcategory2
---subcategory2
---subcategory3
main_category
---subcategory1
------subcategory2
---------subcategory3
main_category
main_category
In the current setup wouldn't this mean:
main_category
---subcategory1
------subcategory2
--------subcategory3
---subcategory2
------subcategory3
---subcategory3
main_category
---subcategory1
------subcategory2
---------subcategory3

At least. if the number of subcategories stands for an id.
--
Rik Wasmus
Feb 8 '07 #10
Rik wrote:
He means the data is not really a tree, just relations. An odd
structure for a menu, but possible.
Okay. But if any kind of relationship is possible, I'd like to see the
visual representation of the menu.

By the way, this structure could work too imho, if the point was to have
tree, but be able to "recycle" same contents like "About us"-page.

Categories
=======
cat_id
cat_name
parent_id
content_id

Cat_contents
=========
content_id
content_blob

Feb 8 '07 #11
"P Pulkkinen" <pe*************************@POISTATAMA.elisanet.f iskrev i
meddelandet news:iJ*****************@reader1.news.saunalahti.f i...
Rik wrote:
He means the data is not really a tree, just relations. An odd
structure for a menu, but possible.

Okay. But if any kind of relationship is possible, I'd like to see the
visual representation of the menu.

By the way, this structure could work too imho, if the point was to have
tree, but be able to "recycle" same contents like "About us"-page.
That was my idea yeah :) But my little code snippet fixed it for me ;)
Categories
=======
cat_id
cat_name
parent_id
content_id

Cat_contents
=========
content_id
content_blob

Feb 8 '07 #12
..oO(Rik)
>P Pulkkinen <pe*************************@POISTATAMA.elisanet.f iwrote:
>I don't get what you mean. Do you mean that a category would have many
parents, like in a real world we have both father and mother? And those
parents were from different "generation".

He means the data is not really a tree, just relations. An odd structure
for a menu, but possible.
It's not that odd. On a shop site for example it could be possible to
reach a particular subcategory or product on different paths. There
might be cases where a simple tree is not enough.

For some examples here's an interesting paper (PDF) describing different
types of breadcrumbs:

Location, Path & Attribute Breadcrumbs
http://instone.org/breadcrumbs

Micha
Feb 8 '07 #13
Jan Morten Sørensen wrote:
Category
id(integer, autonumber)
name(text)
language(integer)

Category_relationships
id(integer, autonumber)
childid(integer)
parentid(integer)
language(integer)
Here's some example code using the PDO module (if you prefer a different
database module, you'll need to adapt the code). It is thoroughly
unchecked, so probably contains syntax errors. Maybe even logic errors,
but it ought to give you a good place to start.

Note that the structure you describe above allows for circular ancestries.
For example:

A parentof B && B parentof A
OR
A parentof B && B parentof C && C parentof A

I'll assume that you check for circular ancestries before adding a new
menu item, so the database contains no such loops.

<?php

define ('NODE_EXISTS', 1);
define ('NODE_IS_CHILD', 2);
define ('NODE_IS_PARENT', 4);

class MenuNode
{
public $id;
public $name;
public $lang;
public $children;

public function __construct($id, $name, $lang)
{
$this->id = $id;
$this->name = $name;
$this->lang = $lang;
$this->children = array();
}

public function make_link ()
{
return sprintf('<a href="menuselect?id=%s" lang="%s">%s</a>'
, (int)$this->id
, htmlspecialchars($this->lang)
, htmlspecialchars($this->name)
);
}

public function menu_out ($indent='')
{
$retval = "$indent<li>\n";
$retval .= "$indent\t".$this->make_link()."\n";
if (count($this->children) 0)
{
$retval .= "$indent\t<ul>\n";
foreach ($this->children as $C)
$retval .= $C->menu_out("$indent\t\t");
$retval .= "$indent\t</ul>\n";
}
$retval .= "$indent</li>\n";
return $retval;
}
}

function build_tree ($db)
{
$nodelist = array();
$links = array();
$statuses = array();

$q = 'SELECT c.id, c.name, c.language, r.parentid
FROM "Category" c
LEFT JOIN "Category_relationships" r ON c.id=r.childid;';

foreach ($db->query($q) as $data_row)
{
list($I, $N, $L, $P) = $data_row;
$I = (int)$I;
$P = (int)$P; // nulls are now 0.
if (!isset($nodelist[$I]))
$nodelist[$I] = new MenuNode($I, $N, $L);
if ($P>0)
$links[] = array($P, $I);
if (! ($statuses[$I] & NODE_EXISTS) )
$statuses[$I] += NODE_EXISTS;
}

// Database usage is finished here. Can close connection
// if you like.

if (isset($links[0]))
foreach ($links as $link)
{
list($P, $C) = $link;
$nodelist[$P]->children[] &= $C;
if (! ($statuses[$P] & NODE_IS_PARENT) )
$statuses[$P] += NODE_IS_PARENT;
if (! ($statuses[$C] & NODE_IS_CHILD) )
$statuses[$C] += NODE_IS_CHILD;
}

$top_level = array();
foreach ($statuses as $N=>$status)
{
if ( ($status&NODE_EXISTS) && !($status&NODE_IS_CHILD) )
$top_level[] &= $nodelist[$N];
}

if (!isset($top_level[0]))
return FALSE;

if (count($top_level) == 1)
return $top_level[0];

$T = new Node(0, 'Home', 'en'); // default lang is 'en'.
foreach ($top_level as $C)
$T->children[] &= $C;
return $T;
}

$db = new PDO($dsn, $username, $password);
$tree = build_tree($db);
if ($tree !== FALSE)
print "<ul>\n".$tree->menu_out("\t")."</ul>\n";

?>

--
Toby A Inkster BSc (Hons) ARCS
Contact Me ~ http://tobyinkster.co.uk/contact
Geek of ~ HTML/CSS/Javascript/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!
Feb 9 '07 #14

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Michael Dyremo | last post by:
Hello. We have a web-application built in ASP.NET using WebForms and Remoting. When selling this application we always incorporate it into the customers existing web-site. Our latest customer...
1
by: soulcode | last post by:
Hi Guys, Does anyone know of an easy way of doing this... ****************** ASP.NET/VB.NET ********************* With MenuSystem.MenuHeaderStyle.Style .Add("font-family", "verdana")...
7
by: romeorion | last post by:
Hey, I'm new to CSS, working on a website I inherited. I have this css nav code. It's vertical and everyone want it to be horizontal. Can someone point out how to change this? body {...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.