Parent Child - Looking For a Better Method

[jimmyoneshot]

In my database I have a parent child relationship set up within my services table. On my front end this can only go three levels deep. Currently I am using the following query to get all items at their levels:-

 

SELECT id, @a:='1' AS level FROM services WHERE parentId = 0

 

UNION

 

SELECT id, @a:='2' AS level FROM services WHERE parentId IN (SELECT id FROM services WHERE parentId = 0)

 

UNION

 

SELECT id, @a:='3' AS level FROM services WHERE parentId IN (SELECT id FROM services WHERE parentId IN (SELECT id FROM services WHERE parentId = 0))

 

UNION

 

SELECT id, @a:='4' AS level FROM services WHERE parentId IN (SELECT id FROM services WHERE parentId IN (SELECT id FROM services WHERE parentId IN (SELECT id FROM services WHERE parentId = 0)))

 

I'm wondering is there a better method of doing this as this seems like overkill?

[jimmyoneshot]

I also need to show parent child relationship so I can produce an indented visual of these i.e. it will look something like this:-

 

-itemA

  -itemB

      -itemC

      -itemD

  -itemE

 

-itemF

[fenway]

One of the stickies has a dozen ways to handle this.

[jimmyoneshot]

Thanks fenway.

 

I ended up looping through the result with php but I'll look into that to see if I can simplify it with mysql.

 

Cheers.