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
