SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Best way for recursive function

    Fellow Sitepointers,

    I am developing a project that allows the assignment of a "Manager/user" relationship.

    Each user can only have one manager, but one manager can have multiple users. Each user may also have users...

    Here is a diagram to depict what I mean.

    Code:
    --+ Big Wig manager (1)
    -----+ Manager A (2)
    ---------+ Worker 1 (4)
    ---------+ Worker 2 (5)
    -----+ Manager B (3)
    ---------+ Worker 3 (6)
    ---------+ Worker 4 (7)
    The table structure is simple, the userID from my user table is used in the Managers table. The managers table has 2 columns, ManagerID, and UserID

    from the chart above, the table would look like this...
    Code:
    ManagerID | UserID |
    1 | 2
    1 | 3
    2 | 4
    2 | 5
    3 | 6
    3 | 7
    If I run a simple query, I can retrieve the direct users that belong to the given ID. SELECT userID FROM Mangers WHERE managerID = 1; Returns 2 and 3.

    Whats the easiest way to find "All Users Under [given ID]". Say I want to find all users that work for Big Wig Manager. I want it to return 2,3,4,5,6 and 7?

    I'm thinking that the best possible way is to use a recursive function to first lookup the users for the top manager ID, and have it get all the users for that manager. and recursively get the userID where the retrieved userID is the Manager.

    The only problem with doing this recursive function its depending on the organization, the query would be run possibly hundereds of times with minimal results.

    Any Suggestions?

  2. #2
    SitePoint Enthusiast
    Join Date
    Aug 2007
    Posts
    56
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I doubt you're interested in starting over from scratch. But the following links are what you are trying to accomplish.

    http://www.sitepoint.com/forums/showthread.php?t=502802
    http://dev.mysql.com/tech-resources/...ical-data.html
    http://www.sitepoint.com/article/hie...-data-database (second page but you may wish to use the example from the first page)

    Last edited by gcbdm; Oct 18, 2007 at 10:29.

  3. #3
    SitePoint Addict tbakerisageek's Avatar
    Join Date
    Sep 2006
    Posts
    213
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by gcbdm View Post
    I doubt you're interested in starting over from scratch. But the following links are what you are trying to accomplish.

    http://www.sitepoint.com/forums/showthread.php?t=502802
    http://dev.mysql.com/tech-resources/...ical-data.html
    http://www.sitepoint.com/article/hie...-data-database (second page but you may wish to use the example from the first page)

    Yeah, I've seen the Nested method before. I don't like it for some reason. I am much better at handling the data in PHP than I am in MySQL. I'm thinking that I may want to limit the Depth to something small but still usable like 4 or 5. I don't think that the hierarchy of a company (at least one that would be using the system I am building) would ever achieve this level...

    I'm considering a different format of displaying/interacting with the data that I'm using. Something that would only handle one level at a time...


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
  •