1
2
3
4
5
6
7
8
9
10
11
12
13
14

<?php

$conn = new mysqli("localhost", "foo_dbo", "pass", "foo_db", 3306);

$result = $conn->query(sprintf("call comments_hier(%d)", 3));

while($row = $result->fetch_assoc()){
   ...
}

$result->close();
$conn->close();
?>

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93

drop table if exists comments;
create table comments
(
comment_id int unsigned not null auto_increment primary key,
subject varchar(255) not null,
parent_comment_id int unsigned null,
key (parent_comment_id)
)engine = innodb;


insert into comments (subject, parent_comment_id) values
('Comment 1',null), 
   ('Comment 1-1',1), 
   ('Comment 1-2',1), 
      ('Comment 1-2-1',3), 
      ('Comment 1-2-2',3), 
        ('Comment 1-2-2-1',5), 
        ('Comment 1-2-2-2',5), 
           ('Comment 1-2-2-2-1',7);


delimiter ;

drop procedure if exists comments_hier;

delimiter #

create procedure comments_hier
(
in p_comment_id int unsigned
)
begin

declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;

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

insert into hier select parent_comment_id, comment_id, v_depth from comments where comment_id = p_comment_id;

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

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

while not v_done do

  if exists( select 1 from comments c inner join hier on c.parent_comment_id = hier.comment_id and hier.depth = v_depth) then

    insert into hier 
      select c.parent_comment_id, c.comment_id, v_depth + 1 from comments c
      inner join tmp on c.parent_comment_id = tmp.comment_id and tmp.depth = v_depth;

    set v_depth = v_depth + 1;      

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

  else
    set v_done = 1;
  end if;

end while;

select 
 c.comment_id,
 c.subject,
 p.comment_id as parent_comment_id,
 p.subject as parent_subject,
 hier.depth
from 
 hier
inner join comments c on hier.comment_id = c.comment_id
left outer join comments p on hier.parent_comment_id = p.comment_id
order by
 hier.depth, hier.comment_id;

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

end #

delimiter ;

-- call this stored proc from your php

call comments_hier(1);

call comments_hier(5);