many to many relationships and PHP

[Robert S]

Hi all,

Say I have a simple book database

books
-----
id
title
publisher

author
------
id
name

book_authors
-----------
book_id
author_id

Books have a title and a publisher, and books can have many authors while authors can write many books. Now say that I'd like to list the details of a particular book. I can get all the information except the titles like this:

select * from books where id = 1;

I can use PHP and $result->fetch_assoc() to get the result back with no problem. but what about when I want to list all the book details including the one or many authors? Now my query looks like this:

select title, publisher, name from books, authors, books_authors
where books.id = books_authors.book_id and
authors.id = books_authors.author_id and
books.id = 1;

In PHP, this gives me back kinda what I expect; a result with all the information for book with id = 1, but you get as many rows as there are authors for a book with all the other information repeated (i.e. title, publisher). So if book 1 had two authors, I get a result set like this:

row1 = 'title 1', 'publisher 1', 'author 1';
row2 = 'title 1', 'publisher 1', 'author 2';

This seems reasonable to me, and I can just use an array to process this data to show book 1 with title 1 and publisher 1, and then just loop over however many authors there are. But it seems like this can get progressively less efficient if I have a lot of authors, or if the data model changed and I had a lot of many to many relationships. I'm also worried about performance if I had to show a lot of results (with each result having multiple records returned for the many to many relationship(s)).

Am I doing the query wrong, or is this just how things work (do the query, and use PHP to post-process)? Any advice is appreciated!

Robert