RE: book categories
>-Original Message-
>From: PJ [mailto:af.gour...@videotron.ca]
>Sent: Sunday, February 22, 2009 2:39 PM
>To: MySql
>Subject: book categories
>
>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 ?
>I hope I'm on the right track... or am I in deep water?
>Help...
>
[JS] We do this all of the time, and it isn't that difficult. You don't want
to use any form of CONCAT. What you want to do is SELECT all of your
categories in one fell swoop, and then loop through them to create your
dropdown. Here's a simple example, directly from our code. This would be
inside a form. (I apologize for the line-wrapping.)
===
Account:
" . mysql_error());
// Loop as long as we have records
while($row_tmp = mysql_fetch_array($result)) {
?>
"
>
===
All it really does is loop through the results of the SELECT statement, and
spit out an for each record.
>--
>
>Phil Jourdan --- p...@ptahhotep.com
> 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=jschwa...@the-
>infoshop.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: book categories
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 so 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 > > >> 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 ? >> I hope I'm on the right track... or am I in deep water? >> Help... >> >> -- >> >> Phil Jourdan --- p...@ptahhotep.com >> 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=claudio.na...@gmail.com >> >> >> > > -- Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com
Re: book categories
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): 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 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 > 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 ? > I hope I'm on the right track... or am I in deep water? > Help... > > -- > > Phil Jourdan --- p...@ptahhotep.com > 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=claudio.na...@gmail.com > >
