SQL语句/ php代码为一本书处理许多作者

Question

I'll try to make it simple. I have a table for my books, one for the authors and a junction table with the id from my books with the author ID.

Now my issue is, I don't want to list the books more than once in the case the book has many authors. I could use the Group By, but I'm looking for a way to list the book once and instead of showing only one author, in the case it has more than one, I'd like to add the other authors in the same listing.

Really thought about this during the day, and I can't think of a good way to do it. Any help is greatly appreciated!

Thanks!

Answer 1

Do two SQL statements. Something like this...

$sql = "SELECT id, name FROM books";

foreach ($conn->query($sql) as $book) {
    $sql = "SELECT a.name
            FROM authors a, author_books ab
            WHERE ab.book_id = $book['id']
            AND ab.author_id = a.id";

    foreach ($conn->query($sql) as $author) {
        echo $row["name"];
    }
}

Cheers