How to implement folder, subfolder in database
I have a problem with logic programming, but I think it might be another solution, much easier to do it in a SQL command, however let go of the problem.
I have a table in MySQL (InnoDb) like this:
CREATE TABLE IF NOT EXISTS `folder` (
`id` INT NOT NULL AUTO_INCREMENT ,
`folder_id` INT NOT NULL ,
`name` VARCHAR(100) NULL ,
`hidden` TINYINT(1) NOT NULL DEFAULT 0 ,
PRIMARY KEY (`id`) ,
INDEX `fk_folder_folder1` (`folder_id` ASC) ,
CONSTRAINT `fk_folder_folder1`
FOREIGN KEY (`folder_id` )
REFERENCES `folder` (`id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
It seems to be quite simple, but I want to list the folders in such a way that the subfolders of that folder appear below it. As in any file repository.
For example:
In the table:
id name folder_id
1 root 1
2 etc 1
3 dev 1
4 apache 2
5 php 2
6 mysql 2
7 hda 3
8 hda1 3
9 lib 1
I want to show it like this:
root
etc
apache
php
mysql
dev
hda
hda1
lib
The question is, is there a way to do this using a SQL or PHP command?
I'm trying to do in PHP and I can't see a path without a recursive function. But I have no success.
Solution:
Since MySQL does not accept the WITH clause, the recursive function in SQL is not included. Thank you, guys.
SELECT c2.name
FROM folder c1 LEFT OUTER JOIN folder c2
ON c2.folder_id = c1.id WHERE c2.folder_id = 1 // the id of the parent folder
AND c2.id != c2.folder_id // Remove the parent folder
ORDER BY c2.folder ASC
source to share
you run the risk of implementing an anti-SQL pattern called naive trees. See the pdf book, it shows some good solutions.
source to share