2

Possible Duplicate:
php / Mysql best tree structure

I am currently coding a file system in PHP and MySQL.

Folders are stored in a database table "folders" with fields: "id", "name", "parent_id". I thought this would be the fastest way.

Files are stored on the system, linked by the database table "files" with fields "id", "name", "parent_id" and "realUrl".

Assuming I would create a path like this: One/Folder/After/Another/Folder. This is table "folder" then:

+++++++++++++++++++++++++++++
+ id |   name   | parent_id +
+----|----------|-----------+
+ 1  | One      |     0     +
+ 2  | Folder   |     1     +
+ 3  | After    |     2     +
+ 4  | Another  |     3     +
+ 5  | Folder   |     4     +

It is obvious that it's not that simple to find out things like the path of a folder. When I request the data of folder "Folder", I would want to know the path to this specific folder. I couldn't figure a good way out with MySQL except for selecting ALL contents of the database table and then iterate through it with PHP. But that could take terribly long when I have thousands of folders?

What would you suggest? I thought about creating a seperate table "parents" where I store all dependencies:

++++++++++++++++++++++++++
+ folders_id | parent_id +
+------------|-----------+
+     2      |      1    +
+     3      |      1    +
+     3      |      2    +
+     4      |      1    +
+     4      |      2    +
+     4      |      3    +
+     5      |      1    +
+     5      |      2    +
+     5      |      3    +
+     5      |      4    +

So if I want to get the path from "One" to "Folder", I just have to

SELECT parent_id FROM parents WHERE folders_id=5

There is huge risk of making errors and it's just redundant data of course. So there might be a more intelligent solution? Could I even iterate through the table "folders" by MySQL commands directly?

Of course my question is also about speed. Is it faster to make multiple easy queries or better just one large to get my data?

With the table "parents" I can find out the path of the folders with one query (merging both tables) and some PHP. Without the table "parents", I don't know how except selecting all, which can't be the solution?

Community
  • 1
  • 1
andreas
  • 7,844
  • 9
  • 51
  • 72
  • 1
    I suggest do this using nested sets - see http://stackoverflow.com/questions/5916482/php-mysql-best-tree-structure - that allows you to retrieve the whole path without using one JOIN per level of the hierarchy. – John Carter Aug 25 '12 at 02:21
  • 1
    Different read on nestes sets http://www.sitepoint.com/hierarchical-data-database/ (actually I've always heard it being called MPTT) – Leite Aug 25 '12 at 02:28
  • 1
    Probably a daft question, buy why don't you use a filesystem for your file system? – eggyal Aug 25 '12 at 07:20
  • @therefromhere and Leite: Wow, I didn't know that. That's probably what I was looking for. eggyal: Did you mean the fs from the machine itself? I think it's slower as storing via MySQL? – andreas Aug 25 '12 at 12:47
  • It depends what you're trying to do, and what parts need to be quick - if you're actually talking about a physical file system then yes that could well make sense to just use one. I took your question to mean you weren't though. – John Carter Aug 25 '12 at 20:17
  • I tried your method and it seems to work pretty well! The only disadvantage I found (yet) was to get the subfolders of one folder. The SQL query is kinda tricky comparing to my table above. But in total my PHP code dropped from around 400 lines to 50. No more going through all rows. :) – andreas Aug 25 '12 at 22:12

0 Answers0