Hi all,

The situation:
What I'm trying to make is a permission system for a website.

The question:
What I am trying to do, and hope to be able to do with a view of some sort, is to create a virtual column or table (view, or anything else that might help me), that gives me a row of values based on the initial value that I put in the select statement.

The table:
CREATE TABLE mgap_ranks(id INT(5) NOT NULL AUTO_INCREMENT, name VARCHAR(265) NOT NULL, inherit VARCHAR(265), PRIMARY KEY (id), UNIQUE (name))

The data:
INSERT INTO mgap_ranks(name, inherit) VALUES('Guest', null), ('Admin', 'Guest'), ('Admin', 'Guest')

The version:
5.5.10


Example:
The following statement:
select id from mgap_ranks where name = 'Admin' or inherit = 'Admin'
Returns the values 1 and 2 (1 being Guest, 2 being Admin)

Second example, and the question:
TRUNCATE TABLE mgap_ranks
INSERT INTO mgap_ranks(name, inherit) VALUES('r1', null), ('r2', 'r1'), ('r3', 'r2'), ('r4', 'r3'), ('r5', 'r4'), ('r6', 'r3'), ('r7', 'r6')

What I want, is a statement or view or something, that returns the ID of the values that fall under the inheritance of the rank provided.

If the value I provide is 'r2', I want the ID's of 'r2' and 'r1'
If the value I provide is 'r5', I want the ID's of 'r5', 'r4', 'r3', 'r2' and 'r1'
If the value I provide is 'r7', I want the ID's of 'r7', 'r6', 'r3', 'r2' and 'r1'


The me acting sad part:
Could anyone please help me with this? It is an issue I have spend some time on already, and one that I would like to solve soon before I have to make loops in my programming code. If the database can do it, it looks a lot better!


Thanks in front
Thank you already, even if you just tried it and didn't came up with a solution!


With kind regards,
"Master-Guy" Marcel

how many levels? the examples you've shown have at most 5 levels, and you can accomplish what you want with 5 self-joins (if there are 6 levels, you'd need 6 self-joins, et cetera)

if you have more than, say, 15 levels, then you should consider implementing the nested set model

The idea is to have it dynamically deternime it's own amount of levels.
I've looked through the nested set model, but from what I understand of it, the use is not what I'm looking for here, since I only want a single parent, and a dynamic tree. Reorganizing the tree would be too much of an effort.

Since the tree is dynamic, I can't say how many levels the structure would contain, so I cannot use a fixed number of self-joins.

give the nested set model a try, it really will do what you're looking for

(yes, a single parent -- it's a hierarchy model, after all)