New idea for hierarchy in MySQL

[wajeeh km]

Hello every one,
I search the web for retrieving all parents of a specific record in MySQL, I thought there is a way like in Oracle.
But I discover that MySQL does not support that, And a lot of people asking for the same thing.
So I develop a new idea to solve this issue and I think it will be nice.
The idea is this:

lets assume we have a table named `category` with 2 columns `id`, `category_name`, and for my new idea I will add additional column named `hierarchy`.
The new column `hierarchy` will contains a reference to all parents of this record.

+----+---------------+-----------------+
| id | category_name | hierarchy |
+----+---------------+-----------------+
| 1 | cars | 1 |
+----+---------------+-----------------+
| 2 | real estate | 2 |
+----+---------------+-----------------+
| 3 | clothes | 3 |
+----+---------------+-----------------+
| 4 | bmw | 1-4 |
+----+---------------+-----------------+
| 5 | audi | 1-5 |
+----+---------------+-----------------+
| 6 | 100 | 1-4-6 |
+----+---------------+-----------------+
| 7 | 80 | 1-4-7 |
+----+---------------+-----------------+
| 8 | A4 | 1-4-8 |
+----+---------------+-----------------+
| 9 | QUATRO | 1-4-8-9 |
+----+---------------+-----------------+
| 10 | TDI | 1-4-8-10 |
+----+---------------+-----------------+
| 11 | Black | 1-4-8-9-11 |
+----+---------------+-----------------+
| 12 | White | 1-4-8-9-12 |
+----+---------------+-----------------+
| 13 | 2 doors | 1-4-8-9-11-13 |
+----+---------------+-----------------+
| 14 | 5 doors | 1-4-8-9-11-14 |
+----+---------------+-----------------+

if you look at the table you will notice that every record has an link to its parents, not only the direct one, But also all of parents.
And for that job I made some modification in insert statement:

Insert into table_name (category_name, hierarchy) values ('new_name', (concat(parent_hierarch, '-', (SELECT Auto_increment FROM information_schema.tables WHERE table_name='table_name'))))

(assume id Auto_Increment)

Now lets make your desired queries:

1- all sub categories of cars:

select * from table_name where hierarchy like '1-%'
2- if you need all parent of BLACK you simply type:

select * from table_name where hierarchy = '1-4-8-9' or hierarchy = '1-4-8' or hierarchy = '1-4' or hierarchy = '1'
(you can build that query from php, java ...., splitting hierarchy field at '-' char)

3- To see all categories, with level and direct parent:

select *, SUBSTR(hierarchy, 1, (LENGTH(hierarchy) - LENGTH(id) - 1)) as parent, LENGTH(hierarchy) - LENGTH(REPLACE(hierarchy, '-', '')) as level From table_name

+----+---------------+-----------------+-----------+--------+
| id | category name | hierarchy | parent | level |
+----+---------------+-----------------+-----------+--------+
| 1 | cars | 1 | | 0 |
+----+---------------+-----------------+-----------+--------+
| 2 | real estate | 2 | | 0 |
+----+---------------+-----------------+-----------+--------+
| 3 | clothes | 3 | | 0 |
+----+---------------+-----------------+-----------+--------+
| 4 | bmw | 1-4 | 1 | 1 |
+----+---------------+-----------------+-----------+--------+
| 5 | audi | 1-5 | 1 | 1 |
+----+---------------+-----------------+-----------+--------+
| 6 | 100 | 1-4-6 | 1-4 | 2 |
+----+---------------+-----------------+-----------+--------+
| 7 | 80 | 1-4-7 | 1-4 | 2 |
+----+---------------+-----------------+-----------+--------+
| 8 | A4 | 1-4-8 | 1-4 | 2 |
+----+---------------+-----------------+-----------+--------+
| 9 | QUATRO | 1-4-8-9 | 1-4-8 | 3 |
+----+---------------+-----------------+-----------+--------+
| 10 | TDI | 1-4-8-10 | 1-4-8 | 3 |
+----+---------------+-----------------+-----------+--------+
| 11 | Black | 1-4-8-9-11 | 1-4-8-9 | 4 |
+----+---------------+-----------------+-----------+--------+
| 12 | White | 1-4-8-9-12 | 1-4-8-9 | 4 |
+----+---------------+-----------------+-----------+--------+
| 13 | 2 doors | 1-4-8-9-11-13 |1-4-8-9-11 | 5 |
+----+---------------+-----------------+-----------+--------+
| 14 | 5 doors | 1-4-8-9-11-14 |1-4-8-9-11 | 5 |
+----+---------------+-----------------+-----------+--------+

This is a new idea and need some improvement.
I hope help me with it and I do not know How I can contact with MySQL developer team to discuss it.
Thanks