On Tue, Feb 24, 2009 at 8:59 AM, PJ <af.gourmet@xxxxxxxxxxxx> wrote:
> Reinhardt Christiansen wrote:
> >
> >
> >
> >> From: PJ <af.gourmet@xxxxxxxxxxxx>
> >> To: MySql <mysql@xxxxxxxxxxxxxxx>
> >> Subject: how to deal with multiple authors for one book
> >> Date: Mon, 16 Feb 2009 17:20:54 -0500
> >>
> >> In my db there are a number of books with several authors; so, I am
> >> wondering how to set up a table on books and authors to be able to
> >> insert (via php-mysql pages) data and retrieve and display these books
> >> with several authors
> >> I suspect that to insert data for a multiple author book I will have to
> >> enter all data other than the author names into the book table and enter
> >> the authors in the author tables with foreign keys to reference the
> >> authors and their book.
> >> Then to retrieve and display the book,I would have to use some kind of
> >> join instruction with a where clause(regarding the position - 1st, 2nd,
> >> 3rd...) to retrieve the authors and their order. The order would
> >> probably be done by a third field (e.g. f_name, l_name, position) in the
> >> book_author table (tables in db - book, author, and book_author)
> >> Am I on the right track, here?
> >>
> > Sort of, but not completely.
> >
> > I think you would really benefit from a tutorial or course on data
> > normalization. I haven't looked for one in several years so I can't
> > suggest a specific tutorial but if you google it, you may well find
> > something that you like.
> >
> > In a nutshell, you are trying to implement a many-to-many relationship
> > (a book can have several authors and an author can have several
> > books). These are not normally implemented directly in relational
> > databases. Instead, you typically have intermediate tables that are
> > usually called "association tables" (or "intersection tables") that
> > sit between the other tables. In your case, you might see something
> > like this:
> >
> > Book Table
> > =======
> > Book_code Title
> > --------------- ------
> > Z1 The Mote In God's Eye
> > Z2 Ringworld
> > Z3 Janissaries
> > Z4 War and Peace
> >
> >
> > Author Table
> > ========
> > Author_code Author_name
> > ------------------ ------------------
> > 1 Larry Niven
> > 2 Jerry Pournelle
> > 87 Leo Tolstoy
> >
> > Books Table (intersection table)
> > =======
> > Book_code Author_code
> > --------------- ------------------
> > Z1 1
> > Z1 2
> > Z2 1
> > Z3 2
> > Z4 87
> >
> > In other words, the Books table identifies that The Mote in God's Eye
> > is written by Niven _and_ Pournelle; Ringworld is written by Niven
> > alone and Janissaries is written by Pournelle alone. And, of course,
> > War and Peace is written by Tolstoy.
> >
> > You're going to want to do something very much like this.
> >
> > A good tutorial will explain this well. I'm out of time; I have to go
> > now.
> >
> > --
> > Rhino
> >
> >
> >
> Thank you for your clear explanation.
> I have things set up rather well and have been able to generate a web
> page to insert most of the data in the db and retrieve it to display in
> another web page.
> I say most because I have several "small" problems which I have posted
> on mysql and php lists. Perhaps you can suggest something either where
> and how to post or what to do.
>
> Problem 1. How to SELECT and display multiple authors. Presently, my
> book_author(intersection table contains fields authID, bookID and
> ordinal. ordinal refers to the order of the author's name (1 if only 1
> or first in line; 2 if 2nd in line). To retrieve the author's name I use
> CONCAT_WS(' ', first_name, last_name) AS Author. So far, in my testing I
> only have 10 books in the db with only single authors. Undoubtedly this
> is not the way to go to retrieve 2 authors and display them as
> (first_name1 last_name1 and first_name2 lastname or "Joe Firstauthor adn
> Bob Secondauthor").
>
> The present query (works fine for 1 author):
> "SELECT b.title, b.sub_title, b.descr, b.comment, b.bk_cover,
> b.copyright, b.ISBN, b.sellers, c.publisher,
> CONCAT_WS(' ', first_name, last_name) AS Author
> FROM book AS b
> LEFT JOIN book_author AS ab ON b.id = ab.bookID
> LEFT JOIN author AS a ON ab.authID=a.id
> LEFT JOIN book_publisher as abc ON b.id = abc.bookID
> LEFT JOIN publishers AS c ON abc.publishers_id = c.id
> ORDER BY title ASC ";
>
> But to show 2 authors I think I need something of the order of:
> CONCAT_WS (' ', (CONCAT_WS (' ', [(first_name, last_name)WHERE
> book_author.ordinal = 1], [(first_name, last_name)WHERE
> book_author.ordinal = 2]) AS Author
>
> I suspect that one cannot nest the CONCAT_WS statement and I suspect the
> WHERE is not in the right place either, but this seems to be fairly
> logical... am I on the right track?
>
> Problem 2... is similar to Problem 1 but deals with multiple categories
> (62) and I'll deal with than when I get this one solved.
> TIA
>
> --
>
> Phil Jourdan --- pj@xxxxxxxxxxxxx
> http://www.ptahhotep.com
> http://www.chiccantine.com
>
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
try:
1. using a subselect using your concat_ws
2. a view if your db supports its
3. using php to manipulate the resultset
--
Bastien
Cat, the other other white meat
