PHP Build a hierarchy set then insert data from a second table

Question

Ive been trying to figure this out for the better half of two months and not found a solution. I can find plenty of information for building the data for my Types table using recursive function but not for my actual data table. I could be using the wrong terminology. I want to add this is not for building a menu but for building dynamic forms into sections.

If I drop the parent column I can get things to work but I need the extra level.

These are the test tables

types
=====
id  name        parent
1   module1     0
2   module2     0
3   component1  1
4   component2  1
5   component3  2
6   component4  2

items
=====
id  name        type_id
1   item1       3
2   item2       3
3   item3       4
4   item4       4
5   item5       5
6   item6       5
7   item7       6
8   item8       6

The result Im trying to achieve

desired output
==============

module1
    component1
        item1
        item2
    component2
        item3
        item4
module2
    component3
        item5
        item6
    component4
        item7
        item8

Any solution to this kind of problem? Table changes, links, or examples? Thanks.

inhan · Accepted Answer · 2012-11-09 23:54:17Z

1

Wouldn't the following query work or am I misunderstanding your question?

SELECT
    t2.`name` AS 'module',t1.`name` AS 'component',i.`name` AS 'item'
FROM (SELECT * FROM `types` WHERE `parent`>0)  t1
INNER JOIN `types` t2 ON t1.`parent`=t2.`id`
LEFT OUTER JOIN  `items` i ON i.`type_id`=t1.`id`

EDIT: Changed WHERE `parent`!=0 into WHERE `parent`>0 since an id by default will be greater than 0 (and typically -unless set explicitly- a negative integer is an invalid id).

edited Nov 9, 2012 at 23:54

answered Nov 8, 2012 at 17:51

inhan

7,5002 gold badges26 silver badges35 bronze badges

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

Comments

phant0m · Accepted Answer · 2012-11-08 17:50:05Z

1

You can use the nested sets model to represent hierarchies. That way, you can do away with the recursive call.

Another thing that you can do, is create an extra table to handle to handle the "is-a-child" relationship with a transitive closure, i.e. then you have all is-a-descendant-of relationships between the nodes.

answered Nov 8, 2012 at 17:50

phant0m

17k6 gold badges52 silver badges84 bronze badges

Collectives™ on Stack Overflow

PHP Build a hierarchy set then insert data from a second table

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related