使用SQL对表中的数据进行分类/分组

Question

I have stored the physical locations of specific files in my database with download counters to provide downloads via shorter urls like /Download/a4s. Each file has a categoryId assigned via foreign keys which just describes to which course/lecture it belongs for an easier overview. The table fileCategories basically looks like this

categoryId | categoryName
---------------------------
         1 | Lecture 1
         2 | Lecture 2
         3 | Personal Stuff

Assume that I have a files table which looks like this with some other columns I did omit

fileId | categoryId | filePath     | ...
----------------------------------------
     1 |          1 | /Foo/Bar.pdf | ...
     2 |          1 | /Foo/Baz.pdf | ...
     3 |          2 | /Bar/Foo.pdf | ...
     4 |          2 | /Baz/Foo.pdf | ...
     5 |          3 | /Bar/Baz.pdf | ...

I have created a page which should display some data about those files and group them by their categories which produces a very simple html table which looks like this:

Id | Path         | ...
-----------------------
Lecture 1
-----------------------
 1 | /Foo/Bar.pdf | ...
 2 | /Foo/Baz.pdf | ...
-----------------------
Lecture 2
-----------------------
 3 | /Bar/Foo.pdf | ...
 4 | /Baz/Foo.pdf | ...
-----------------------
Personal Stuff
-----------------------
 5 | /Bar/Baz.pdf | ...

So far I am using multiple SQL queries to fetch and store all categories in PHP arrays and append file entries to those arrays when iterating over the files table. It is highly unlikely this is the best method even though the number of files is pretty small. I was wondering whether there is a query which will automatically sort those entries into temporary tables (just a spontaneous guess to use those) which I can output to drastically improve my current way to obtain the data.

Answer 1

You can not do this with just mysql but a combination of JOIN and some PHP.

SELECT * FROM files f LEFT JOIN fileCategories c USING (categoryId) ORDER BY c.categoryName ASC

Be sure to order by the category first (name or ID) and optionally order by other params after that to allow the following code example to work as expected.

in PHP then iterate over the result, remember the category id from each row and if it changes you can output the category delimiter. assumung the query result is stored in $dbRes

Example Code:

$lastCategoryId = null;
while ($row = $dbRes->fetchRow()) {
    if ($lastCategoryId !== $row['categoryId']) {
        echo "--------------------" . PHP_EOL;
        echo $row['categoryName'] . PHP_EOL
        echo "--------------------" . PHP_EOL;
    }

    echo $row['filePath'] . PHP_EOL;
    $lastCategoryId = $row['categoryId'];
}