MySQL Forums
Forum List  »  PHP

many to many relationships and PHP
Posted by: Robert S
Date: May 06, 2009 11:32AM

Hi all,

Say I have a simple book database




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_authors.book_id and = books_authors.author_id and = 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!


Options: ReplyQuote

Written By
many to many relationships and PHP
May 06, 2009 11:32AM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.