Problem:
MySQL does not allow stored functions to call themselves recursively, so how does one flatten a hierarchial path with an unknown number of levels?
 

Solution:
Keep in mind that any recursive algorithm can be rewritten as a loop (and vice versa):


select * from folders;
+----+-----------+--------+
| id | name | parent |
+----+-----------+--------+
| 1 | docs | 0 |
| 2 | old | 1 |
| 3 | new | 1 |
| 4 | deep | 3 |
| 5 | very deep | 4 |
+----+-----------+--------+
5 rows in set (0.00 sec)


delimiter !!

create function foldername (this_id integer)
returns varchar(255)
deterministic
reads sql data
begin
declare result, this_name varchar(255) default '';
declare parent_id integer;
set parent_id = this_id;

while this_id is not null
do
set parent_id = null;

select name, parent
into this_name, parent_id
from folders where id = this_id;

set this_id = parent_id;
if parent_id is not null
then
set result = concat(this_name, '/', result);
end if;
end while;

return result;
end;
!!

delimiter ;

select *,foldername(id) from folders;
+----+-----------+--------+--------------------------+
| id | name | parent | foldername(id) |
+----+-----------+--------+--------------------------+
| 1 | docs | 0 | docs/ |
| 2 | old | 1 | docs/old/ |
| 3 | new | 1 | docs/new/ |
| 4 | deep | 3 | docs/new/deep/ |
| 5 | very deep | 4 | docs/new/deep/very deep/ |
+----+-----------+--------+--------------------------+
5 rows in set, 5 warnings (0.01 sec)


Use caution: This function will loop endlessly if your data contains a reference loop. Either make sure your data is consistent, or add loop handling code.