Displaying a list - MySQL Adjacency

I am just coding my first module in Contao and I am trying to display a list from a table I created. The table looks like this:

id | pid | name | show_independently_of_children
1 | 0 | category 1 | 0
2 | 0 | category 2 | 1
3 | 1 | category 1.1 | 1
4 | 2 | category 2.1 | 1
5 | 3 | category 1.1.1 | 1
4 | 2 | category 1.2 | 1


I am having difficulty in getting the output that I would like, which based on the above, would look like:

category 1 > category 1.1
category 1 > category 1.1 > category 1.1.1
category 1 > category 1.2
category 2
category 2 > category 2.1

The database might have a total of 1000 rows.

Any help would be very much appreciated.

I was always a fan of the Nested Set (http://en.wikipedia.org/wiki/Nested_set_model) I combined that with the traditional parent child when I used it for my own antique cms. But the big advantage is that you can order it easily with SQL (ORDER BY left) and it is also efficient for getting subsets or whatever (WHERE left > x AND right < y). I'd say that in your case 1 simple query is required.

The only drawback is the sort of expensive updating of the tree upon insert or any other operation involving moving nodes. But I never had any problems whatsoever with that.

The wikipedia article explains everything, you can decide if it is right for you...

Quote:

---

Originally Posted by Ruud

I was always a fan of the Nested Set (http://en.wikipedia.org/wiki/Nested_set_model) I combined that with the traditional parent child when I used it for my own antique cms. But the big advantage is that you can order it easily with SQL (ORDER BY left) and it is also efficient for getting subsets or whatever (WHERE left > x AND right < y). I'd say that in your case 1 simple query is required.

The only drawback is the sort of expensive updating of the tree upon insert or any other operation involving moving nodes. But I never had any problems whatsoever with that.

The wikipedia article explains everything, you can decide if it is right for you...

---

Do you know, I had considered this but I thought, it is a nightmare (comparatively) to update the position of an item. Now you mention it again, I realise that this doesn't matter as 99.9999999999999% or requests to the database will be reading and 0.0000000000001% of requests will involve a write so it is much better to make the reading bit as trivial as possible.

Sometimes it is to easy to get traped in a 1 dimentional train of thought - time away from a project will always bring clarity :-)

Cheers

Haha, well, I know the feeling. Especially when tired... Funny thing is; I removed a sentence from my reply in which I said that you'd probably write to the tree less often. Removed it because I didn't actually know about your situation.....