Need help for a SQL query

Question

This is my table structure:

id  id_parent   id_origin   level   name
1   0   1   1   PHP
2   0   2   1   Javascript
3   0   3   1   SMARTY
4   0   4   1   HTML
5   1   1   2   Basic
6   1   1   2   Date & Math Function
8   2   2   2   DOM
9   5   1   3   Introduction
10  5   1   3   Session & Cookies
12  2   2   2   Introduction
13  4   4   2   Basic Structure
14  6   1   3   PHP Date Function
16  3   3   2   Basic Syntax
26  4   4   2   Table

I want result like below format

Myfinalstr
-----------
PHP
PHP->Basic
PHP->Basic->Introduction
PHP->Basic->Session & Cookies
PHP->Date & Match Function
PHP->Date & Match Function->PHP Date Function
Javascript
Javascript->DOM
Javascript->Introduction
SMARTY
SMARTY->Basic Syntax
HTML
HTML->Basic Structure
HTML->Table

Answer 1

The following isnt a complete solution but it will get you started:

Example stored procedure call

mysql> call chapter_hier(1);
+----+----------------------+-----------+----------------------+-------+
| id | category_name        | id_parent | parent_category_name | depth |
+----+----------------------+-----------+----------------------+-------+
|  1 | PHP                  |      NULL | NULL                 |     0 |
|  5 | Basic                |         1 | PHP                  |     1 |
|  6 | Date & Math Function |         1 | PHP                  |     1 |
|  9 | Introduction         |         5 | Basic                |     2 |
| 10 | Session & Cookies    |         5 | Basic                |     2 |
| 14 | PHP Date Function    |         6 | Date & Math Function |     2 |
+----+----------------------+-----------+----------------------+-------+
6 rows in set (0.00 sec)

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

Full script and test data

drop table if exists chapters;
create table chapters
(
id smallint unsigned not null primary key,
name varchar(255) not null,
id_parent smallint unsigned null,
key (id_parent)
)
engine = innodb;

insert into chapters (id, name, id_parent) values
(1,'PHP',null),
(2,'Javascript',null),
(3,'SMARTY',null),
(4,'HTML',null),
(5,'Basic',1),
(6,'Date & Math Function',1),
(8,'DOM',2),
(9,'Introduction',5),
(10,'Session & Cookies',5),
(12,'Introduction',2),
(13,'Basic Structure',4),
(14,'PHP Date Function',6),
(16,'Basic Syntax',3),
(26,'Table',4);


drop procedure if exists chapter_hier;
delimiter #

create procedure chapter_hier
(
in p_id smallint unsigned
)
begin

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

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

insert into hier select id_parent, id, v_depth from chapters where id = p_id;
create temporary table tmp engine=memory select * from hier;

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

while not v_done do

    if exists( select 1 from chapters c
        inner join tmp on c.id_parent = tmp.id and tmp.depth = v_depth) then

        insert into hier select c.id_parent, c.id, v_depth + 1 from chapters c
            inner join tmp on c.id_parent = tmp.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.id,
 c.name as category_name,
 p.id as id_parent,
 p.name as parent_category_name,
 hier.depth
from 
 hier
inner join chapters c on hier.id = c.id
left outer join chapters p on hier.id_parent = p.id
order by
 hier.depth;

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

end #

delimiter ;

Hope it helps :)