cyclone200 cyclone200 - 3 months ago 9
MySQL Question

SQL display and fetch GROUP BY categories

I have a list of authors and I would like to display it by book genre, like this:

Horror
Stephen King
Author 2
Author 3
...

Comedy
Author 4
Author 5
...


In my database I have an author table:

t_author

aut_name | aut_genre
Stephen King | 1
...
...
Author 4 | 2


And a genre table :

t_genre

genre_id | genre_name
1 | Horror
2 | Comedy
...
...


I've tried to use a GROUP BY but I can't find how to display the names of the category and organize my list:

SELECT t_author.aut_name, t_author.aut_genre
FROM t_author
LEFT JOIN t_genre ON t_author.aut_genre = t_genre.genre_id
GROUP BY t_genre.aut_genre


So, using MySQL and PDO, is there a way to select and fetch all the authors and display it by categories?

No need to say that I'm not looking for a code but just a clue because I don't even see how to describe it. I mean, is GROUP BY the right statement to use?

EDIT 1 :

Apparently, I was looking for something like this :

SELECT t_author.aut_name, t_author.aut_slug, t_genre.genre_name
FROM t_author
LEFT JOIN t_genre ON t_author.genre_id = t_genre.genre_id


But it displays:

Horror
Stephen King
Horror
Author 2
Comedy
Author 4
Horror
Author 3


I perfectly understand why but I can't find how to display it like below:

Horror
Stephen King
Author 2
Author 3
...

Comedy
Author 4
Author 5
...


Answer

HtmHell provided me a good way to do this with a 2D array. For my 3D array, I edited his code like this:

foreach($result as $author=> $slug) {
$authorsByGenre[$slug['genre_name']][] = array($slug['aut_name'] => $slug['aut_slug']);
}
return $authorsByGenre;

Answer

How about something like this:

$authorsByGenre = [];

$results = $db->query("
    SELECT      t_author.aut_name, t_genre.genre_name
    FROM        t_genre
    JOIN        t_author
    ON          t_author.aut_genre = t_genre.genre_id
")->fetchAll();

foreach ($results as $data) {
    $authorsByGenre[$data['genre_name']][] = $data['aut_name'];
}

print_r($authorsByGenre);
Comments