Mysql select recursive get all child with multiple level

Question

i have a table

 CREATE TABLE IF NOT EXISTS `Folder` (
    `idFolder` INT(11) NOT NULL AUTO_INCREMENT,
    `FolderName` VARCHAR(150) NOT NULL,
    `idFolderParent` INT(11) NULL,
    PRIMARY KEY (`idFolder`),
    CONSTRAINT `fk_1`
    FOREIGN KEY (`idFolderParent`)
    REFERENCES `Folder` (`idFolder`)
)

i fill this table by

idFolder , FolderName , idFolderParent
   1           ADoc           Null  
   2           ADoc1           1  
   3           ADoc2           2
   4           ADoc3           3
   5           ADoc4           4
   6           ADoc5           5
   7           ADoc6           4

when select a folder with idFolder=1, it should be return all child for this folder and subchild folder(2, 3, 4, 5 ,6 ,7)

When i select a folder id = 4 ==> (5,7, 6)

When i select a folder id = 3 ==> (4, 5,6, 7)

How to do that with one query?

Thx

Answer 1

None of the previous solutions worked for me. Both only work if the parents are saved into the database in a certain order.

I have to admit I do not fully understand the way the query works but could find a way that works for me (at least better than the other answers).

The data with which the first and second queries do not work is:

idFolder , FolderName , idFolderParent
   1           ADoc           Null  
   2           ADoc1           7  
   3           ADoc2           2
   4           ADoc3           3
   5           ADoc4          Null
   6           ADoc5           5
   7           ADoc6           5

If you use the first and second queries in this dataset, for the id 5 you only get as a result '6,7'. But if you use my query you get: '6,7,2,3,4' which is the expected result.

My version:

SELECT GROUP_CONCAT(lv SEPARATOR ',') FROM (
SELECT @pv:=(SELECT GROUP_CONCAT(idFolder SEPARATOR ',') FROM Folder 
WHERE FIND_IN_SET(idFolderParent, @pv)) AS lv FROM Folder 
JOIN
(SELECT @pv:=5) tmp
) a;

Hope it helps someone. I cannot comment nor downvote the other answers because of lack of reputation :(