Infinite Subgroups

[Shawazi]

I have a table with the fields

id
name
attached_to

The default for attached_to is 0. An example table is:

id name attached_to
1 Test a 0
2 Test b 0
3 Sub a 1
4 Subsub a 3

Is there a way to have PHP read out

Test a
- Sub a
+ Subsub a
Test b

I've been thinking for an hour and am stumped. It seems to me I'll need to cap how many groups there can be. :bemused:

The closets I've come is
SELECT a.name, b.name AS attached_name
FROM positions a, positions b
WHERE a.id = b.attached_to

name attached_name

Test a Sub a
Sub a Subsub a

which won't show those that do not have an attachment

[Shawazi]

I figured

SELECT a.name, a.id, b.name AS attached_name, b.id AS attached_id
FROM positions a
LEFT JOIN positions b ON a.id = b.attached_to
ORDER BY id, attached_id

took care of showing all the correct info, but I am unsure how to go through all the results and ordering it properly.
So I can see something like
Test 1 | Sub 1
Test 1 | Sub1b
Test 2 | None
Sub 1 | Subsub 1

but it doesn't order it the right way of

Test 1 | Sub 1
Sub 1 | Subsub 1
Test 1 | Sub1b
Test 2 | None

[bogu]

U should mark this thread as RESOLVED...