List: General Discussion « Previous MessageNext Message » From: PJ Date: February 23 2009 12:03am Subject: Re: book categories View as plain text
```Claudio Nanni wrote:
> Hi Phil,
> relax you are on track!
> First a little theory.
> In this case you have the so called N-to-M relationship.
> While if you need to relate one book to one author(given only one author per
> book!)
> you have the so called 1-to-N relationship, in this case you have the N-to-M
> relationship.
> If you are asking yourself why one book-one author is a 1-to-N relationship
> do not think of the single row/record,
> but reason this way(again, given only one author per book):
>
OK, I understand. But in my case, there are a number of books with sever
authors and I'm trying to figure that out in another thread. [HOW TO
QUERY(SELECT) and display MULTIPLE AUTHORS]
> Take one row on the [books] table and ask yourself: "How many possible
> authors have written this book?"  Ans: 1
> Take one row on the [authors] table and ask yourself: "How many possible
> books have been written by this authors" Ans: Many (N any number)
>
> so you have a 1-to-N relationship
>
> in your example you have [books] and [categories]
>
> Take one row on the [books] table and ask yourself: "To how many possible
> categories belongs this book?"  Ans: Many (N)
> Take one row on the [categories] table and ask yourself: "How many books can
> belong to this category?" Ans: Many (M , N is already taken!)
>
> so you have a N-to-M relationship
>
> while to implement a 1-to-N relationship you use a Foreign Key on one table,
> to implement a N-to-M relationship you need to use a table (cross reference)
>
> This means that while you still have the two original tables you will build
> a third table that connect the former two ones.
> This third table(Cross reference) it is just composed, at least, by two
> fields(two foreign keys) one referring to the primary key
> of the first table [books] and one referring to the primary kay of the
> second table [categories].
> Just insert for each book as many rows as the categories to which the book
> belongs, for example,
> if you have a book with book_id=1 and various
> categories(1,2,3,4,5,6,7,8,9,10), to assign to the book
> the categories 1,3,8 insert three rows in the cross-reference table:
>
> book_id | category_id
> -------------------------------
> 1           |       1
> 1           |       3
> 1           |       8
>
This I understand too and I do appreciate the input which is quite clear
and informative; but my real problem is to figure out how to use a
dropdown table (62 choices) to enter the data into the reference table
(linking categories and books from book_categories).
In fact it would be great to be able to extract the information to
select the categories from the categories table and use the id field as
the value field and the category_name field as the for the option in the
select section of the dropdown table. Otherwise it is rather tedious to
enter by hand the id and the category.  :-(  and I am sooooo lazy
;-)

Once I can populate the book_category table from a php-mysql form page,
the the problem remains on how to display the categories attributed to
each book...This is not an earth-shaking need but rather a tough
challenge since is is probably quite simple to do a php-mysql page to
display the books based on the book_category table.
And I am not sure that concat could work in this case... it still looks
like the same problem as the multiple author thingie.
Phil Jourdan
>
> AGAIN: CONCAT is just a string function, it is not a sql operator, and it is
> not giving anything that you don't already know,
> dont focus on CONCAT, forget it for now.
>
> Let me know
>
> Claudio Nanni
>
>
> 2009/2/22 PJ <af.gourmet@stripped>
>
>
>> Here's a tough one...
>> In my library I have some 62 categories where a number of books can be
>> in several categories.
>> Now, to relate the "categories" table(AS c) to the "book" table (AS a),
>> I assume that I need an intermediate table "book_category" (AS d)
>> linking d.bookID to the b.bookID and d.categoryID to c.id. With so many
>> categories and (probably) thousands of books d may become quite lengthy
>> (but, I guess it's better than 62 * 100's of lengthy fields added to b).
>> To enter the relationships I would add instructions on my
>> addNewBooks.php form with the input as multiple choice dropdown box
>> (listing the categories)...
>> so far, so good (I hope)...
>> Now, how do I SELECT and retrieve these categories to display on the web
>> page?
>> From the book_category table with a WHERE statement? If so, what then?
>> CONCAT_WS the stuff to go into the html table <td>?
>> I hope I'm on the right track... or am I in deep water?
>> Help...
>>
>> --
>>
>> Phil Jourdan --- pj@stripped
>>   http://www.ptahhotep.com
>>   http://www.chiccantine.com
>>
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:
>> http://lists.mysql.com/mysql?unsub=1
>>
>>
>>
>
>

--

Phil Jourdan --- pj@stripped
http://www.ptahhotep.com
http://www.chiccantine.com

```