I've tried to work on a stored procedure using the this thread to create a depth-based infinite comment
Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)
And I was able to successfully generate multiple comments, and it's child comments and the child comment's child comment and so on with a single query. My problem is how do I generate the child comment directly under the parent comment? The query that I've been using
CALL comment_procedure('6da6688bad307bb');
generated it in order of its depth, I've tried modifying it but I can't seem to generate the right result.
Do I need to edit my stored procedure or do I need a specific PHP code to have the desired output.
begin
declare v_done tinyint unsigned default 0;
declare v_depth smallint unsigned default 0;
create temporary table comment_hier(
parent_id varchar(50),
cmmt_id varchar(50),
dateandtime datetime,
depth smallint unsigned default 0
)engine = memory;
insert into comment_hier select parent_id, cmmt_id , date, v_depth from cmmt_sect where parent_id = c_cmmt_id;
create temporary table tmp engine=memory select * from comment_hier;
while not v_done do
if exists( select 1 from cmmt_sect p inner join comment_hier on p.parent_id = comment_hier.cmmt_id and comment_hier.depth = v_depth) then
insert into comment_hier
select p.parent_id, p.cmmt_id, date, v_depth + 1 from cmmt_sect p
inner join tmp on p.parent_id = tmp.cmmt_id and tmp.depth = v_depth;
set v_depth = v_depth + 1;
truncate table tmp;
insert into tmp select * from comment_hier where depth = v_depth;
else
set v_done = 1;
end if;
end while;
select
p.cmmt_id,
p.content as comment,
b.cmmt_id as parent_comment_id,
b.content as parent_comment,
comment_hier.dateandtime,
comment_hier.depth
from
comment_hier
inner join cmmt_sect p on comment_hier.cmmt_id = p.cmmt_id
left outer join cmmt_sect b on comment_hier.parent_id = b.cmmt_id
order by comment_hier.depth;
drop temporary table if exists comment_hier;
drop temporary table if exists tmp;
end
