Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    Senior Coder o0O0o.o0O0o's Avatar
    Join Date
    Jan 2008
    Location
    C:\Windows\System32
    Posts
    1,018
    Thanks
    19
    Thanked 9 Times in 9 Posts

    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

  • #2
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by o0O0o.o0O0o View Post
    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

  • #3
    Senior Coder o0O0o.o0O0o's Avatar
    Join Date
    Jan 2008
    Location
    C:\Windows\System32
    Posts
    1,018
    Thanks
    19
    Thanked 9 Times in 9 Posts
    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

  • #4
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by o0O0o.o0O0o View Post
    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
    Last edited by oesxyl; 01-09-2008 at 04:23 AM.

  • #5
    Senior Coder o0O0o.o0O0o's Avatar
    Join Date
    Jan 2008
    Location
    C:\Windows\System32
    Posts
    1,018
    Thanks
    19
    Thanked 9 Times in 9 Posts
    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

  • #6
    Senior Coder o0O0o.o0O0o's Avatar
    Join Date
    Jan 2008
    Location
    C:\Windows\System32
    Posts
    1,018
    Thanks
    19
    Thanked 9 Times in 9 Posts
    Quote Originally Posted by oesxyl View Post
    how they solve this problem?



    i found this one

    PHP Code:
    select greatest(
             ( 
    select max(parent_idfrom tree )
           , ( 
    select max(child_idfrom 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
    Last edited by o0O0o.o0O0o; 01-09-2008 at 05:09 AM. Reason: Found it

  • #7
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by o0O0o.o0O0o View Post
    i found this one

    PHP Code:
    select greatest(
             ( 
    select max(parent_idfrom tree )
           , ( 
    select max(child_idfrom 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
    Last edited by oesxyl; 01-09-2008 at 05:27 AM.

  • #8
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by o0O0o.o0O0o View Post
    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

  • #9
    Senior Coder o0O0o.o0O0o's Avatar
    Join Date
    Jan 2008
    Location
    C:\Windows\System32
    Posts
    1,018
    Thanks
    19
    Thanked 9 Times in 9 Posts
    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

  • #10
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by o0O0o.o0O0o View Post
    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):

    Code:
    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
    Last edited by oesxyl; 01-09-2008 at 07:21 AM.

  • #11
    Senior Coder o0O0o.o0O0o's Avatar
    Join Date
    Jan 2008
    Location
    C:\Windows\System32
    Posts
    1,018
    Thanks
    19
    Thanked 9 Times in 9 Posts
    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/hie...-data-database

    you can have a look

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

  • #12
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by o0O0o.o0O0o View Post
    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/hie...-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

  • #13
    Senior Coder o0O0o.o0O0o's Avatar
    Join Date
    Jan 2008
    Location
    C:\Windows\System32
    Posts
    1,018
    Thanks
    19
    Thanked 9 Times in 9 Posts
    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

  • #14
    Master Coder
    Join Date
    Dec 2007
    Posts
    6,682
    Thanks
    436
    Thanked 890 Times in 879 Posts
    Quote Originally Posted by o0O0o.o0O0o View Post
    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
    Last edited by oesxyl; 01-10-2008 at 04:10 AM.

  • #15
    Senior Coder o0O0o.o0O0o's Avatar
    Join Date
    Jan 2008
    Location
    C:\Windows\System32
    Posts
    1,018
    Thanks
    19
    Thanked 9 Times in 9 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •