Should I use a loop in sql or php? [duplicate]

Question

This question already has answers here:

Closed 13 years ago.

Possible Duplicate:
What is the most efficient/elegant way to parse a flat table into a tree?
php / Mysql best tree structure

I have a table in my database full of groups, each group row has a parent id until the last parent id is 0, making a pyramid. For example:

id: 1 name: staff parent: 0

id: 2 name: communications team parent: 1

id: 3 name: web department parent: 2

Now that can go on forever down the lines, so what I want to do is by using one sql statement giving the web department id it will pull all of the parent groups until the parent is 0.

Is that possible within an sql statement? How could I do that?

Answer 1

I don't think you need a loop at all... just something like this in T-SQL:

SELECT id, name, parent
FROM tablename
WHERE parent <= (select parent from tablename where name = 'staff')
ORDER BY parent;

This should return all rows where the parent id is less than or equal to the parent id of staff. If you want it ordered the other way, use ORDER BY parent ASC instead.

• Tyler: well the parent won't always be less than, the id's are randomly created, so if I do less than I will get all parents and groups created before this one.
• David: Can you elaborate on this statement, then:<BR>so what I want to do is by using one sql statement giving the web department id it will pull all of the parent groups until the parent is 0. <BR>I suppose I don't understand, it sounds like you're retrieving them from the current entry, until the parent is 0... so from the current ID to 0. Can you rephrase that?

Answer 2

You pull everything that you might need from the sql and then use a recursive function in PHP to walk through the results. Look for PHP recursive trees.

• Tyler: oh I see, like just pull EVERYTHING from the sql, then sort through the junk in php?