selecting max from two columns

i am storing menus and submenus like following

parent_id
child_id
title


in table when i want to add new menu the i need the max id so that i can add the new item.

sometimes parent_id will be bigger and sometimes the child_id will be bigger

so how can can choose the next child_id or parent id

i am storing menus and submenus like following

parent_id
child_id
title


in table when i want to add new menu the i need the max id so that i can add the new item.

sometimes parent_id will be bigger and sometimes the child_id will be bigger

so how can can choose the next child_id or parent id

I don't think it work this way and even if you find a solution will be error prone.
In my opinion you must use two table:
first:

id -> uniq
title
has_child -> boolean value

second:

parent_id
child_id

If you are agreed with this we can implement it, :)

best regards

i have read 2-3 articles on storing hierarchical data no one has used two tables
you may be right ,

but what is the problem with this one


id
parent_id
child_id
title


Parent at highest level will have 0 parent_id and title will store the name of child_id

can u give me the example where error can occur

i have read 2-3 articles on storing hierarchical data no one has used two tables
you may be right ,

how they solve this problem?


but what is the problem with this one

id
parent_id
child_id
title

Parent at highest level will have 0 parent_id and title will store the name of child_id

can u give me the example where error can occur

id parent_id child_id title
0 0 0 menuitem1
1 1 0 submenuitem1

but no element with parent_id 1, and variants, :)

id, parent_id and child_id have the same meaning, "identifier" of a (sub)menuitem, so there are not orthogonale, if you need them, and you need, they must belong to different object to avoid conflict or that means two or more table.

the logic of adding or removing items/subitems become complicated
even if you impose some constraints and make convention releted to
assigning value for child_id and parent_id.

what if you want to add subsubmenus to a submenus?

For me seems very artificial. :)

In fact is a tree, so you must have a list of nodes and a list of arcs between some of the nodes.

what did you use to store menu data, mysql?

best regards

i am using mysql

0 means the parent of all so i will start with

0 as parent with child 1 and then from 1 all other menus

id is the primary key so leave it

0-------------1-------------Main
1-------------2-------------Submenu1
1-------------3-------------Submenu2
2-------------4--------------SUb-submenu
4-------------5-------------sub-sub-sub menu

so we can have any no. of branches

we can use recursion to find the path and display tree

how they solve this problem?






i found this one

select greatest(
( select max(parent_id) from tree )
, ( select max(child_id) from tree )
) as col3

i dont know whether this is the best way to aprroach hierarchical data
but it works



No matter what child_id will always have the highest value ,
was just confused :)

i found this one

select greatest(
( select max(parent_id) from tree )
, ( select max(child_id) from tree )
) as col3

i dont know whether this is the best way to aprroach hierarchical data
but it works

No matter what child_id will always have the highest value ,
was just confused :)

the query is a pain, :) but no care to much because you don't have hundreds of items, :). Is a good example about how not to do, :)

is well that work, let's hope you heave no need to modify it, :)

I'm glad you solve the problem.

PS: if I be you, I'll try to replace asap with something better.

best regards

i am using mysql

0 means the parent of all so i will start with

0 as parent with child 1 and then from 1 all other menus

id is the primary key so leave it

0-------------1-------------Main
1-------------2-------------Submenu1
1-------------3-------------Submenu2
2-------------4--------------SUb-submenu
4-------------5-------------sub-sub-sub menu

so we can have any no. of branches

we can use recursion to find the path and display tree

on each depth level you must do a separate query, I'm wrong?
if I'm not wrong you have a lot of query. :)

Main -+- submenu1 -- subsubmenu -- sub-sub-submenu
\-- submenu2

3 in this case.

if you add a branch to submenu2 you must have a separate query
for it? I can't figure out how else could be done.

best regards

I know there will be more queries but i dont have much items ,

Which method you are suggesting which contains less queries?

I have no particular interest in sticking to my method,
Can u please explain your method , if that contains less quesies i will be happy to use that instead

I know there will be more queries but i dont have much items ,

Which method you are suggesting which contains less queries?

I can say I have a method, :).
I'm interested to find if you or somebody else could see something I can't see.
Next month I must start a project where I need to store a tree in mysql.
I make some research and I know there is no good solution for this problem.

In your case, is ok to have a complex or not so efficient one or more query but only a table and with exceptions of maintenance is not such a problem.
In my case, there are at least few tens of thousand of rows and two or more table don't metter to much.
So to be more clear about my last post, you know any way to retrive all the data from your table and to have a minimum of query?


I have no particular interest in sticking to my method,
nor do I in the two table method, that's why I'm asking, :).

Can u please explain your method , if that contains less quesies i will be happy to use that instead
with two table I think you can do something like( nothing tested, could be wrong):


select a.parent_id, a.child_id, n.title from a, n where n.id = a.child_id order by a.parent_id


and then build a hash array, in php as:

menu = array(parent_id => array(child_id => title))

I know is not valid php syntax, :), I try to explain the structure of the menu for short.:)

I'll try the two table method. I know the thread is allready closed but sun as I have some results from tests, I will post here. That means less then one week. I hope is not too late for you.

best regards

PS: my ex. seems to be wrong, subsubmenu case

i have read article from sitepoint which describes two method to store a tree
i used first method , i also tried second method ehich contains less queries but i could not fully understand that one

http://www.sitepoint.com/article/hierarchical-data-database

you can have a look

the author is sure that the other method is good but i had few problems with that

i have read article from sitepoint which describes two method to store a tree
i used first method , i also tried second method ehich contains less queries but i could not fully understand that one

http://www.sitepoint.com/article/hierarchical-data-database

thank you for link, is good stuff, :), I will study and test the second method to see how can solve my problem.


you can have a look

the author is sure that the other method is good but i had few problems with that

can you give me more details when you have time? this could help me, :)

I'm sure this method work, I'm not so sure if is best for my specific problem, anyway is a candidate, :).

best regards

In the second method

1) First he says we have to number from left to right which is lengthy work

2) If i want to add new menu , this article does not describe that

i don't know what rebuild_tree function does

In the second method

1) First he says we have to number from left to right which is lengthy work

yes, and auto_increment can't be used.


2) If i want to add new menu , this article does not describe that

i don't know what rebuild_tree function does
as far I understand, rebuild_tree recompute the pair of number for each node and must be called with the root node values as argument each time you modify the tree. That means a lot of processing.
I don't understand, yet, the second method of renumbering the node.
Adding a node seams to be simple but it make use of rebuild_tree or the second method of renumbering( see the bottom of the page 3. before disavantages).

PS: this methods are borrowed from functional programming, I don't think that can be implemented to be efficient in php.
best regards

I understood only first method , thats why i used that method ,

I think there will be more queries if we have to display the thousand of parents and their childs. but for particular instance there will be only few menus and sub menus we will be refering to

Although there will thousands of entries in database but we not going to execute all at once.

i am working in a online supermarket which have hundreds of menus and submenus
but at particular time we using only few