SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  1. #1
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Finding Child and Child of Child and so on

    Hi

    Well, I have a situation, where I need to find all the Child, and Child of Child and so on, until no more records found.

    for example, suppose,

    There is a User A,
    under A, there are a user B and C, under B, there is D,E and under C there is F,G and so on,

    So what I want is that all the Child of A, Grand Child, Great Grand child etc etc until there is no more child. Well, another example could be the Family Tree, so in simple words I want all the descendants of A or any other user.

    In the database, I have columns like this :

    ID, ParentID, Name

    So it will stats like this :

    ID, ParentID, Name
    1 0 A
    2 1 B
    3 1 C
    4 2 D
    .
    .
    .
    .
    .

    Please guide me what should be the best approach to do this.

    Thanks
    Zeeshan

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)

  3. #3
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well, thanks for your addition, but I dont think that will help. That has no similarity. In that case, there is a defined depth (4) but in my case, the depth is not defined. It may be 1 or it may be 10 or even more.

  4. #4
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,495
    Mentioned
    161 Post(s)
    Tagged
    4 Thread(s)
    In that case, I guess you'll have to do it with PHP (or whatever language you use).

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by zeeshanhashmi View Post
    That has no similarity.
    oh yes it does

    so what is the maximum number of levels in your data?

    and please don't say that it could be infinite, because that's just lazy

    go take a look at your data, and count the levels

    we'll still be here when you get back

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  6. #6
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    hi r973

    Thanks for your reply, in fact, in my case its really infinite, its like an affiliate thing, and there are types of users.

    For general user the commission will be given to Direct Referrals and 1 more lever. While for out special type users, the commission will be given based on the whole download no matter how deep is that. So I really do not know what the depth could be. :-(

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    okay, you'll have to write some recursive application code

    be prepared for really lousy performance

    alternatively, you could try the nested set model
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    SitePoint Guru
    Join Date
    Jun 2006
    Posts
    638
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I needed the same thing, and didn't find a good solution that was MySQL only.

    There was this one:
    http://www.ibase.ru/devinfo/DBMSTrees/sqltrees.html

    And some info can be found here:
    http://dev.mysql.com/tech-resources/...ical-data.html

    But both are way to complicated for what they need to be.

    So, I came up with two ways to do this, both using recursion:
    #1
    $data = getTree(5, array() );

    function getTree($id, $data) {
    $data[$id]['data'] = DB::selectRow('SELECT * FROM foo WHERE id = ' . $id);
    $rows = DB::select('SELECT * FROM foo WHERE parent_id = ' . $id);
    foreach ($rows as $row) {
    $data[$id]['chield'][] = getTree($row['id'], $data);
    }
    return $data;
    }

    #2
    Same as #1, but you load all the table in an array first.

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by Vali View Post
    But both are way to complicated for what they need to be.
    no kidding

    that's the main reason i never implemented the nested set model (which is what both of those articles are about)

    i hear that it's pretty good on performance provided that you don't update the data, which is hellish difficult
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    if ($zee == "Guru") { $zee--;}
    Join Date
    Nov 2005
    Location
    Karachi - Pakistan
    Posts
    1,134
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yes, I agree !

    Well, I just wrote a PHP function to do this. In fact, it is not good in terms of performance, but I make it better, like I used some query optimization etc etc, for example, all the affiliate ids that has no child are RULED OFF at first step. and so on.

    Thanks a Million people. I agree that generally, these things have certain depth.


Bookmarks

Posting Permissions

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