Tree in PHP and MySql

Discussion in 'PHP' started by gaurav_, Aug 28, 2011.

0
  1. #1
    hi,
    Im storing the data in MYSQL in the form of B - Tree

    There is a Root (A)

    when a new user signs up it will automatically join to the place (node) that is available and node is free.

    Suppose
    when (B) joins

    (B) will be added in the left side of (A)

    and when (C) joins

    (C) will automatically join in the right side of (A)

    and when (D) joins

    (D) will be automatically join Left side of (B)

    and when (E) joins

    (E) will automatically join the Right Side of (B)

    and so on...

    please help me how can i traverse my tree to find the correct available node to join a new user.

    my database fields are

    parent_id
    left_node_id
    right_node_id

    please do help me, im not getting answer anywhere :-(
     
    gaurav_, Aug 28, 2011 IP
  2. sarahk

    sarahk iTamer Staff

    Messages:
    28,899
    Likes Received:
    4,555
    Best Answers:
    123
    Trophy Points:
    665
    #2
    I'd probably drop those node fields and just have the parent_id and maybe a "flag" for left and right then just query which records have a child count of 0

    select tableA.id
    from mytable
    where not exists (select parent_id from mytable as tA, mytable as tB where tA.parent_id = tB.parent_id and tA.flag = 'left' and tB.flag='right' and tA.parent_id = mytable.id)

    that way you'd find the ids of any record that does not have the two child records. 1 more query to find out what it does have and you have your flag as well.

    add an order onto the query to ensure you have the oldest record to attach a child to.
     
    sarahk, Aug 28, 2011 IP
  3. gaurav_

    gaurav_ Member

    Messages:
    54
    Likes Received:
    0
    Best Answers:
    0
    Trophy Points:
    41
    #3
    Hi,
    Sorry for my late reply,

    actually i have to insert the record like this into Db

    treehg.jpg


    please help me with some logic :-(
     
    gaurav_, Aug 29, 2011 IP
  4. lukeg32

    lukeg32 Peon

    Messages:
    645
    Likes Received:
    19
    Best Answers:
    1
    Trophy Points:
    0
    #4
    Hi,

    The tree you are doing is in effect broken;

    If someone is referred anyone, they should be put at a level immediately below the parent.

    I.E In your example, A as the root;

    If A refers B, then B will be the level under A.
    If A refers C, then C will be the level under A.

    If A then refers D, they will ALSO be on the level under A
    However, if B refers D then they will appear below A, and B in the tree

    You might want to rethink your structure before proceeding.
     
    lukeg32, Aug 30, 2011 IP