Find/Get Root

[The Little Guy]

I am going to build a function that gets an id from a table.

I have a second table that has categories, with three columns:

+ id + parent + name +

 

the id passed to the function will match the id column from above, then that id will have a parent id, which matches another row from the same table as the above three columns, and so on and so forth.

 

Basically I want to loop through this until parent equals 0, and there is more than one parent that equals 0  in the table (I have three at the moment).

 

So, lets say I pass 10 to the function, the returned MySQL row would be:

+ 8 + 0 + Internet +

 

if I pass "3" to it, I would get:

+ 2 + 0 + Windows +

 

 

parent of one row equals id of another row in the same table.

 

I hope that all makes sense...

[ignace]

And your question is..?

[The Little Guy]

Basically I want to loop through this until parent equals 0

 

how do I do that (I would like to use only one query if possible)?

[ignace]

Take a look at http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

[The Little Guy]

those examples didn't help, I don't have a left and right in my database, any suggestions?

[ignace]

those examples didn't help, I don't have a left and right in my database

 

Correct. But you do have an Adjacency List Model (parent, child) and the article discusses that model. A probably better read would be http://www.sqlsummit.com/AdjacencyList.htm that not only explains it but also gives you a better approach.

[The Little Guy]

It had no selects...