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

Question

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?

alexn · Accepted Answer · 2012-02-03 21:50:18Z

2

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.

edited Feb 3, 2012 at 21:50

alexn

59.2k14 gold badges114 silver badges146 bronze badges

answered Feb 3, 2012 at 21:48

David

8067 silver badges2 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Tyler Over a year ago

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 Over a year ago

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?

phpmeh · Accepted Answer · 2012-02-03 21:47:05Z

0

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.

answered Feb 3, 2012 at 21:47

phpmeh

1,8022 gold badges24 silver badges44 bronze badges

1 Comment

Tyler Over a year ago

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

Collectives™ on Stack Overflow

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

2 Answers 2

2 Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Linked

Related