Recursive tree CRUD PHP using a single MySQL table

Question

i'm using a tutorial from jesse price but it isn't working for me, its only showing the root element, so I wonder could anybody give me a clue how a recursive tree's CRUD would work? It was an interview question, needless to say, i don't have the job, but still I want to know how it works.... using a single MySQL table please help!

Answer 1

Once you understand the READ / SELECT the rest of the CRUD becomes pretty straightforward.

1) You start by selecting all nodes that have parent_id = 0 aka the root nodes:

$nodes = SELECT * FROM nodes WHERE parent_id = 0;

2) For each returned node you run another select query where parent_id = node_id.

3) Repeat step 2 until ad infinitum or until there are no more child nodes.

---

Inserting has nothing to know, you simply insert a new row and specify the parent_id.

Updating and deleting is a bit more complicated and there are several approaches, I suggest you take a look into the following presentation: http://www.slideshare.net/billkarwin/models-for-hierarchical-data.

Answer 2

BTW, I wrote that blog post a long time ago. I'll have to create a more updated tutorial for php5 oop based on a model (mvc) and recursive patterns.

Like Alix Axel said, you need to add another column to the mysql table where you want to have a recursive hierarchy.

Here's some better examples

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

Answer 3

Thank you so much guys, done it now, even with ajax to boot!

Answer 4

this might help get you started - just think about how you'd remove a node without orphaning children !!

http://i.imagehost.org/0934/product_hier.jpg

-- the table

drop table if exists product;
create table product
(
prod_id smallint unsigned not null auto_increment primary key,
name varchar(32) not null,
parent_id smallint unsigned null
)engine = innodb;

-- some test data

insert into product (name, parent_id) values
('Products',null), 
   ('Systems & Bundles',1), 
   ('Components',1), 
      ('Processors',3), 
      ('Motherboards',3), 
        ('AMD',5), 
        ('Intel',5), 
           ('Intel LGA1366',7);




-- the stored proc for returning a hierachy

drop procedure if exists product_hier;

delimiter #

create procedure product_hier
(
in p_prod_id smallint unsigned
)
begin

declare p_done tinyint unsigned default(0);
declare p_depth smallint unsigned default(0);

create temporary table hier(
 parent_id smallint unsigned, 
 prod_id smallint unsigned, 
 depth smallint unsigned default 0
)engine = memory;

insert into hier values (p_prod_id, p_prod_id, p_depth);

/* http://dev.mysql.com/doc/refman/5.0/en/temporary-table-problems.html */

create temporary table tmp engine=memory select * from hier;

while p_done <> 1 do

    if exists( select 1 from product p inner join hier on p.parent_id = hier.prod_id and hier.depth = p_depth) then

        insert into hier 
            select p.parent_id, p.prod_id,  p_depth + 1 from product p 
            inner join tmp on p.parent_id = tmp.prod_id and tmp.depth = p_depth;

        set p_depth = p_depth + 1;          

        truncate table tmp;
        insert into tmp select * from hier where depth = p_depth;

    else
        set p_done = 1;
    end if;

end while;

select 
 p.prod_id,
 p.name as prod_name,
 b.prod_id as parent_prod_id,
 b.name as parent_prod_name,
 hier.depth
from 
 hier
inner join product p on hier.prod_id = p.prod_id
inner join product b on hier.parent_id = b.prod_id
order by
 hier.depth, hier.prod_id;

drop temporary table if exists hier;
drop temporary table if exists tmp;

end #

delimiter ;


-- the table

select * from product;

-- call the stored proc passing in the root node you want to get a sub-tree for

call product_hier(1);

call product_hier(3);

call product_hier(5);