CODEWITHSUNDEEP
mysql
Poni
Poni

Reputation: 11317

mysql query all sub-accounts and their sub-accounts and so on

I have one table like this

account_id (INT)
inviter_id (INT)

Now, logged in user with account id 5 wants to see the logs that related to all of his invitees, directly and indirectly, as this table may represent nested hierarchy of unlimited depth.

How would I do that with MySQL?
I accept a solution in PHP/C/C++/C# (:

Actually I've looked for it here and at google and couldn't find anything for that particular case, as everyone try to have nested menus at their website and ask about it.

I've been thinking about simply querying for all accounts in the database (there are about a few hundreds) and from there simply build a tree, or something, but then again I have to stay synced with the database.
So to stay synced with the database I've thought about querying the COUNT() of the accounts in the table, but, what if I need to change an inviter'd (maybe deleting one)?

Anyway, I could appply the rule of "not changing inviter" - if I'd do that, then COUNT() would work I think - is there any better approach to that kind of issue?

Upvotes: 0

Views: 796

Answers (2)

Jon Black
Jon Black

Reputation: 16559

you can take a look at my answer(s) here if you like:

Print hierachical data in a parent child form unordered list php?

or here:

Mysql Recursive Stored Procedure...Limit 0 reached...can't change the max_sp_recursion_depth variable

or here:

Multi-tiered Comment Replies: Display and Storage

or here:

MySQL Hierarchical Structure Data Extraction

hope it helps

Upvotes: 1

Caner
Caner

Reputation: 59168

Check this out, this might give you some ideas: http://www.ideashower.com/our_solutions/create-a-parent-child-array-structure-in-one-pass/

Upvotes: 0

Related Questions