Showing users in tree structure?

I have a database table called users

This table has two columns (that are important)

uuid, and parentUuid

Here are the rules for the table:

• If a user invites another user, the invited users parentUuid column is equal to the uuid of the inviter.
• If a user was not invited, their parentUuid column is null
• There can be infinite levels of users.

What I want to do is, create a function function counter($levels, $uuid){}

When counter is called (lets assume $levels = 3)

I want the function开发者_高级运维 to return an array that looks like

array(0 => 200, 1 => 600, 2 => 1800);

So the basic idea is I want it to count down, for $levels levels how many users are in the tree under the user.

What is the best way to do this?

I'd extend your users table to include a "level" column, that indicates the depth in level from the root that the user is. That way, when a new user gets added, their level just gets set to the parent's level + 1.

I know this is a bit different of a solution than what you're asking for, but the traversal process for getting the data you want from a table that doesn't store user levels is tricky at best, and the execution time is potentially very long. This seems to be a good example of a situation where the best solution is a slight modification to your schema.

What you're looking at is hierarchical data stored in a tree using the adjacency list model. This is very difficult to scale. Try converting it into a nested set instead. What the difference is and how to structure your data is very well explained here: http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

SAP uses hierarchies almost everywhere this the the structure of the table:

Firstly you need to make sure that your list of entries has a unique identifier UnID even if the bit of information you need is not the UnID. The UnID can be numeric for ease of incrementation.

Then when you add items to your hierarchy table you should store them in this format:

NodeID  
    Note: 0 is always the root nodeID. Any other entry is the UnId of the new entry. It can never be null

ParentID 
    Note: this is the UnID of the parent that you want to attribute to the new entry. It can never be null

ChildId 
    Note: this can be null. It is updated only when this New entry gets to be a parent. 

NextId 
    Note: this is the important one. It determines which child is next in the sequence of children below the parent. The last one in the sequence is always null

Level
    Note: this ensures that a UnID cannot be the parent at more than one level.

You will need some program logic to iron out the attempts to create circular references mentioned in a comment somewhere here, but by implication this means that when a referral takes place you must update three records in your hierarchy table: The new entry, the parent and the last child of the parent (with the nextID) to ensure your pyramid is correct.

There are multiple ways of storing hierarchical data in a database. I suggest using Materialized Path. Failing that, Nested Sets and Adjacency Lists also work.

https://communities.bmc.com/communities/docs/DOC-9902