many-to-many relationship in a bookstore application

simppa77

I'm building a bookstore application, and I've run into problems
right at the beginning...

as everyone knows, a book can have many authors, and one author can have written many books... a classic many-to-many relationship.

I've created three tables: books, authors, bookauthors
the last contains only two colums, ISBN and AuthorID as foreign keys.

My problem is this:

How do I query the database in a way, that results in a single
row per book which contains all the authors?

At the moment, if a book has more than one author, it is returned
in multiple rows, each row containing only one author.
So how do I combine all the possible authors into a single column?

Or am I looking at this the wrong way? Should this be solved somehow differently... using arrays perhaps?

Please help, I'm very frustraded with this, since ALL the example bookstore applications awailable take a short cut when it comes to the relation between authors and books. They all include only one author per book... which is quite ridicilous.

Simo Savonen, Finland

csousley

Try adding another table that holds just authors, and a book id. Then head throught that table looking for * from bookid, make sence?

I'm sure there's a million ways to do this, if this is the best way, I don't know...

Hope this helps

-Cy

Lars_Berg

Adding an extra table is not a good idea.

Do the aggregation in PHP instead.

$q = "select .. order by isbn";
//

$b = "";
while ($row = mysql_fetch_array($c)) {
  if ( $b != $row["isbn"] ) { // new book
     if ( $b != "") echo "<br>"; // finish presentation of last book
     $b = $row["isbn"];
   //write book info
 }
  // display author info
}
if ( $b != "" ) echo "<br>"; // finish presentation of last book

simppa77

I'm not sure what you mean with yet another table?
Wouldn't that be adding redundant data...

I've skimmed through several books regarding how to
create these tables... and I'm quite positive that the problem
is not in my tables... but in my query.

Let me explain a bit more:

Let's say that we have a book:
ISBN: 0-672-31784-2
TITLE: PHP and MySQL Web Development

and we have this data in the table Books.

We have two autors:
AuthorID: 1
Name: Luke Welling
AuthorID: 2
Name: Laura Thompson

and we have this data in the table Authors

The combining table, BookAuthors has the following data:
ISBN: 0-672-31784-2
AuthorID: 1
ISBN: 0-672-31784-2
AuthorID: 2

Now, my unfortunate attempts to query these tables has
resulted in the following result:

ISBN TITLE AUTHOR
0-672-31784-2 Php and MySQL ... Luke Welling
0-672-31784-0 Php and MySQL ... Laura Thompson

when instead, I wanted to get this:

ISBN TITLE AUTHOR
0-672-31784-2 Php and MySQL ... Luke Welling, Laura Thompson

in a single line... is this even possible?

simo

simppa77

thank you, Lars... this looks like it might do the trick.

simo

hicelik

i think there is no way of selecting a book and its authors on a single line using regular SQL expressions. But if you're using Oracle, you can write db packages consisting utility functions; and you can use those functions in your select statements.

if i were you; i would not create a seperate bookauthors table; instead, in book table define an authors text field long enough. Because normally a book has one author.