I am trying to create a simple file upload system using PHP and MySQL. I have created a database with two tables in, structure below:
File Table
+---+------------------+--------------+
| # | Name | Type |
+---+------------------+--------------+
| 1 | file_id | int |
| 2 | file_name | varchar |
| 3 | file_size | int |
| 4 | file_type | varchar |
| 5 | folder | int |Foreign Key to folders.folder_id
| 6 | user_Id | int |
| 7 | upload_time | timestamp |
| 8 | modify_time | timestamp |
+---+------------------+--------------+
Folders Table
+---+------------------+--------------+
| # | Name | Type |
+---+------------------+--------------+
| 1 | folder_id | int |
| 2 | name | varchar |
| 3 | subfolder | int |
+---+------------------+--------------+
Files Query
SELECT *
FROM files
JOIN folders
ON folders.folder_id = files.folder
ORDER BY folders.subfolder ASC,
folders.folder_id ASC
Folders Query
SELECT * FROM folders
The tables both echoed into a HTML table. To do this, I have two query runs happening separately, one which echoes the folders and displays them on the table, and the other echoes the files and lists them below the list of folders.
Rather than listing all the folders and then all the files I want the files to be listed below their relevant folders.
This is how my table is currently displayed:
Table Heading
-------------
folder
folder2
folder3
file1
file2
file3
file4
This is how I want it to be:
Table Heading
-------------
folder
file1
file2
folder2
file3
folder3
file4